Excel VBA Nested Loop Issue

Question

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

I cant get them to work together. - What exactly isn't working? — SierraOscar
– SierraOscar, Commented May 4, 2016 at 13:33

interesting-name-here · Accepted Answer · 2016-05-04 13:36:55Z

2

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

edited May 4, 2016 at 13:36

interesting-name-here

1,8901 gold badge22 silver badges35 bronze badges

answered May 4, 2016 at 13:34

basodre

5,7901 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Robertson Over a year ago

Perfect! Thank you

Collectives™ on Stack Overflow

Excel VBA Nested Loop Issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related