asked on

Excel VBA Copy data from two columns into new sheet

Hi

I have a spreadsheet with a number of sheets. There are two column in different positions.
Let's call them Column A and Column B.
How do I loop through all sheets using Excel VBA, locate these columns and copy them under each other
in a new sheet that lists all data in these columns

Amir Azhdari

Lets say Sheet3 is the new sheet that lists all data in those columns:
Please find attached:
Copy to Sheets.xlsm

Sub ReferToAnotherCell()
Dim RRow As Long, RDateCol As Long, N As Long, C As Long
C = 1
For N = 1 To ActiveWorkbook.Sheets.Count - 1
For PRow = 1 To ActiveWorkbook.Sheets(N).UsedRange.Rows.Count
ActiveWorkbook.Worksheets("Sheet3").Cells(C, 1).Value = ActiveWorkbook.Sheets(N).Cells(PRow, 1).Value
ActiveWorkbook.Worksheets("Sheet3").Cells(C, 2).Value = ActiveWorkbook.Sheets(N).Cells(PRow, 2).Value
C = C + 1
Next PRow

Next N

End Sub

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

I’m glad I was able to help and thank you for the testimonial.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Distinguished Expert in Excel 2018
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
       Experts Exchange Top Expert VBA 2018 to 2020

Murray Brown

ASKER

Thanks very much Martin

Martin Liss

You're welcome.