Error when switching between workbooks

Patricia0923

New Member
Joined
Mar 18, 2014
Messages
25
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

Well-known Member
Joined
Oct 5, 2011
Messages
1,186
Try this,

Code:
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

New Member
Joined
Mar 18, 2014
Messages
25
Try this,

Code:
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

New Member
Joined
Mar 18, 2014
Messages
25
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
 

Forum statistics

Threads
1,081,418
Messages
5,358,559
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top