copy range of data from hidden sheet

[isatrist]

Hi All, Please Help!

I am trying to copy data from hidden sheet on one workbook to another workbook window open using a command button. The workbooks are on seperate drives.

data range to copy is from A2:Y200 from P:\folder\sheet1 (sheet1 is hidden and workbook not open)
paste to D3:AB200 into Z:\folder\sheet2 (Active window and sheet command button is on)

Everything i try gives a runtime error.
I tried just recording the macro and get an out of memory error.

Any suggestions would be greatly appreciated.

Right now I can't even get it to work with the hidden sheet visible as it gets hung up on the range to paste. Can you please adjust for me:

[Private Sub CommandButton1_Click()
Dim Wkb1 As Workbook
Dim Wkb2 As Workbook
Dim rngToCopy As Range
Dim rngToPaste As Range

' set the workbook you are copying to as active and give it a name for reference
Set Wkb1 = ActiveWorkbook

' open the workbook you are copying from and activate it
Set Wkb2 = Workbooks.Open(Filename:="C:\Users\Foldername\EA.xlsm")
Wkb2.Activate

With Worksheets("Data")
Set rngToCopy = .Range("A2", "Y200")
End With
With rngToCopy
Set rngToPaste = Worksheets("Matrix").Range("D3:AB200")
End With
rngToPaste.Value = rngToCopy.Value
End Sub \]

[arlu1201]

Please put your code in code tags before the moderators ask you to do so. In the meantime, i will look through your issue.

Also, you have specified worksheets("Data"), i think it will be good to mention which workbook it belongs to. Also, the worksheet Matrix should contain the name of the corresponding workbook.

[isatrist]

Hi Thanks for looking into this the "data" sheet is in workbook "EA.xlsm"
and the "Matrix" sheet is in workbook "IM.xlsm"

The IM.xlsm workbook holds the command button so is active the EA.xlsm needs to be opened and I would like the "Data" sheet to be hidden.
Thanks
PS how do I do the tags I am new to this forum.

[arlu1201]

Just put box bracket and code inside it. When you finish, put / and code inside box brackets.

[isatrist]

Hi okay so I figured out how to copy the data over with the "data" sheet visible. Now how do I change it to hide the "data" sheet? Here is what I have:

[Private Sub CommandButton1_Click()
Dim Wkb1 As Workbook
Dim Wkb2 As Workbook
Dim rngToCopy As Range
Dim rngToPaste As Range

' set the workbook you are copying to as active and give it a name for reference
Set Wkb1 = ActiveWorkbook

' open the workbook you are copying from and activate it
Set Wkb2 = Workbooks.Open(Filename:="C:\Users\Foldername\EA.xlsm")
Wkb2.Activate

With Worksheets("Data")
Set rngToCopy = .Range("A2", "Y200")
End With
With rngToCopy
Wkb1.Activate
Sheets("Matrix").Activate
Set rngToPaste = Worksheets("Matrix").Range("D3:AB200")
End With
rngToPaste.Value = rngToCopy.Value

' close the workbook you are copying from
Wkb2.Close

End Sub\]