Copy from multiple sheets to Master

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
442
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,

How do I copy all data from all sheets to the last row of those sheets based on column AL, excluding sheets Lookup, Menu, All Orders and All Delivered to the Master Sheet. Range would be from A:AL and I would only need to include the header from any of the other sheets once.

Many thanks
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi @thedeadzeds :

Please try this macro:

VBA Code:
Sub copydata()
  Dim sh As Worksheet, shM As Worksheet
  Dim headers As Boolean
  
  Application.ScreenUpdating = False
  
  Set shM = Sheets("Master")  'Fit to the name of your master sheet
  shM.Cells.ClearContents
  
  For Each sh In Sheets
    Select Case sh.Name
      Case "Master", "Lookup", "Menu", "All Orders", "All Delivered"  'Fit the name of the sheets to exclude
      
      Case Else
        If headers = False Then
          headers = True
          sh.Rows(1).Copy shM.Rows(1)
        End If
        sh.Range("A2:AL" & sh.Range("AL" & Rows.Count).End(3).Row).Copy
        shM.Range("A" & shM.Range("AL" & Rows.Count).End(3).Row + 1).PasteSpecial xlPasteValues
    End Select
  Next
  Application.CutCopyMode = False
End Sub


Let me know if it's what you need, I'll get back to you as soon as I can.
Cordially
Dante Amor
----- --
 
Upvote 0
Solution
Hi @thedeadzeds :

Please try this macro:

VBA Code:
Sub copydata()
  Dim sh As Worksheet, shM As Worksheet
  Dim headers As Boolean
 
  Application.ScreenUpdating = False
 
  Set shM = Sheets("Master")  'Fit to the name of your master sheet
  shM.Cells.ClearContents
 
  For Each sh In Sheets
    Select Case sh.Name
      Case "Master", "Lookup", "Menu", "All Orders", "All Delivered"  'Fit the name of the sheets to exclude
     
      Case Else
        If headers = False Then
          headers = True
          sh.Rows(1).Copy shM.Rows(1)
        End If
        sh.Range("A2:AL" & sh.Range("AL" & Rows.Count).End(3).Row).Copy
        shM.Range("A" & shM.Range("AL" & Rows.Count).End(3).Row + 1).PasteSpecial xlPasteValues
    End Select
  Next
  Application.CutCopyMode = False
End Sub


Let me know if it's what you need, I'll get back to you as soon as I can.
Cordially
Dante Amor
----- --

Amazing thanks so much
 
Upvote 1

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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
Back
Top