Error when switching between workbooks

[Patricia0923]

Hi,
I need to transfer data from a workbook named R2D2 to one named Matrix.
It runs after a change event (date data inputted into cell C1).

The code looks like this:

Dim R2D2data As String
Dim Matrix As String

R2D2data = "C:\Users\Owner\Desktop\Excel Projects\R2D2.xlsx"
Matrix = "Matrix Text.xlsm"

Workbooks.Open Filename:=R2D2data
Range("B2:G2").Select
Application.CutCopyMode = False
Selection.Copy

Workbooks(Matrix).Activate
Range("B4:G4").Select
ActiveSheet.Paste

Workbooks.(R2D2data).Activate I get a Run-time error '9' Subscript out of range error here
Range("B3:G3").Select
ActiveSheet.Paste

I need to be able to go back and forth to the R2D2data file many times to extract various pieces of data.

Any help would be appreciated

 

[vds1]

Try this,

Dim R2D2data As Workbook '// Define workbook Variable


Set R2D2data = Workbooks.Open("C:\Users\Owner\Desktop\Excel Projects\R2D2.xlsx") 'set the variable to R2D2.xlsx




Range("B2:G2").Select
Application.CutCopyMode = False
Selection.Copy


ThisWorkbook.Activate '// same as your Workbooks(Matrix).Activate
Range("B4:G4").Select
ActiveSheet.Paste


R2D2data.Activate
Range("B3:G3").Select
ActiveSheet.Paste

 

[Patricia0923]

Try this,

Dim R2D2data As Workbook '// Define workbook Variable


Set R2D2data = Workbooks.Open("C:\Users\Owner\Desktop\Excel Projects\R2D2.xlsx") 'set the variable to R2D2.xlsx




Range("B2:G2").Select
Application.CutCopyMode = False
Selection.Copy


ThisWorkbook.Activate '// same as your Workbooks(Matrix).Activate
Range("B4:G4").Select
ActiveSheet.Paste


R2D2data.Activate
Range("B3:G3").Select
ActiveSheet.Paste

Hi,
Thank you very much I will try it and see.
Right now my Excel 365 has just stopped working!?
I have this weird lock and key in the name box and the cells are all greyed out?

 

[Patricia0923]

Hi,
Thank you very much I will try it and see.
Right now my Excel 365 has just stopped working!?
I have this weird lock and key in the name box and the cells are all greyed out?

Hi again,
Just following up again - I added the code and it worked perfectly!!

Thanks so much!

Tricia

 

[vds1]

Hi again,
Just following up again - I added the code and it worked perfectly!!

Thanks so much!

Tricia

Thank you for your response. Have a nice day.