VBA can't open external workbook and copy data at the same time

[neek0la]

Hi all,
Odd situation here
I have the followiing VBA code working well when i open the Quote register file.

line_backup = Workbooks("Quote Register.xlsx").Worksheets("Data").Range("A2").End(xlDown).Row() + 1
Workbooks("Quote Register.xlsx").Worksheets("Data").Range("A" & line_backup).Value = Range("QuoteNo1") & Range("Rev").Value

However when i try automate it and have the same VBA code open the file with the code below it wont work. The file opens but that's about it

Workbooks.Open "C:\Users\me\Documents\Quote Register.xlsx"

line_backup = Workbooks("Quote Register.xlsx").Worksheets("Data").Range("A2").End(xlDown).Row() + 1
Workbooks("Quote Register.xlsx").Worksheets("Data").Range("A" & line_backup).Value = Range("QuoteNo1") & Range("Rev").Value

[6StringJazzer]

I cannot say for sure without your file, but it looks like you have this code in a standard module (e.g., Module1). This means that all unqualified references default to ActiveWorkbook. When you open a new file, that file becomes ActiveWorkbook. However, it appears that you intend these to reference ThisWorkbook. This is why I discourage unqualified references in a standard module under most circumstances.

I would think if my diagnosis is correct, you would be getting error messages, but you don't mention that. But if you qualify the references this may start working for you.

Also, sometimes an error is not where you think it is, and it is always best to provide all of the code, not just the lines where you think there is a problem. And even better, provide the file for testing.

Change sheet name to match actual sheet name.

Workbooks.Open "C:\Users\me\Documents\Quote Register.xlsx"

line_backup = Workbooks("Quote Register.xlsx").Worksheets("Data").Range("A2").End(xlDown).Row() + 1
Workbooks("Quote Register.xlsx").Worksheets("Data").Range("A" & line_backup).Value = ThisWorkbook.Worksheets("Sample Sheet").Range("QuoteNo1") & ThisWorkbook.Worksheets("Sample Sheet").Range("Rev").Value