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?
[TABLE="width: 724"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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

D
  • Solved
VBA macro/button to copy data from multiple workbooks into master workbook
Replies
5
Views
393
John_w
J
P
  • Question Question
VBA to copy entire columns from one spreadsheet and insert the copied columns into multiple spreadsheets within many subfolders in a directory
Replies
0
Views
37
ParrBee
P
R
  • Question Question
Checkbox problems
2
Replies
12
Views
506
randomdrums
R
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
588
NdNoviceHlp
NdNoviceHlp
P
  • Question Question
For Each Loop with copy and paste over multiple worksheets
Replies
1
Views
179
Sam_D_Ben
Sam_D_Ben

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