How to copy data from two different sheets in excel VBA dynamically?

Question

I have two different worksheets. I have to copy the first column(A) from first sheet. Based upon the string in the first column, copy the data form the first column, copy the second column(B) from first sheet, copy the second column(B) from second sheet and then find out the difference between the two copied columns. All this data will be pasted into a new sheet. Repeat the process to copy the third column(C) from first sheet, copy the third column(C) from second sheet and then find out the difference. This process is repeated until the last column.

How to make the code dynamic such that it looks up the data in the first column in bot the sheets and then copies the data from the other columns.

I was able to make this code work with WWC's assistance, but how to look up the data in the first column and then copy values.

    Sub Macro4()
'
' Macro4 Macro
'

'
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Dim coli As Double
Dim Coli3 As Double
Dim rowy As Double

Dim numCols As Double
Dim startRow As Double
Dim lastRow As Double

startRow = 6 'assuming data starts here
Coli3 = 2 ' start the columns out on ws3

Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")
Set ws3 = ThisWorkbook.Worksheets("Comparison")

Application.ScreenUpdating = False

ws3.Cells.Clear


ws1.Range("A1").EntireColumn.Copy Destination:=ws3.Range("A1")

'Find how many columns there are in sheet1 based on data in row 1
numCols = ws1.Cells(7, Columns.Count).End(xlToLeft).Column
For coli = 2 To numCols
    'Find last Data row in the given column in sheet1
    lastRow = ws1.Cells(ws1.Rows.Count, coli).End(xlUp).Row

    For rowy = 6 To lastRow
        ws3.Cells(rowy, Coli3) = Format(ws1.Cells(rowy, coli).Value, "#,##0") ' copy sheet 1 to the right spot of sheet 3
        ws3.Cells(rowy, Coli3 + 1) = Format(ws2.Cells(rowy, coli).Value, "#,##0") 'copy sheet 2 to the right spot of sheet 3
        'perform calculation and place in the right spot on sheet 3
        If rowy = "6" Then
            ws3.Cells(rowy, Coli3) = ws1.Cells(rowy, coli) & "-Sheet1" ' copy sheet 1 to the right spot of sheet 3
            ws3.Cells(rowy, Coli3 + 1) = ws2.Cells(rowy, coli) & "-Sheet2" 'copy sheet 2 to the right spot of sheet 3
            ws3.Cells(rowy, Coli3 + 2) = "Difference"
        Else
            ws3.Cells(rowy, Coli3) = Format(ws1.Cells(rowy, coli).Value, "#,##0") ' copy sheet 1 to the right spot of sheet 3
            'ws3.Cells(rowy, Coli3).Font.Name = "Arial"
            'ws3.Cells(rowy, Coli3).Font.Size = 8
            ws3.Cells(rowy, Coli3 + 1) = Format(ws2.Cells(rowy, coli).Value, "#,##0") 'copy sheet 2 to the right spot of sheet 3
            'ws3.Cells(rowy, Coli3 + 1).Font.Name = "Arial"
            'ws3.Cells(rowy, Coli3 + 1).Font.Size = 8
            ws3.Cells(rowy, Coli3 + 2) = Format((ws1.Cells(rowy, coli).Value) - (ws2.Cells(rowy, coli).Value), "#,##0")
            'ws3.Cells(rowy, Coli3 + 2).Font.Name = "Arial"
            'ws3.Cells(rowy, Coli3 + 2).Font.Size = 8
        End If

    Next rowy ' move to the next row on ws1, ws2, ws3

    'Since we are placing 3 cols at a time in sheet 3 we increment differently
    Coli3 = Coli3 + 3 '1 becomes 4, 4 becomes 7, 7 becomes 10 and so on


End Sub

Data in the Sheets

What have you tried? There are myriad examples of loops with VBA online. — BruceWayne, Dec 27 '17 at 18:45
I have posted my code here. It works fine but I am not able to put it in a for loop. I am not able to define numbers in the range to copy the entire column — redds1, Dec 27 '17 at 19:26
If your problem is referencing a range dynamically, you might want to look into using Cells().....See http://excelmatters.com/referring-to-ranges-in-vba/ — Fred, Dec 27 '17 at 19:37
I tried to replace: ws1.Range("B1").EntireColumn.Copy Destination:=ws3.Range("B1") with ws1.Range.Cells(2).EntireColumn.Copy Destination:=ws3.Range(2) ws1.Range(2, 1).EntireColumn.Copy Destination:=ws3.Range(2, 1) it does not work — redds1, Dec 27 '17 at 19:47
As Fred has mention in the comments, use `.Cells()` this will complete your task — Maldred, Dec 27 '17 at 21:22
ws1.Range(Cells(1, 2), Cells(lastrow, 2)).EntireColumn.Copy Destination:=ws3.Range(Cells(1, 2), Cells(lastrow, 2)) does not work — redds1, Dec 27 '17 at 22:11
you define lastrow first, yes? dim lastrow as double say STARTCol = 1 count your columns with data and do a for loop on i until colnum-1, **there are a couple ways to do this lastrow = ws1.Cells(ws1.Rows.Count, STARTCol + i).End(xlUp).Row // then the copy should work — Wookies-Will-Code, Dec 27 '17 at 22:21
here is code to find the last column with data:https://stackoverflow.com/a/11927387/8716187 so you want to watch out for dynamic amount of cols and rows in each col, definitely a for loop with lastrow found for each col with data, then the copies should be straight forward using the range(cells(row,col),cells(row,col)) method always put the worksheet in front of range . . .I see some ambiguity in the code . . sure a reference is to the sheet with calling code, but never leave it open — Wookies-Will-Code, Dec 27 '17 at 22:30
you are going to have counters running on column for sure. Sounds like once you have col 1 from first sheet and col 1 from second sheet, you will have to place them side by side, col1, col2 and then find the difference in col3, BUT . . . you can just do this all in place with no copies, you can run through col1 on A and compare to Col1 on B, if not found place in a running row counter on C in Col 1. You will have to reverse it as well and search for col 1 B in Col 1 A and place in your running counter on C col 1. No copying and the comparison gets done, for each column. That will be fun. — Wookies-Will-Code, Dec 27 '17 at 22:36
great for loop counter exercise. but that is what vba is for, it will chew up this project in no time. a unique value finder between two columns, repeated for as many columns as there are in sheets A and B. Cool. In the end C will hold the difference in columns for sheets A and B, yes? — Wookies-Will-Code, Dec 27 '17 at 22:37
I am defining the last row as: lastrow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row. It is retrieving the correct value. But ws1.Range(Cells(1, 2), Cells(lastrow, 2)).EntireColumn.Copy Destination:=ws3.Range(Cells(1, 2), Cells(lastrow, 2)) is throwing an error. I get a run time error 1004: method range failed. Even if I hard code the rows and columns, it does not work. Please help — redds1, Dec 27 '17 at 22:42
Did you get what I was saying earlier? You don't have to copy anything. Compare ws1.cells(row y, col i) to ws2.cells(row y, col i) and build a unique list in ws3.cells(row y, col i). be sure that after running through ws1.cells(row y, col i) against ws2.cells(row y, col i) that you reverse it and compare ws2.cells(row y, col i) to ws1.cells(row y, col i). — Wookies-Will-Code, Dec 28 '17 at 14:42
I am trying to figure out if you are trying to identify items in ws1 col i not in ws2 col i AND items in ws2 col i that are not in ws1 col i. Or if rather you want to compare each cell and only record if they are different. If you want to simply look for differences row by row it a different approach. — Wookies-Will-Code, Dec 28 '17 at 14:44
it looks as though you are finding the mathematical difference between the two columns, that would be a different solution. — Wookies-Will-Code, Dec 28 '17 at 15:35
one solution to provide the mathematical difference without the copy, one solution that copies the values and then performs the calculation. Depends on your usage I suppose, but I hope this gets you closer to what you want. — Wookies-Will-Code, Dec 28 '17 at 16:24
and I looked into your method and since you are defining a range that is not the entire column, the entire column copy is similar to selecting a column (highlighting it all) and hitting CRTL+C, it copies the whole column (actually it probably determines the last row with data) but you get the idea. — Wookies-Will-Code, Dec 28 '17 at 16:37
@Wookies-Will-Code, for some reason I was not able to see these comments. Thanks a lot. I used your code below and I was able to see the difference. But I have different types of data in both sheets. It seems I would need to look up the data type. Please see below:Sheet1: Sheet2: A Total Column1 A Total Column1 1 100 10 1 300 10 2 200 20 4 100 20 3 300 30 Sheet3: A Total-Sheet1 Total-Sheet2 Difference Column1-Sheet1 Column1-Sheet2 Difference 1 100 300 -200 10 10 0 2 200 0 200 20 0 20 3 300 0 300 30 0 30 4 0 100 -100 0 20 -20 — redds1, Jan 01 '18 at 17:47

score 0 · Answer 1 · answered Dec 28 '17 at 16:04

Alright, to find the difference between each cell in each column respectively between sheet A and sheet B and place it in the respective cell on sheet C, no copying required, unless you really want that we can configure the answer for that if it is a requirement. Here is a macro that operates on 3 worksheets, taking the difference of sheet 1 and sheet 2 and placing it in sheet 3, column by column, cell by cell. It determines the number of columns with data, it determines the last row of data in the column. Will the columns on sheet A and B always be of the same length? Let me know if this helps you.

Sub diff_macro()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Dim coli As Double
Dim rowy As Double

Dim numCols As Double
Dim startRow As Double
Dim lastRow As Double

startRow = 1 'assuming data starts in column 1

Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")
Set ws3 = ThisWorkbook.Worksheets("Sheet3")

'Find how many columns there are in sheet1 based on data in row 1
numCols = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

For coli = 1 To numCols
    'Find last Data row in the given column in sheet1
    lastRow = ws1.Cells(ws1.Rows.Count, coli).End(xlUp).Row

    For rowy = 1 To lastRow ' go through each row and perform the difference calculation
        ws3.Cells(rowy, coli) = (ws1.Cells(rowy, coli).Value) - (ws2.Cells(rowy, coli).Value)

    Next rowy ' move to the next row on ws1, ws2, ws3

Next coli 'move to next column on ws1, ws2, ws3


End Sub

Cheers, WWC

I have edited the question to include the link to "Data in the Sheets". I understand your code above and I was able to make it work with certain modifications. But my bad that I did not realize that the first column can have different items in sheet 1 and sheet 2 and I need to look up the data in the first column and then copy the values. Could you please assist? Also, please note that the data in the first column of both sheets will be always a string.Happy New Year! — redds1, Jan 01 '18 at 17:58
I have created a new question for clarity: Please see the link: https://stackoverflow.com/questions/48053440/how-to-search-data-from-two-different-sheets-and-copy-them-to-a-third-sheet — redds1, Jan 01 '18 at 21:42

score 0 · Answer 2 · answered Dec 28 '17 at 16:22

If you really want (1) sheet A value then (2) sheet B value then the (3) difference, here is simple modification to the macro that copies A, copies B, then performs the Calc, there will be a a lot of columns in sheet C because you are generating 3 cols for every col in sheet A. But this does the trick. If you have headers for the columns then startRow should be 2. You could run a separate for loop to put the headers on C or write an if statement based on row 1 that acts differently from the rest in the current for loop.

Here it is, slightly modified to "copy" the cols over to sheet C:

Sub diff_macro()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Dim coli As Double
Dim Coli3 As Double
Dim rowy As Double

Dim numCols As Double
Dim startRow As Double
Dim lastRow As Double

startRow = 1 'assuming data starts in column 1
Coli3 = 1 ' start the columns out on ws3 at column 1

Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")
Set ws3 = ThisWorkbook.Worksheets("Sheet3")

'Find how many columns there are in sheet1 based on data in row 1
numCols = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

For coli = 1 To numCols
    'Find last Data row in the given column in sheet1
    lastRow = ws1.Cells(ws1.Rows.Count, coli).End(xlUp).Row

    For rowy = 1 To lastRow
        ws3.Cells(rowy, Coli3).Value = ws1.Cells(rowy, coli).Value ' copy sheet 1 to the right spot of sheet 3
        ws3.Cells(rowy, Coli3 + 1).Value = ws2.Cells(rowy, coli).Value 'copy sheet 2 to the right spot of sheet 3
        'perform calculation and place in the right spot on sheet 3
        ws3.Cells(rowy, Coli3 + 2).Value = (ws1.Cells(rowy, coli).Value) - (ws2.Cells(rowy, coli).Value)

    Next rowy ' move to the next row on ws1, ws2, ws3

    'Since we are placing 3 cols at a time in sheet 3 we increment differently
    Coli3 = Coli3 + 3 '1 becomes 4, 4 becomes 7, 7 becomes 10 and so on

Next coli 'move to next column on ws1, ws2

End Sub

Hopefully these get you closer to being where you want. Cheers, WWC

score 0 · Answer 3 · answered Dec 28 '17 at 16:35

One more answer, this one helps you out with your attempted code. You already define the column in a range by defining the first and last row, let go of the entirecolumn copy. Also you don't need to specify a range (sized) for the copy, just pick the top cell or another cell in the target column. These two lines work fine so if you want to stick with your code then modify accordingly. This copies the contents of the column on sheet A to sheet C, easy enough to do the same for B. But it works. Dump the entirecolumn copy if you define the range, I am guessing the entire column copy would only require the column number and would not want a range.

'Find how many columns there are in sheet1 based on data in row 1
numCols = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

For coli = 1 To numCols
    'Find last Data row in the given column in sheet1
    lastRow = ws1.Cells(ws1.Rows.Count, coli).End(xlUp).Row

    ws1.Range(Cells(1, coli), Cells(lastRow, coli)).Copy 'you defined the range you don't need anything else
    ws3.Cells(1, coli).PasteSpecial 'you can place conditions here if you wish

Next coli 'move to next column on ws1, ws2

There you go. -WWC

How to copy data from two different sheets in excel VBA dynamically?

3 Answers3