Copy paste repeat

[tio2chem]

I have a spreadsheet with over 80 tabs. I want to collect the contents of Columns B and C on each of those 80+ tabs and put it on 1 separate tab without copying and pasting 80+ times. In other words a macro but the macro recorder does seem to quite get the index to next tab and pasting at the bottom of the existing data.

Columns B (Name) + C (associated unique identifier number) are related and contain no more than 150 rows.
Any ideas on saving me 80+ copy/paste cycles?

I can always remove duplicates after I have all the data on one tab.

Thank you.

 

[Richard U]

a quick macro should do it....

assuming that the sheet you want to paste it to is the last sheet...

Sub SaveMyFingers()

Dim ws As Worksheet
Dim lng_ctr As Long

lng_ctr = 1


With Sheets(Sheets.Count)
For Each ws In ThisWorkbook.Worksheets

    .Range(.Cells(lng_ctr, 2).Address, .Cells(lng_ctr + 149, 3)).Value = _
    ws.Range(ws.Cells(1, 2).Address, ws.Cells(150, 3).Address).Value
    lng_ctr = lng_ctr + 150
Next ws
End With
End Sub

 

[tio2chem]

a quick macro should do it....

assuming that the sheet you want to paste it to is the last sheet...

Sub SaveMyFingers()

Dim ws As Worksheet
Dim lng_ctr As Long

lng_ctr = 1


With Sheets(Sheets.Count)
For Each ws In ThisWorkbook.Worksheets

    .Range(.Cells(lng_ctr, 2).Address, .Cells(lng_ctr + 149, 3)).Value = _
    ws.Range(ws.Cells(1, 2).Address, ws.Cells(150, 3).Address).Value
    lng_ctr = lng_ctr + 150
Next ws
End With
End Sub

Thank you