Help with PasteSpecial in Macro VBScript issues required
I'm currently working on copying and pasting straight charts from Qlikview to an Excel spreadsheet. This is automated using a macro from within Qlikview.
Originally I was simply copying and pasting the charts via macro which worked fine but then I started coming across issues with copying and pasting data with leading zeros, the zeros were not carried over. This is most likely down to Excel interpreting these as numbers.
A workaround I've programmed is to format the spreadsheet template as text and then copy and paste into that which in theory should keep the leading zeros as long as I paste as values from Qlikview to Excel.
The first issue I encountered with this is the macro falls over every time it comes across a chart with no data. I thought I had gotten around this issue by placing a check to only perform the copy and paste special if the row count is greater than 0 for a particular chart.
Oddly, this works for some of the charts but not all. Here is an example of the code:
'-- This bit activates the correct spreadsheet template and worksheet to use and then copies the data held in a particular straight chart to the clipboard. WS1 is determined by an earlier process, it's basically the name of the worksheet and that works fine.