I currently have a series of worksheets which contain production data by shift (AM, PM & Nights, so 3 separate sheets per day). At the end of the week these are copied & pasted into a separate work book. What I want to do is just click on a button in the work book which copy's & pastes the currently open worksheet. I have the following which I came about by recording a macro:
Sub Macro1()
'
' Macro1 Macro
'
'
Windows("2021 01-07 AM.xlsx").Activate
ActiveWindow.SmallScroll Down:=-12
Range("A3:J34").Select
Selection.Copy
Windows("Wk 48 28-11-2021 to 30-11-2021.xlsx").Activate
Range("A6:A7").Select
ActiveSheet.Paste
End Sub
I know this will work for one sheet (2021 01-07AM.xlsx) but then won't work for any other sheets. The 1st range is fine as this will be the same for every sheet. The 2nd range, again I know won't work as I will need to paste into the next available row each time a new worksheet is opened, eg data from the worksheet 2021 01-07PM.xlsx will need to go underneath the data from the worksheet 2021 01-07AM.xlsx and so on.
Sub Macro1()
'
' Macro1 Macro
'
'
Windows("2021 01-07 AM.xlsx").Activate
ActiveWindow.SmallScroll Down:=-12
Range("A3:J34").Select
Selection.Copy
Windows("Wk 48 28-11-2021 to 30-11-2021.xlsx").Activate
Range("A6:A7").Select
ActiveSheet.Paste
End Sub
I know this will work for one sheet (2021 01-07AM.xlsx) but then won't work for any other sheets. The 1st range is fine as this will be the same for every sheet. The 2nd range, again I know won't work as I will need to paste into the next available row each time a new worksheet is opened, eg data from the worksheet 2021 01-07PM.xlsx will need to go underneath the data from the worksheet 2021 01-07AM.xlsx and so on.