VBA - Copying & Pasting Data from one Workbook to Another

[JohnGow383]

Hi,
I'm really stuck and I don't know what to do. I have one workbook (lets say WB1) which populates a table when clicking a command button. Afterwards a selection is copied from the first WB and the 2nd WB is opened (WB2). On the 2nd WB I have a event Open macro which then finds the last row in Column C (with offset 1) and selects. I then want to copy and paste values (the selection is 5 columns wide and 2 rows down) however I keep getting run-time errors saying the copy and paste area aren't the same size. They are. If I kill the macro, the selected cell is correct and clicking paste values manually works perfectly.
I can't reference the WB1 (where the data came from) as this spreadsheet changes name on a daily basis.

Any ideas what's going wrong? Is it all happening too quick? I dont see why.
Thanks

 

[JohnGow383]

Here is the code on WB2 for selecting the last row in column C and pasting the stored values from the 1st WB

Sub ImportData()

Dim ws As Worksheet
Dim lastrowC As Long
Dim ImportRng As Range

Set ws = ThisWorkbook.Worksheets("Voyage Boil Off History")

lastrowC = ws.Cells(Rows.Count, "C").End(xlUp).Row

Cells(lastrowC, 3).Offset(1, 0).Select

 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False 'Pastes values from Noon sheet

End Sub

 

[JohnGow383]

After doing some more tests, it would appear that the Workbook_Open sub does not seem to work when the first WB is still open. If I close the first WB and open the 2nd one manually then it works okay.
Any ideas anyone? As mentioned, if I could reference the first WB it would be easier but I can't as the first WB name changes daily. Is the code outpacing the opening of the WB? ie trying to exectute before the WB is open, but why would the first WB stop the code from executing?

 

[JohnGow383]

I've solved it. I'm an idiot. I was using

FollowHyperlink Address

to open the WB2 instead of

Workbooks.Open

. Whilst it opens the WB no problems it doesn't trigger the Workbook_Open code.