Copy paste ranges from multiple sheets

[Woody1313]

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

 

[Tom Ogilvy]

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

 

[Woody1313]

Thanks

Works great!!