Hi Guys,
I'm using the following code to copy the ranges below and paste the values from each sheet to the same row.
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
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