Copy paste ranges from multiple sheets

Hi

I am new to VBA and need help on the following problem:

I have a workbook with 2 types of worksheets. 2 permanent worksheet in
the begining and 2 and the end of the work book. In between theses
worksheets are a variable amount of sheets which a user can create.

I need to copy and paste a specific range from the variable sheets into
a "Data" sheet (a permanent sheet) , one column for each sheet.

I have read a few books and this is what I Have so far

Sub copydna()
Dim lastsheet As Long
Dim rng As Range
lastsheet = Sheets.Count
For N = 3 To lastsheet - 3
Sheets(N).Activate
Set rng = Range("H49:H303")
Sheets("Data").Cells(1, 1).offset(0, 1).Value = rng.Value
Next
End Sub

It is not correct , I am missing the correct way to offset the range.

I will be greatful for any help.

Thanks

John

Sub copydna()
Dim lastsheet As Long
Dim rng As Range
lastsheet = Sheets.Count
For N = 3 To lastsheet - 3
Sheets(N).Activate
Set rng = Range("H49:H303")
Sheets("Data").Cells(1, 1).offset(0, N-3) _
.Resize(255,1).Value = rng.Value
Next
End Sub

--
regards,
Tom Ogilvy


"Woody1313" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I am new to VBA and need help on the following problem:
>
> I have a workbook with 2 types of worksheets. 2 permanent worksheet in
> the begining and 2 and the end of the work book. In between theses
> worksheets are a variable amount of sheets which a user can create.
>
> I need to copy and paste a specific range from the variable sheets into
> a "Data" sheet (a permanent sheet) , one column for each sheet.
>
> I have read a few books and this is what I Have so far
>
> Sub copydna()
> Dim lastsheet As Long
> Dim rng As Range
> lastsheet = Sheets.Count
> For N = 3 To lastsheet - 3
> Sheets(N).Activate
> Set rng = Range("H49:H303")
> Sheets("Data").Cells(1, 1).offset(0, 1).Value = rng.Value
> Next
> End Sub
>
> It is not correct , I am missing the correct way to offset the range.
>
> I will be greatful for any help.
>
> Thanks
>
> John
>

Thanks

Works great!!