Simple scroll and select?

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).PasteSpecial
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).PasteSpecial _
Paste:=xlPasteValues
Next SheetNumber
End Sub

HTH,
Bernie
MS Excel MVP


"ste mac" <[email protected]> wrote in message
news:[email protected]...
> 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).PasteSpecial
> Paste:=xlPasteValues
> Next
> End Sub

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

cheers

ste