Macro help to consolidate cells with data into Master

Barbare11a

New Member
Joined
Jun 27, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi,

I've got a file with multiple tabs that I'd like to copy-paste values from into a summary tab. I found the thread VBA - Copy and Paste Range From Multiple Sheets into Summary from Oct 2021 and The response from My Answer is This is brilliant and has solved 95% of my query.

My data is a standard table from B:S that pulls from other locations. To ensure we have all the data coming across, there are lots more rows than required, and the rows just return 0s when the base data is finished.

The below code will copy a set range from each nominated sheet. What I would like to do is have it copy and paste-values for all cells where there is "active data", IE not a row of 0s.

I think there may be a few ways to do this, but am a bit out of my depth and the things I have tried have failed:

Option1 - search down column B until you find a 0, then copy everything from B3: downwards to the row before finding the 0
Option2 - Copy all active cells (anything in column B) and then another macro? to search the summarised data (Tab = "Summary") and delete all rows where b=0

Any help is greatly appreciated!

Current VBA Code:
VBA Code:
Sub Consolidate_States()
On Error GoTo M
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
Dim lastRow As Long
Dim Lastrowa As Long
lastRow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = 8

For i = 2 To lastRow
    ans = Sheets("Master").Cells(i, 1).Value
    
    With Sheets(ans)
        .Range("A3:S238").Copy
        Sheets("Summary").Cells(Lastrowa, 1).PasteSpecial xlPasteValues
        Lastrowa = Sheets("Summary").Cells(Rows.Count, "b").End(xlUp).Row + 1
    End With
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You tried to use a sheet name that does not exist" & vbNewLine & "Or we had another problem"
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,175,794
Messages
5,899,527
Members
434,779
Latest member
Mr1510

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
Top