Help: Macro for copying old workbook columns to new workbook

[macrosforbros]

Hey guys so im struggling with making a macro:


To start; i get inventory lists that don't have my data on it and have to go copy columns from multiple tabs. I was creating the macro below but the problem am having is:
How can i make this work on any 2 workbooks i have (ex: this one is copying data from the 26th to the 27th, but tomorrow i want this to copy over data from the 27th to the 28th)
Is there a way to reference the active workbooks; say the new one is open in front of the old workbook. I just want it to work on any 2 workbooks without having to reference there file names.

Sub Formatting()
'
'?Inventory Macro
' Copy over old data to new workbook
'


'
Windows("Inventory 7-27-19.xlsx").Activate
Windows("Inventory 7-26-19.xlsx").Activate
Sheets("D1 ANC").Select
Columns("K:Q").Select
Selection.Copy
Windows("Inventory 7-27-19.xlsx").Activate
Sheets("D1 ANC").Select
Columns("K:K").Select
ActiveSheet.Paste
Windows("Inventory 7-26-19.xlsx").Activate
Sheets("D1 PTU").Select
Columns("K:Q").Select
Selection.Copy
Windows("Inventory 7-27-19.xlsx").Activate
Sheets("D1 PTU").Select
Columns("K:K").Select
ActiveSheet.Paste
Windows("Inventory 7-26-19.xlsx").Activate
Sheets("E1 PTU").Select
Columns("K:Q").Select
Selection.Copy
Windows("Inventory 7-27-19.xlsx").Activate
Sheets("E1 PTU").Select
Columns("K:K").Select
ActiveSheet.Paste

End Sub

(there's more sheets but didn't want to include them since they are all the same)

 

[JoeMo]

Hey guys so im struggling with making a macro:


To start; i get inventory lists that don't have my data on it and have to go copy columns from multiple tabs. I was creating the macro below but the problem am having is:
How can i make this work on any 2 workbooks i have (ex: this one is copying data from the 26th to the 27th, but tomorrow i want this to copy over data from the 27th to the 28th)
Is there a way to reference the active workbooks; say the new one is open in front of the old workbook. I just want it to work on any 2 workbooks without having to reference there file names.

When Excel starts up, if you have a personal workbook it will be opened and hidden. Excel assigns an index of 1 to it. As you open additional workbooks, Excel assigns an index to them sequentially. So, if you have a personal workbook, when you open the old workbook it will have index 2. If you then open a new workbook it will have index 3. If there is no personal workbook then the old workbook will be index 1 and the new workbook index 2. Here's an example of referencing by index number.

Sub ReferenceOldAndNewWorkbooks()
Dim i As Long
For i = 1 To Workbooks.Count
    If Workbooks(i).Name <> "PERSONAL.XLSB" Then
        Workbooks(i).Activate
        MsgBox "Workbooks(" & i & ")" & " is " & Workbooks(i).Name
    End If
Next i
End Sub