Home Looping through columns while summing the number of columns
Reply: 0

Looping through columns while summing the number of columns

user2383
1#
user2383 Published in August 15, 2018, 8:38 pm

Scenario: I am trying to loop across some worksheets, and for each of the I loop across rows. When I loop though a worksheet, I also paste the columns to a summary sheet, where I paste it starting at the next empty row of the summary worksheet.

Question: I tried a solution using a sum up of row variables in a for loop to do it, but it does not work. I suppose that, since it resets the variable at each worksheet, it ends up pasting data over other filled rows in the Summary.

Code:

Option explicit
Sub Source_Cleaner()

Dim wb As Workbook
Dim ws As Worksheet
Dim zz As Long, lrowsum As Integer

ThisWorkbook.Activate
Set wb = ThisWorkbook

'Optimize Macro Speed Start
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

lrowsum = 0

For Each ws In wb.Worksheets

    If ws.Name <> "Summary" Then

        For zz = 1 To ws.UsedRange.Rows.Count      
            If CBool(InStr(ws.Range("A" & zz).Value, "C")) Then                
                'Gathering to Summary
                wb.Sheets("Summary").Cells(zz + lrowsum, 1) = ws.Cells(zz, 1) 
                wb.Sheets("Summary").Cells(zz + lrowsum, 2) = ws.Cells(zz, 2) 

            ElseIf CBool(InStr(ws.Range("A" & zz).Value, "O")) Then
                'Gathering to Summary
                wb.Sheets("Summary").Cells(zz + lrowsum, 1) = ws.Cells(zz, 1) 
                wb.Sheets("Summary").Cells(zz + lrowsum, 2) = ws.Cells(zz, 2) 

            End If
            lrowsum = zz
            Next zz

End If
Next ws

'Optimize Macro Speed End
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub

OBS: In this code, I make lrowsum (which would be the total amount of rows, which are being summed as I iterate across sheets), and zz the row of the sheet. In the end of the sheet, I make lrowsum = zz, and in the next loop I sum both, which incur in the error explained previously.

Question: First of all, is there a better way to post to the next empty row of a worksheet directly?

Question2: How could I sum the variables at the end of each ws loop, to the get the correct value?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302924 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO