Copy & Paste worksheet into a workbook

Jones54

New Member
Joined
Jan 25, 2016
Messages
19
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.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
4,091
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
@mumps I get an error 91 on the 'LastRow = ...' line when testing your code.
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,423
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
What is the error message?
 
Upvote 0

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
4,091
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Capture.PNG
 
Upvote 0

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,259
Office Version
  1. 2016
Platform
  1. Windows
I don't see any problem with @mumps code. However, you need to note that the code copy range is from A1 to last occupied row and column which may have caused the problem.

I can see from you original code you were copying from A3 to J34. Basically you want to copy from A3 to J & last occupied row. Then the paste destination is pivoted at A6(? ... you selected A6 and A7)
So, the modification to the code should be
VBA Code:
Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, wb As Workbook
    Dim desWS As Worksheet
    Set desWS = ThisWorkbook.Sheets(1)
    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            With wb.Sheets(1)
                LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'                lcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                LastDestRow = desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Row
                If LastDestRow < 7 Then
                    .Range(.Cells(3, 1), .Cells(LastRow, "J")).Copy desWS.Cells(7, "A")
                Else
                    .Range(.Cells(3, 1), .Cells(LastRow, "J")).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
                End If
            End With
        End If
    Next wb
    Application.ScreenUpdating = True
End Sub

I remark lcol since the last column is fixed at J. If last column is dynamic, then lcol code is to be used.

My code was with workbook closed but will not work either because I was thinking the AM, PM and Night was in the same workbook but separate sheets ?
 
Last edited:
Upvote 0

Jones54

New Member
Joined
Jan 25, 2016
Messages
19
Thanks everybody for sharing your knowledge. Zot I have tried the code but still get the same error as I did before "Microsoft Visual Basic for Applications, with a Red Stop symbol with the code 400" when I run the macro. Just to confirm I had the worksheets for the AM, PM & Nights all open, along with the workbook "wk 48 28-11-2021 t.o 30-11-2021".
 
Upvote 0

Forum statistics

Threads
1,186,314
Messages
5,957,146
Members
438,290
Latest member
bradmsg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top