Error 400 when trying to copy/paste between workbooks

0
votes

I'm having trouble with Excel VBA with the following: I'm trying to open an existing empty workbook template (pn), copy data from an existing sheet (wb) and paste it into "pn", but I keep getting all kinds of error such as Error 400, Subscript out of range and constant freezing.

I've tried the code with many variations, but mostly (and in my last attempt below) the error happens in the following line:

Workbooks(pn).Sheets("data").Select

Does it have to do with VBA properly getting the opened file name or switching between workbooks? How to properly switch between different workbooks and copy/paste data?

EDIT: also, when running the code on debug mode using ("Step into") it works just fine. I've noticed that the real opened file name only appears when the code waits for the workbook to open entirely or when I alt+tab into it, else its name keeps showing as "Excel" and the error happens!

'opens template
fileName = Application.GetOpenFilename("Excel-files,*.xlsx", 1, "OPEN TEMPLATE", , False)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error GoTo Err1
Workbooks.Open (fileName)
pn = ActiveWorkbook.Name

'copy and paste between workbooks
Windows(wb).Activate
Workbooks(wb).Sheets("Info").Select
Workbooks(wb).Worksheets("Info").Range("A4:Z1000").Select
Selection.Copy 'Destination:=Workbooks(pn).Sheets("data").Cells(3, 1)
Workbooks(pn).Sheets("data").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
2
excelvba
You can store a full reference to the file path and switch between them. Dim pn As Workbook, wp As Workbook and Set pn = Workbooks.Open("complete path") and Set wb = Workbooks.Open("complete path") and then you can use them like pn.Sheets("data") – jessi

2 Answers

0
votes

You never need to use copy and paste in VBA. You can assign values to variables or you can assign cells to cells directly.

EXAMPLE:

'opens template
Filename = Application.GetOpenFilename("Excel-files,*.xlsx", 1, "OPEN TEMPLATE", , False)
Dim pn As Workbook
pn = Workbooks.Open(Filename)

'copy and paste between workbooks
pn.Sheets("data").Range("A3") = Workbooks(wb).Worksheets("Info").Range("A4:Z1000")
0
votes

That worked nicely. From now on I am generally using the following structure in the code:

Dim wb As Workbook
Dim hld As Workbook
Set wb = ThisWorkbook
...
wb.Sheets("Info1").Range("A2:AP1500").Copy Destination:=hld.Sheets("Info1").Cells(1, 1)
...

Sometimes I can only get Copy/Paste or Copy/Destination to work instead of direct assignment of values, for some reason, but still way better than what I was doing before. Thanks!