Francis
Francis

Reputation: 121

VBA : Copy all data to another workbook's sheet

I have a macro that works well on my pc, but doesn't in my friend's pc, which I've never seen before.

The error comes from this part :

...
Workbooks("GOP_Flow_" + Format(DateAdd("m", -1, Date), "yyyy") + Format(DateAdd("m", -1, Date), "mm")).Worksheets(1).UsedRange.Copy
main.Worksheets(2).Range("A1").PasteSpecial (xlPasteValues)
...

There's a run-time error 9 : "subscript out of range" on the copy line.
- How could this kind of thing happen since we use the same version of Excel?
- Is there an alternative way to do this?

Thank you in advance.

Upvotes: 1

Views: 135

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Just a guess... You have different options in your folder options settings for viewing file names.

On your PC, the option Hide extensions for know file types is checked, so the file's extension is not visible in the file's name. Hence Workbooks("GOP_Flow_201704) works for you without the file extension.

On your friend's PC, the option is not checked, so the extension makes part of workbooks name, something like "GOP_Flow_201704.xlsx", so you will need to add the extension to the name...

Workbooks("GOP_Flow_" & _
 Format(DateAdd("m", -1, Date), "yyyy") & _
 Format(DateAdd("m", -1, Date), "mm") & ".xlsx")...
'                                        ^^^^^^^

As a solution, putting the file's extension in the name works in both cases.

p.s. By the way, dont use + for string concatenation, use & instead.

Upvotes: 1

Related Questions