CODEWITHSUNDEEP
vbaexcel
Dan Robertson
Dan Robertson

Reputation: 3

Excel VBA Nested Loop Issue

really hoping someone can help me.

I'm new to VBA only started picking bits up a few weeks back. I'm trying to create a nested loop to consolidate data from multiple tabs in Excel.

This is based on the worksheet name beginning ">" and column 3 containing a True or False value. Data is then pasted into a sheet called import.

Both loops seem to work independently but I cant get them to work together.

Thanks in advance.

Sub TestImport()
Dim WS As Worksheet
Dim Criteria As Boolean
Dim C As Integer
Criteria = True
Application.ScreenUpdating = False
Sheets("Import").UsedRange.Offset(1).Clear
For Each WS In ThisWorkbook.Worksheets
If Left(WS.Name, 1) = ">" Then
    For C = 2 To WS.Range("A1000").End(xlUp).Row
    If WS.Cells(C, 3) = Criteria Then
        WS.Range(Cells(C, 5), Cells(C, 12)).Copy
        Worksheets("Import").Range("A100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        End If
        Next C
    End If
Next WS
Application.ScreenUpdating = True
Sheets("Import").Select
End Sub

Upvotes: 0

Views: 191

Answers (1)

basodre
basodre

Reputation: 5780

Since you are looping across the sheets in the workbook, you should fully qualify any ranges with which you are working. Specifically, the following line:

WS.Range(Cells(C, 5), Cells(C, 12)).Copy

should be changed to:

WS.Range(WS.Cells(C, 5), WS.Cells(C, 12)).Copy

Upvotes: 2

Related Questions