Hi, can anybody help please... I am trying to make the code below pick
up the range of data.. 'A1 down to the last value on each sheet', 2
thru 56
and paste it in the sheet "S1"...but I cannot get the scroll bit
right...

thanks

ste

Sub cdata()
Dim StartCell, EndCell As Range

Application.ScreenUpdating = False
For sheetNumber = 2 To 56

SheetName = "S" & Format(sheetNumber, "##0")
Sheets(SheetName).Select

Set StartCell = ActiveSheet.Range("A1")

Application.Goto reference:=ActiveSheet.Cells(ActiveSheet.Range
_("A1").Value + 1, "A"), Scroll:=False
'struggling with this bit

Set EndCell = ActiveCell
Range(StartCell, EndCell).Select
Selection.Cut
Sheets("S1").Range("A65536").End(xlUp).PasteSpecia l
Paste:=xlPasteValues
Next
End Sub

Ste,

If your data is contiguous, starting in row 1, then you can simply use:

Sub cdata2()
Dim SheetNumber As Integer

Application.ScreenUpdating = False

For SheetNumber = 2 To 56
With Worksheets("S" & Format(SheetNumber, "##0"))
.Range(.Range("A1"), .Range("A1").End(xlDown)).Copy
End With
Sheets("S1").Range("A65536").End(xlUp)(2).PasteSpe cial _
Paste:=xlPasteValues
Next SheetNumber
End Sub

HTH,
Bernie
MS Excel MVP


"ste mac" wrote in message
om...
Hi, can anybody help please... I am trying to make the code below pick
up the range of data.. 'A1 down to the last value on each sheet', 2
thru 56
and paste it in the sheet "S1"...but I cannot get the scroll bit
right...

thanks

ste

Sub cdata()
Dim StartCell, EndCell As Range

Application.ScreenUpdating = False
For sheetNumber = 2 To 56

SheetName = "S" & Format(sheetNumber, "##0")
Sheets(SheetName).Select

Set StartCell = ActiveSheet.Range("A1")

Application.Goto reference:=ActiveSheet.Cells(ActiveSheet.Range
_("A1").Value + 1, "A"), Scroll:=False
'struggling with this bit

Set EndCell = ActiveCell
Range(StartCell, EndCell).Select
Selection.Cut
Sheets("S1").Range("A65536").End(xlUp).PasteSpecia l
Paste:=xlPasteValues
Next
End Sub

Hi Bernie, thanks a lot for your help, it was just what I needed...

cheers

ste