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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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