asked on 8/4/2021

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

VBA Microsoft Excel Microsoft Office

Last Comment

Martin Liss

8/8/2021

Amir Azhdari

8/4/2021

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

8/4/2021

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Martin Liss

8/8/2021

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

8/8/2021

ASKER

Thanks very much Martin

Martin Liss

8/8/2021

You're welcome.