VBA to merge some columns from 2 excel sheets into 1 master sheet

C

Cathmac801

New Member
Joined
Apr 23, 2014
Messages
20
Hi everyone,

I want to merge some columns from 2 sheets into an existing master sheet. Each month we get a worksheet called Data1PN and another called Data2WB they have all the same headings - i need to take data from columns A, B, C, D, P, T, U, V & AM in each sheet and paste them into MasterWork1 (MasterWork1 has loads of formula in different columns). The mapping is as follows:

Column maps to :Column
Data1PN/Data2WBMasterWork1
AA
DB
BD
CE
PI
TF
UG
VH
AMQ

I recorded a macro and it works well for Data1PN - however i don't know how to get the data from the 2nd sheet (Data2WB) and find the next free row to paste the data in from there. Each month there are a different number of rows in each sheet.

I hope this makes sense, if anyone can help please let me know.

Thanks
Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
1Sr NoForenameSurnameRef No.TitleAddress Line 1Address Line 2Address Line 3Address Line 4POST CODEPhone No.GenderDOBCivil StatusID NoStart DateData 1Data 2Data 3Total AmtSplit 1Split 2Data 3Data 4Data 5Data 6Data 7Data 8Data 9Data 10Data 11Data 12Data 13Data 14Data 15Data 16Data 17Data 18Checker CommentsDoer Comment
21JackHuges12315/08/20213,150.491,555.871,594.62increase due
32PeterNolan45623/08/20211,974.24961.711,012.53
43JoeEnright78928/08/20212,390.68927.391,463.29
54JohnLehane13308/08/20214,029.832,524.731,505.10
65ShaneMurphy14506/08/20214,562.302,970.381,591.92
76DerreckKeenan15614/08/20214,967.763,346.331,621.43
87CormacCaulfied75624/08/20213,281.252,019.661,261.59
98PatrickCrowley12710/08/20213,102.411,895.411,207.00backpay from last month
109JerHuges64812/08/20215,866.944,566.491,300.45
1110GerryNolan48623/08/20218,393.606,229.812,163.79
1211NathanEnright258703/08/20217,597.215,968.141,629.07
1312TonyLehane369930/08/20219,852.448,109.341,743.10
1413PeterMurphy4864303/08/20219,713.698,151.091,562.60
1514AnnieKeenan3549713/08/20219,562.558,176.101,386.45
1615CatherineCaulfied5456911/08/20218,348.176,847.861,500.31
1716PaulineCrowley98521/08/20219,459.487,665.641,793.84
1817MaryHuges638625/08/202110,226.028,386.531,839.49
1918KateNolan6548908/08/202110,935.788,509.122,426.66
2019MaureenEnright321914/08/20217,412.946,097.711,315.23arrears due to dependent
2120VeraLehane547816/08/202110,922.039,130.871,791.16
2221JennieMurphy5845631/08/202111,839.789,213.662,626.12
2322AmyKeenan635489917/08/20218,920.947,569.171,351.77
2423RuthCaulfied657504/08/202111,053.648,499.602,554.04
2524SineadCrowley46527/08/202110,802.429,471.981,330.44
2625LouiseMcAree85402/08/202111,004.188,894.682,109.50
2726NoreenMcArdle6461216/08/202114,416.3011,298.693,117.61
Data1PN



Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
1Sr NoForenameSurnameRef No.TitleAddress Line 1Address Line 2Address Line 3Address Line 4POST CODEPhone No.GenderDOBCivil StatusID NoStart DateData 1Data 2Data 3Total AmtSplit 1Split 2Data 3Data 4Data 5Data 6Data 7Data 8Data 9Data 10Data 11Data 12Data 13Data 14Data 15Data 16Data 17Data 18Checker CommentsDoer Comment
21JaneDoeP453611/06/20219361.106164.233196.87
32JohnMurphyP756313/08/202110,515.969925.34590.62
43KeithHallP458903/07/201910260.491560.868699.63arrears due for june
54PeterJonesP65424/07/202115943.362354.3813588.98
65AnnieLowryP89564504/07/20217198.525813.521385.00
Data2WB
Cell Formulas
RangeFormula
V2:V6V2=T2-U2


Book2
ABCDEFGHIJKLMNOPQ
1Sr NoRef No.PayNo.ForenameSurnameTotal AmtSplit 1Split 2Start DateAllowance Start dateArrears Split 1Arrears Split 2Total ArrearsTotal Split 1Total Split 2Doer CommentChecker Comments
2
MasterWork1
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Hey there...
Are the Data1PN and Data2WB sheets in the same workbook as MasterWork1? If not, are they in a single workbook or two workbooks? And if so, how are the workbooks named?
 
Upvote 0
tonyyy said:
Hey there...
Are the Data1PN and Data2WB sheets in the same workbook as MasterWork1? If not, are they in a single workbook or two workbooks? And if so, how are the workbooks named?
Click to expand...
Hi Tonyyy
They are all in the same workbook as a tab each.
Thanks
Cath
 
Upvote 0
Thanks for the prompt reply, Cath. You might consider the following...

VBA Code: 
Sub MasterWork1()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastRow1 As Long, lastRow2 As Long

Application.ScreenUpdating = False
Set ws1 = Sheets("MasterWork1")
For Each ws2 In ThisWorkbook.Sheets
    If ws2.Name = "Data1PN" Or ws2.Name = "Data2WB" Then
        lastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row + 1
        lastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
        With ws1
            .Range(.Cells(lastRow1, 1), .Cells(lastRow1 + lastRow2 - 2, 1)).Value = ws2.Range(ws2.Cells(2, 1), ws2.Cells(lastRow2, 1)).Value
            .Range(.Cells(lastRow1, 2), .Cells(lastRow1 + lastRow2 - 2, 2)).Value = ws2.Range(ws2.Cells(2, 4), ws2.Cells(lastRow2, 4)).Value
            .Range(.Cells(lastRow1, 4), .Cells(lastRow1 + lastRow2 - 2, 4)).Value = ws2.Range(ws2.Cells(2, 2), ws2.Cells(lastRow2, 2)).Value
            .Range(.Cells(lastRow1, 5), .Cells(lastRow1 + lastRow2 - 2, 5)).Value = ws2.Range(ws2.Cells(2, 3), ws2.Cells(lastRow2, 3)).Value
            .Range(.Cells(lastRow1, 9), .Cells(lastRow1 + lastRow2 - 2, 9)).Value = ws2.Range(ws2.Cells(2, 16), ws2.Cells(lastRow2, 16)).Value
            .Range(.Cells(lastRow1, 6), .Cells(lastRow1 + lastRow2 - 2, 6)).Value = ws2.Range(ws2.Cells(2, 20), ws2.Cells(lastRow2, 20)).Value
            .Range(.Cells(lastRow1, 7), .Cells(lastRow1 + lastRow2 - 2, 7)).Value = ws2.Range(ws2.Cells(2, 21), ws2.Cells(lastRow2, 21)).Value
            .Range(.Cells(lastRow1, 8), .Cells(lastRow1 + lastRow2 - 2, 8)).FormulaR1C1 = "=(RC6)-(RC7)"
            .Range(.Cells(lastRow1, 17), .Cells(lastRow1 + lastRow2 - 2, 17)).Value = ws2.Range(ws2.Cells(2, 39), ws2.Cells(lastRow2, 39)).Value
        End With
    End If
Next ws2
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
tonyyy said:
Thanks for the prompt reply, Cath. You might consider the following...

VBA Code: 
Sub MasterWork1()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastRow1 As Long, lastRow2 As Long

Application.ScreenUpdating = False
Set ws1 = Sheets("MasterWork1")
For Each ws2 In ThisWorkbook.Sheets
    If ws2.Name = "Data1PN" Or ws2.Name = "Data2WB" Then
        lastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row + 1
        lastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
        With ws1
            .Range(.Cells(lastRow1, 1), .Cells(lastRow1 + lastRow2 - 2, 1)).Value = ws2.Range(ws2.Cells(2, 1), ws2.Cells(lastRow2, 1)).Value
            .Range(.Cells(lastRow1, 2), .Cells(lastRow1 + lastRow2 - 2, 2)).Value = ws2.Range(ws2.Cells(2, 4), ws2.Cells(lastRow2, 4)).Value
            .Range(.Cells(lastRow1, 4), .Cells(lastRow1 + lastRow2 - 2, 4)).Value = ws2.Range(ws2.Cells(2, 2), ws2.Cells(lastRow2, 2)).Value
            .Range(.Cells(lastRow1, 5), .Cells(lastRow1 + lastRow2 - 2, 5)).Value = ws2.Range(ws2.Cells(2, 3), ws2.Cells(lastRow2, 3)).Value
            .Range(.Cells(lastRow1, 9), .Cells(lastRow1 + lastRow2 - 2, 9)).Value = ws2.Range(ws2.Cells(2, 16), ws2.Cells(lastRow2, 16)).Value
            .Range(.Cells(lastRow1, 6), .Cells(lastRow1 + lastRow2 - 2, 6)).Value = ws2.Range(ws2.Cells(2, 20), ws2.Cells(lastRow2, 20)).Value
            .Range(.Cells(lastRow1, 7), .Cells(lastRow1 + lastRow2 - 2, 7)).Value = ws2.Range(ws2.Cells(2, 21), ws2.Cells(lastRow2, 21)).Value
            .Range(.Cells(lastRow1, 8), .Cells(lastRow1 + lastRow2 - 2, 8)).FormulaR1C1 = "=(RC6)-(RC7)"
            .Range(.Cells(lastRow1, 17), .Cells(lastRow1 + lastRow2 - 2, 17)).Value = ws2.Range(ws2.Cells(2, 39), ws2.Cells(lastRow2, 39)).Value
        End With
    End If
Next ws2
Application.ScreenUpdating = True
End Sub
Click to expand...
@tonyyy thank you so much for this, it works perfectly, sorry for the late reply i didn't have my laptop over the weekend so couldn't try it until now. thanks again
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
127
sijpie
sijpie
J
  • Solved
Help with VBA If/then statement match cells in same row in two columns on two different sheets then copy next cell from sheet 2 to sheet 1.
Replies
8
Views
891
offthelip
O
W
  • Question Question
EXCEL VBA Find Headers and Copy Column from Sheet 1 to Sheet 2
Replies
1
Views
966
AlphaFrog
AlphaFrog
K
  • Question Question
Vba code to copy & paste values from one sheet to another if condition is met
2
Replies
12
Views
12K
mumps
mumps
K
  • Question Question
Excel VBA for copy and past one sheet to another sheet when some criteria match
Replies
0
Views
395
khan_safi773
K

Forum statistics

Threads
1,224,809
Messages
6,181,075
Members
453,020
Latest member
mattg2448

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top