CODEWITHSUNDEEP
vbaexcel
jabezs09
jabezs09

Reputation: 1

Code wont loop through sheets as well as rows

I am trying to write code to loop through all sheets in a workbook, apart from 1, and add a column which is a concatenation of 3 others. This seems to loop through all the rows for one worksheet, but not the others in the book

Sub addConcats()
  Dim sh As Worksheet
  Dim rw As Range
  Dim RowCount As Integer

  'Run through worksheets
  Dim x As Long
  Sheet1.Select
  For x = 2 To ThisWorkbook.Sheets.Count
    If Sheets(x).Name <> "VAT Transaction Report" Then Sheets(x).Select 
    Replace:=False
    Dim LastRow As Long
    LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

    For y = 2 To LastRow
      'Concat
      ActiveSheet.Cells(y, 20).Value = ActiveSheet.Cells(y, 7).Value & 
ActiveSheet.Cells(y, 9).Value & ActiveSheet.Cells(y, 12).Value
    Next y
  Next x
End Sub

Upvotes: 0

Views: 40

Answers (3)

Shai Rado
Shai Rado

Reputation: 33672

Try the code below, for your For loop to take into consideration the Sheets(x) you are trying to update:

Dim LastRow As Long

For x = 2 To ThisWorkbook.Sheets.Count
    If Sheets(x).Name <> "VAT Transaction Report" Then
        With Sheets(x)                            
            LastRow = .UsedRange.Row - 1 + .UsedRange.Rows.Count            
            For y = 2 To LastRow
                'Concat
                .Cells(y, 20).Value = .Cells(y, 7).Value & .Cells(y, 9).Value & .Cells(y, 12).Value
            Next y
        End With
    End If
Next x

Upvotes: 1

Rik Sportel
Rik Sportel

Reputation: 2679

The problem is that you select a sheet, but do not use Sheet.Activate. Next you use ActiveSheet. It is better to avoid selecting sheets altogether and just work against a Worksheet object (sh variable)

Try the following:

Sub addConcats()

Dim sh As Worksheet
Dim x As Integer
Dim y As Integer
Dim LastRow As Long

For x = 1 To ThisWorkbook.Sheets.Count
    Set sh = Sheets(x)
    If sh.Name <> "VAT Transaction Report" Then
        LastRow = sh.UsedRange.Rows.Count
        For y = 2 To LastRow
        'Concat
            sh.Cells(y, 20).Value = sh.Cells(y, 7).Value & sh.Cells(y, 9).Value & sh.Cells(y, 12).Value
        Next y
    End If
Next x

End Sub

Upvotes: 0

Dave
Dave

Reputation: 4356

No need to select each worksheet for this, or run with x and y.

Sub addConcats()

Dim sh As Worksheet
Dim LastRow As Long
For Each sh in ThisWorkbook.Worksheets
    If sh.Name <> "VAT Transaction Report" Then 
        LastRow = sh.Cells(sh.Rows.Count, 1).End(xlUp)
        For y = 2 To LastRow
            'Concat
            sh.Cells(y, 20).Value = sh.Cells(y, 7).Value & sh.Cells(y, 9).Value & sh.Cells(y, 12).Value
        Next y
    End If
Next 
End Sub

Upvotes: 1

Related Questions