Help with For each Sheet next

[cprince]

Hi All,

I ve got a workbook with 3 sheets.

Sheet 1 is called "Total"
Sheet 2 is called "mary"
Sheet 3 is called "Jane"

I may have more sheets in the future, so I want it to loop until there are no more sheets.

I need to copy the range A1:A200 in Sheet2 and paste in Sheet1 then,

copy the range A1:A200 in Sheet3 and paste in Sheet1 at the bottom of pasted data.

For each sheet .

Can someone help me with some code for this?

 

[jindon]

Hi
something like

Sub test()
Dim ws As Worksheet, LastR As Range
Sheets("Total").Columns("a").ClearContents
For Each ws In WorkSheets
     If ws.Name <> "Total" Then
          With Sheets("Total")
              Set LastR = .Range("a" & Rows.Count).End(xlUp).Offset(1)
              If IsEmpty(.Range("a1")) Then Set LastR = .Range("a1")
          End With
          ws.Range("a1:a200").Copy LastR
     End If
Next
End Sub

 

[venkat1926]

try something like this
change to suit you

Sub test()
Dim i As Integer
Dim dest As Range
For i = 2 To ThisWorkbook.Worksheets.Count
Sheets(i).Activate
' for each sheet row 1 is the same header in all the sheets
Set dest = Sheets(1).Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
Range(Range("a2"), Range("a2").End(xlDown).End(xlToRight)).Copy dest
Next i
End Sub

venkat