I'm trying to paste from one workbook with multiple sheets to several different workbooks. Example: Workbook "X" has 50 sheets labeled 1-50. I need each sheet copied to the respective workbook "Y". Each sheet in workbook "X" can have a different range of active cells that need to be copied over.
Examples:
workbook "X" sheet "1" copied to workbook "Y" sheet "Jan" (cell A2 to cell Z50)
workbook "X" sheet "2" copied to workbook "Y" sheet "Jan" (cell A2 to cell Z125)
Here is what I was trying with no luck and probably not even close.
Sub Data_Feb()
Dim x As Workbook
Dim y As Workbook
Set x = Workbooks.Open("workbook file path")
Set y = Workbooks.Open("workbook file path")
'Copy from workbook "X" & specific sheet
x.Sheets("584").Range ("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'Paste to workbook "Y"
y.Sheets("Feb").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Close & Save workbooks
x.Close
y.Save
y.Close
Examples:
workbook "X" sheet "1" copied to workbook "Y" sheet "Jan" (cell A2 to cell Z50)
workbook "X" sheet "2" copied to workbook "Y" sheet "Jan" (cell A2 to cell Z125)
Here is what I was trying with no luck and probably not even close.
Sub Data_Feb()
Dim x As Workbook
Dim y As Workbook
Set x = Workbooks.Open("workbook file path")
Set y = Workbooks.Open("workbook file path")
'Copy from workbook "X" & specific sheet
x.Sheets("584").Range ("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'Paste to workbook "Y"
y.Sheets("Feb").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Close & Save workbooks
x.Close
y.Save
y.Close