Excel Macro to Copy data from multiple worksheets to master sheet..please help.

A

anandt23

New Member
Joined
May 11, 2016
Messages
11
Hi
I need help in writing a macro to copy information from multiple worksheets into one master worksheet, within the same workbook.

I have multiple worksheets which are my sales invoices, and a master sheet where I would like to copy all the line items from my invoices, including the invoice number and invoice date.

In each of my invoices I would like to copy the data within the section B18:N28, but only the rows that have data, not the blank rows.
I would also like to copy the section B32:N41, also only the rows with data not the blank rows.

And paste this data into the master sheet.

But I would also like to copy the invoice date which is located in cell M10 and the Invoice Number which is in cell N11 to be pasted against each of these lines.

And I would like to repeat this for all worksheets within the workbook.

Could anyone help me with writing a macro for this please?

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
 
Sort by date Sort by votes

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Here you go mate, try this http://www.filedropper.com/sampleapril2016invoicesv20_1

Probably took a much longer way round that other may have done, but it works smoothly.

Assuming all ranges of invoices are always the same, if you want to add a new invoice just take the following code and paste it into MS Word, click replace and replace "NewSheetName" with the name of the sheet name of the new invoice. then name and apply to button on new invoice sheet accordingly.

Code: 
Sub NewInvoice_To_Master()
Application.ScreenUpdating = False
Dim LastRow As Long, ws As Worksheet
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B18")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K18")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L18")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N18")
    ws.Range("H" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B32")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L32")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N32")
    If Sheets("NEWSHEETNAME").Range("B19") <> "" Or Sheets("NEWSHEETNAME").Range("L33") <> 0 Then
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B19")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K19")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L19")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N19")
    ws.Range("H" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B33")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L33")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N33")
    End If
    If Sheets("NEWSHEETNAME").Range("B20") <> "" Or Sheets("NEWSHEETNAME").Range("L34") <> 0 Then
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B20")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K20")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L20")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N20")
    ws.Range("H" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B34")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L34")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N34")
    End If
    If Sheets("NEWSHEETNAME").Range("B21") <> "" Or Sheets("NEWSHEETNAME").Range("L35") <> 0 Then
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B21")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K21")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L21")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N21")
    ws.Range("H" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B35")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L35")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N35")
    End If
    If Sheets("NEWSHEETNAME").Range("B22") <> "" Or Sheets("NEWSHEETNAME").Range("L36") <> 0 Then
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B22")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K22")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L22")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N22")
    ws.Range("H" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B36")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L36")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N36")
    End If
    If Sheets("NEWSHEETNAME").Range("B13") <> "" Or Sheets("NEWSHEETNAME").Range("L37") <> 0 Then
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B23")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K23")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L23")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N23")
    ws.Range("H" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B37")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L37")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N37")
    End If
    If Sheets("NEWSHEETNAME").Range("B24") <> "" Or Sheets("NEWSHEETNAME").Range("L38") <> 0 Then
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B24")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K24")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L24")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N24")
    ws.Range("H" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B38")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L38")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N38")
    End If
    If Sheets("NEWSHEETNAME").Range("B25") <> "" Or Sheets("NEWSHEETNAME").Range("L39") <> 0 Then
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B25")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K25")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L25")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N25")
    ws.Range("H" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B39")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L39")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N39")
    End If
    If Sheets("NEWSHEETNAME").Range("B26") <> "" Or Sheets("NEWSHEETNAME").Range("L40") <> 0 Then
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B26")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K26")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L26")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N26")
    ws.Range("H" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B40")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L40")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N40")
    End If
    If Sheets("NEWSHEETNAME").Range("B27") <> "" Or Sheets("NEWSHEETNAME").Range("L41") <> 0 Then
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B27")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K27")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L27")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N27")
    ws.Range("H" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B41")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L41")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N41")
    End If
    If Sheets("NEWSHEETNAME").Range("B28") <> "" Then
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N11")
    ws.Range("B" & LastRow).Value = Sheets("NEWSHEETNAME").Range("M10")
    ws.Range("C" & LastRow).Value = Sheets("NEWSHEETNAME").Range("B28")
    ws.Range("D" & LastRow).Value = Sheets("NEWSHEETNAME").Range("K28")
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L28")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N28")
    End If
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 2
    ws.Range("E" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L30")
    ws.Range("F" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N30")
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L43")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N43")
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("I" & LastRow).Value = Sheets("NEWSHEETNAME").Range("L45")
    ws.Range("J" & LastRow).Value = Sheets("NEWSHEETNAME").Range("N45")
    Set ws = Sheets("Master")
    LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    ws.Range("J" & LastRow).Value = " "
   
    Range("C10:E15,H10:I13,M13:N13").Select
    Range("M13").Activate
    Range("C10:E15,H10:I13,M13:N13,B18:M28").Select
    Range("B18").Activate
    Range("C10:E15,H10:I13,M13:N13,B18:M28,B32:M41").Select
    Range("B32").Activate
    Selection.ClearContents
    Range("C10:E10").Select
End Sub
 
Last edited:
Upvote 0
Thanks for that..saved me alot of time and work. I ended up editing the code slightly by putting in a worksheet loop so it does it for all invoices in the workbook so no need to have multiple macros for each worksheet. Works a treat now.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Copy and paste multiple matching rows to a new sheet
Replies
7
Views
146
SamKhem
S
Kaz09
  • Question
Copy rows from multiple sheets based on data
Replies
1
Views
175
Fluff
Fluff
J
  • Question
Copy Paste Multiple Worksheets w/ variable ranges into 1 master sheet vba code
Replies
0
Views
290
Justin_G
J
GMC The Macro Man
  • Solved
VBA Script - Return to named file
Replies
6
Views
202
mumps
mumps
H
  • Solved
Consolidate data from selected sheets into master sheet & automatically update data.
Replies
8
Views
710
Joe4
Joe4

Forum statistics

Threads
1,215,504
Messages
6,125,183
Members
449,212
Latest member
kenmaldonado

Share this page

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