Issue With Copy from Multiple Sheets

GeoKoro13

New Member
Joined
Nov 24, 2016
Messages
27
Hi Guys,

I'm using the following code to copy the ranges below and paste the values from each sheet to the same row.

Code:
Sub SummurizeSheets()
    Dim ws As Worksheet
     
    Application.ScreenUpdating = False
    Sheets("Monthly_Report").Activate
     
    For Each ws In Worksheets
        If ws.Name <> "Monthly_Report" And ws.Name <> "Master_Sheet" And ws.Name <> "Default" Then
            ws.Range("B14").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C14").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B2").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B4").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 9).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D4").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 10).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("E4").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("A9").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 12).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B9").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 13).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C9").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 14).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C12").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D21").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 16).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C23").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 17).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D32").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 18).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("G12").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("H21").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 20).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("G23").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 21).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("H32").Copy
            Worksheets("Monthly_Report").Cells(Rows.Count, 22).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        End If
    Next ws
End Sub

The problem I'm having is with this coding if one Range is empty (for example they are drop down lists) then return nothing.
The next time I'll use the coding tho, if any of those Ranges have a value now will fill up the first empty cell in the column but it won't be at the "correct" row.

I guess you'll find this coding pretty basic so that is why I'm having this issue. Could you provide me with any solution?

Thanks
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You might consider the following (untested) code...

Code:
Sub SummurizeSheets()
    Dim ws As Worksheet
    Dim NextRow As Long
    
    Application.ScreenUpdating = False
    Sheets("Monthly_Report").Activate
     
    For Each ws In Worksheets
        If ws.Name <> "Monthly_Report" And ws.Name <> "Master_Sheet" And ws.Name <> "Default" Then
            NextRow = Worksheets("Monthly_Report").Cells.Find(What:="*", After:=Worksheets("Monthly_Report").Cells(1, 1), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row + 1
            ws.Range("B14").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 6).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C14").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 7).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B2").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 8).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B4").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 9).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D4").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 10).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("E4").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 11).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("A9").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 12).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B9").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 13).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C9").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 14).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C12").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 15).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D21").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 16).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C23").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 17).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D32").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 18).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("G12").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 19).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("H21").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 20).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("G23").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 21).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("H32").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 22).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        End If
    Next ws
End Sub

Cheers,

tonyyy
 
Upvote 0
You might consider the following (untested) code...

Code:
Sub SummurizeSheets()
    Dim ws As Worksheet
    Dim NextRow As Long
    
    Application.ScreenUpdating = False
    Sheets("Monthly_Report").Activate
     
    For Each ws In Worksheets
        If ws.Name <> "Monthly_Report" And ws.Name <> "Master_Sheet" And ws.Name <> "Default" Then
            NextRow = Worksheets("Monthly_Report").Cells.Find(What:="*", After:=Worksheets("Monthly_Report").Cells(1, 1), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row + 1
            ws.Range("B14").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 6).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C14").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 7).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B2").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 8).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B4").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 9).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D4").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 10).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("E4").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 11).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("A9").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 12).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B9").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 13).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C9").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 14).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C12").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 15).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D21").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 16).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C23").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 17).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D32").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 18).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("G12").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 19).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("H21").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 20).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("G23").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 21).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("H32").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 22).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        End If
    Next ws
End Sub

Cheers,

tonyyy

Thank you tony for your time.

I'll test it tomorrow and I'll let you know the outcome.
Cheers
 
Upvote 0
You might consider the following (untested) code...

Code:
Sub SummurizeSheets()
    Dim ws As Worksheet
    Dim NextRow As Long
    
    Application.ScreenUpdating = False
    Sheets("Monthly_Report").Activate
     
    For Each ws In Worksheets
        If ws.Name <> "Monthly_Report" And ws.Name <> "Master_Sheet" And ws.Name <> "Default" Then
            NextRow = Worksheets("Monthly_Report").Cells.Find(What:="*", After:=Worksheets("Monthly_Report").Cells(1, 1), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row + 1
            ws.Range("B14").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 6).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C14").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 7).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B2").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 8).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B4").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 9).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D4").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 10).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("E4").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 11).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("A9").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 12).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("B9").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 13).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C9").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 14).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C12").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 15).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D21").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 16).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C23").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 17).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("D32").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 18).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("G12").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 19).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("H21").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 20).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("G23").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 21).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("H32").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 22).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        End If
    Next ws
End Sub

Cheers,

tonyyy

Thanks Tony!! Works Great!!!!
 
Upvote 0
You're very welcome. Glad it worked out for you.
 
Upvote 0
You're very welcome. Glad it worked out for you.

Hey, although will sound weird but how I could copy ws.range("B1") but replace the text in column E?

So, what I mean is everytime I copy this cells from all the worksheets to replace the one I pasted before. For instance, 1st time will be 6 rows. The 2nd time will be 8 but the will replace the 6 older.
 
Upvote 0
I think you're asking to clear the old data from the Monthly Report before pasting in the new. If so, then add the line in red...

Code:
Sub SummurizeSheets()
    Dim ws As Worksheet
    Dim NextRow As Long
    
    Application.ScreenUpdating = False
    Sheets("Monthly_Report").Activate
    [COLOR=#ff0000]Sheets("Monthly_Report").UsedRange.Offset(1, 0).ClearContents 'Assumes headers in Row 1[/COLOR]
    For Each ws In Worksheets
        If ws.Name <> "Monthly_Report" And ws.Name <> "Master_Sheet" And ws.Name <> "Default" Then
            NextRow = Worksheets("Monthly_Report").Cells.Find(What:="*", After:=Worksheets("Monthly_Report").Cells(1, 1), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row + 1
            ws.Range("B14").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 6).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ws.Range("C14").Copy
            Worksheets("Monthly_Report").Cells(NextRow, 7).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 
Upvote 0
No, no legend... but thanks just the same. Again, you're very welcome.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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