Copying and Pasting Between Different Workbooks

[tobermory]

Hi all,

I have a 'holding' spreadsheet, which basically holds all my necessary macros.

A lot of these macros, involves copying and pasting between different different workbooks, on a monthly basis, and the file name of the book includes the month I'm working on, and is often dynamic from month to month anyway. For example...

Windows("Data Apr18.xlsx").Activate
Sheets("Data").Select
Range("L2:L20001").Select
Selection.Copy
Windows("Data2 Apr18 v1.xlsx").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

This means I have to go into my macros and replace workbook names it copy/pastes from, every month.

Would there be a more user friendly way around this?

I'm not sure how feasible this is, but I was thinking if the name of one workbook was in Cell A1 (Sheet1), with the name of the second workbook in Cell A2 (Sheet1).

 

[JLGWhiz]

If you always run macro at mid month then this should work.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Workbooks("Data " & Format(Date, "mmmyy") & ".xlsx").Sheets("Data")
Set sh2 = Workbooks("Data2 " & Format(Date, "mmmyy") & "v1.xlsx").Sheets(1)
sh1.Range("L2:L20001").Copy
sh2.Range("B2").PasteSpecial xlPasteValues
End Sub