Macro to Run Through All Worksheets with One Exception

Question

I am trying to write a macro that selects any rows with text in a particular column (doesn't matter what text) and then paste it into a Summary sheet, this needs to cycle through all the sheets in the workbook bar the summary sheet. However I am having problems getting it to work, I keep getting 'Compile Error: Method or Data Member not Found', I need the macro to run through all sheets regardless of name as the sheets are eventually archived and new ones added.

I have now changed the way the macro finds the cells with text in to the below, I don't know if this changes things:

Sub SmartCopy()

Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long, j As Long
Set s1 = Sheets("Customer 1")
Set s2 = Sheets("Action Summary")
N = s1.Cells(Rows.Count, "C").End(xlUp).Row
j = 2
For i = 6 To N
    If s1.Cells(i, "C").Value = "" Then
    Else
        s1.Cells(i, "C").EntireRow.Copy s2.Cells(j, 1)
        j = j + 1
    End If
Next i

End Sub

I am new to this and probably have it totally wrong but any help would be appreciated.

On which line does this error occur? – EngJon Jan 28 '15 at 14:11 — EngJon, Jan 28 '15 at 14:11
The second line 'ThisWorkbook.Worksheet' – user3514907 Jan 29 '15 at 07:51 — user3514907, Jan 29 '15 at 07:51

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

First STOP using .Select

Second, this should work based on your criteria of filtering and copying specific rows. You may need to tweak the filter or the rows that are copied, but this loop will work.

Sub TestIt()

Dim wsheet As Worksheet

For i = 1 To ThisWorkbook.Worksheets.Count
    If ThisWorkbook.Worksheets(i).Name <> "Action Summary" Then 'And ThisWorkbook.Worksheets(i).Name <> "Another Exception" And ...
        Set wsheet = ThisWorkbook.Worksheets(i)
        For j = 6 to wsheet.Range("A" & Rows.Count).End(xlUp).Row
            If Not wsheet.Range("C" & j).Value = "" Then
                wsheet.Range("C" & j).EntireRow.Copy
                Sheets("Action Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
            End If
        Next j
    End If
Next i

End Sub

edited May 23 '17 at 11:59

Community

1
1

answered Jan 28 '15 at 14:44

Chrismas007

6,085
4
24
47

You said tweak the filter/ copy, I have tried to but keep getting the error 'Runtime Error 1004 - We couldn't do this for the selected range of cells'. Is there a particular way I need to write the filter/ copy part for it to accept it? – user3514907 Jan 28 '15 at 15:38
No it isn't essential as I only want to copy rows with text in column C (doesn't matter what text) but I wasn't sure how to do it. – user3514907 Jan 29 '15 at 07:50
I managed to change my copy and paste macro and removed the filter (edited my original post to reflect this) but I'm now not sure how to work your answer into the new macro. – user3514907 Jan 29 '15 at 11:55
Works great, for future reference how would I add more than one exception sheet? Thank you – user3514907 Jan 29 '15 at 15:03

Macro to Run Through All Worksheets with One Exception

1 Answers1

Linked