VBA code to copy ranges from 3 sheets into one summary sheet.

[mopo600]

Hi all,

Very new to VBA, would appreciate any help/feedback.

I have 3 sheets under one workbook all under the same formatted heading in source workbook (4. Summer 17 Master Chart). The sheets are named RETAIL, OUTLET, SPECIAL SALES.

I want to add a button in the sheet "COSTING LP" in my target workbook (NEW LINE PLAN TEMP). The button would copy all the data under the headings from all 3 source sheets and paste them all as values in the "DATA" tab in the target workbook so I can have one consolidated chart. The DATA tab has the same formatted headings as the source sheets. What is the best way to go about this?

Thanks!

 

[mumps]

What is the full name including extension of the source workbook containing sheets RETAIL, OUTLET, SPECIAL SALES? Do you want the data in the 3 source sheets to be copied underneath each other in the DATA sheet?

 

[mopo600]

The full name is 4. Summer 17 Master Chart. It's an xlsx if that is what you are asking?

Yes, I would like them to copied underneath each other.

 

[mumps]

Place this macro in your target workbook and run it from there. Make sure that both workbooks are open before running the macro.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim srcWb As Workbook
    Set srcWb = Workbooks("4. Summer 17 Master Chart.xlsx")
    Dim ws As Worksheet
    For Each ws In srcWb.Sheets
        ws.UsedRange.Offset(1, 0).Cells.Copy
        Sheets("DATA").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[mopo600]

Thank you, that is very helpful and almost exactly what I need. A couple of follow up questions

In my source wkbook, there are other tabs as well that I don't want copied. Am I able to specify just the 3 sheets I want copied instead of doing "for each ws" copying all the sheets.

Also, I don't want to copy all the titles and column headings. I want it to copy from ROW 13 and below on each sheet. How would I specify that?


Thanks again!

Place this macro in your target workbook and run it from there. Make sure that both workbooks are open before running the macro.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim srcWb As Workbook
    Set srcWb = Workbooks("4. Summer 17 Master Chart.xlsx")
    Dim ws As Worksheet
    For Each ws In srcWb.Sheets
        ws.UsedRange.Offset(1, 0).Cells.Copy
        Sheets("DATA").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim srcWb As Workbook
    Set srcWb = Workbooks("4. Summer 17 Master Chart.xlsx")
    Dim ws As Worksheet
    For Each ws In srcWb.Sheets(Array("RETAIL", "OUTLET", "SPECIAL SALES"))
        ws.UsedRange.Offset(12, 0).Cells.Copy
        Sheets("DATA").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[mopo600]

Thank you!!

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim srcWb As Workbook
    Set srcWb = Workbooks("4. Summer 17 Master Chart.xlsx")
    Dim ws As Worksheet
    For Each ws In srcWb.Sheets(Array("RETAIL", "OUTLET", "SPECIAL SALES"))
        ws.UsedRange.Offset(12, 0).Cells.Copy
        Sheets("DATA").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[mumps]

You are very welcome.

 

[mopo600]

I'm using the current code and it is very helpful, now I'm looking to add upon it but need some help to consolidate all the below.

If I wanted to add a 2nd source workbook to copy from, can that be done? or would i have to use another method? It has the same format as the first, with the same 3 tabs that I want to copy. the file name is "4. Fall 17 Master Chart.xlsx". So I would be copying all the info from 6 tabs in 2 workbooks into one "DATA" Tab

Before I run the macro each time, can the old copy/pasted info be deleted from previously running the macro? Everything below row 1 as row 1 has the headings. I can do this manually as it's not a big deal but if i can get it to do it automatically, that would be great.

Can I add a column or rename a heading cell in the Data tab "Division" and have it fill in the columns with a formula? I want it to look at cell B2 and if it the text starts with 7W or 7Q to display W, and if it starts with 7M or 7Y to display M.

Last, how would I add a pivot table for all the data that was copied into a new sheet


Thanks again!

 

[mumps]

Adding another source workbook and deleting the previous pasted data should not be a problem. Can you explain in detail what you mean by:

Can I add a column or rename a heading cell in the Data tab "Division" and have it fill in the columns with a formula? I want it to look at cell B2 and if it the text starts with 7W or 7Q to display W, and if it starts with 7M or 7Y to display M.

After the data from all the 6 sheets is copied to the "DATA" sheet, do you want to add a column to the "DATA" sheet with the header "Division" in row 1and then fill that column with the formula?