Worksheet loop

[FROGGER24]

Hi Everyone, I requesting some help to modify some code. How can I modify "Worksheets("Sheet1")." so the code will run on numerous worksheets in workbook (up to 50 worksheets). I would also need the code to stop running if on the current worksheet cells C7 & C11 are blank.

Worksheets("Summary Sheet").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("D34").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 2).End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("c7").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 5).End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("e30").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 6).End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("e28").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 7).End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("e15").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 8).End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("f28").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 9).End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("e29").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 10).End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("e24").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 11).End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("f29").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 12).End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("AA1").Value

Thanks for any help, and Merry Christmas

 

[maabadi]

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
if ws.name = "Summary Sheet" Then
Else
If ws.Range("C7").Value = ""  And ws.Range("C11").Value = "" Then
Else
Worksheets("Summary Sheet").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = ws.Range("D34").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 2).End(xlUp).Offset(1).Value = ws.Range("c7").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 5).End(xlUp).Offset(1).Value = ws.Range("e30").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 6).End(xlUp).Offset(1).Value = ws.Range("e28").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 7).End(xlUp).Offset(1).Value = ws.Range("e15").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 8).End(xlUp).Offset(1).Value = ws.Range("f28").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 9).End(xlUp).Offset(1).Value = ws.Range("e29").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 10).End(xlUp).Offset(1).Value = ws.Range("e24").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 11).End(xlUp).Offset(1).Value = ws.Range("f29").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 12).End(xlUp).Offset(1).Value = ws.Range("AA1").Value
End If
End If
Next ws

 

[FROGGER24]

Thanks for the reply. I am getting Runtime error 1004, "Application-defined or object-defined error"

Worksheets("Summary Sheet").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = ws.Range("D34").Value

 

[FROGGER24]

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
if ws.name = "Summary Sheet" Then
Else
If ws.Range("C7").Value = ""  And ws.Range("C11").Value = "" Then
Else
Worksheets("Summary Sheet").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = ws.Range("D34").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 2).End(xlUp).Offset(1).Value = ws.Range("c7").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 5).End(xlUp).Offset(1).Value = ws.Range("e30").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 6).End(xlUp).Offset(1).Value = ws.Range("e28").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 7).End(xlUp).Offset(1).Value = ws.Range("e15").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 8).End(xlUp).Offset(1).Value = ws.Range("f28").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 9).End(xlUp).Offset(1).Value = ws.Range("e29").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 10).End(xlUp).Offset(1).Value = ws.Range("e24").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 11).End(xlUp).Offset(1).Value = ws.Range("f29").Value
Worksheets("Summary Sheet").Cells(Rows.Count, 12).End(xlUp).Offset(1).Value = ws.Range("AA1").Value
End If
End If
Next ws

Your solution worked perfectly, thank you, Merry Christmas to all

 

[maabadi]

You're Welcome & thanks for Feedback.