combine data from multiple workbooks to existing worksheet in existing workbook

[Apple2_1979]

I need to combine data from multiple workbooks into an existing worksheet in an existing workbook.

I have tried many solutions from past posts vba but none work.

Select All rows with data starting from A6:G6 down until it hits an empty row at which point the vba will goto a different wookbook and paste this data into the next empty row starting at A5

Next it needs to go back to the first workbook and selct B2, E2 & G2 copy the data, goto workbook where the first rows of data were copied to and paste the data (B2, E2 & G2 ) in columns H, I, J corresponding to the rows entered earlier.

Then do it over and over until the last workbook has been copied.

When using the macro recorder I get the following:

Sub Macro7()
'
' Macro7 Macro
'
'
Sheets("COMBINE WORKSHEETS").Select
Windows("2012_VMT_001.xlsx").Activate
Range("A6:G8").Select
Selection.Copy
Windows("VMT_REPORTING.xlsm").Activate
ActiveWindow.WindowState = xlNormal
Range("A5").Select
ActiveSheet.Paste
Windows("2012_VMT_001.xlsx").Activate
Range("B2,E2,G2").Select
Range("G2").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("VMT_REPORTING.xlsm").Activate
ActiveWindow.WindowState = xlNormal
Range("H5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("H5:J7"), Type:=xlFillDefault
Range("H5:J7").Select
Windows("2012_VMT_003.xlsx").Activate
Range("A6:G10").Select
Selection.Copy
Windows("VMT_REPORTING.xlsm").Activate
ActiveWindow.WindowState = xlNormal
Range("A8").Select
ActiveSheet.Paste
Windows("2012_VMT_003.xlsx").Activate
Range("B2,E2,G2").Select
Range("G2").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("VMT_REPORTING.xlsm").Activate
ActiveWindow.WindowState = xlNormal
Range("H8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("H8:J12"), Type:=xlFillDefault
Range("H8:J12").Select
Range("A5").Select
End Sub

Apologies for the long windedness.

 

[jbeaucaire]

Like so... this macro goes IN the consolidation workbook in regular code module:

Option Explicit

Sub MergeWorkbooks()
Dim wsMERGE As Worksheet, wbDATA As Workbook
Dim fPATH As String, fNAME As String, LR As Long, NR As Long

Set wsMERGE = ThisWorkbook.Sheets("COMBINE WORKSHEETS")
NR = wsMERGE.Range("A" & Rows.Count).End(xlUp).Row + 1  'spot next row in target sheet
fPATH = "C:\Path\To\My\Files\"                          'remember the final \

fNAME = Dir(fPATH & "*VMT*.xlsx")                       'get first filename in noted path
Application.ScreenUpdating = False                      'speed up execution

Do While Len(fNAME) > 0                 'continue loop until all found files in path are done
    Set wbDATA = Workbooks.Open(fPATH & fNAME)              'open found file
    LR = Range("A" & Rows.Count).End(xlUp).Row              'spot last row of source data
    Range("A6:G" & LR).Copy wsMERGE.Range("A" & NR)         'copy data
    Range("B2,E2,G2").Copy wsMERGE.Range("H" & NR)          'copy extra cells
    wbDATA.Close False                                      'close found file, no save
    
    fNAME = Dir                         'get next filename using same filter as before
    NR = wsMERGE.Range("A" & Rows.Count).End(xlUp).Row + 1  'spot next row in target sheet
Loop

With wsMERGE.Range("H5:J" & NR).SpecialCells(xlBlanks)
    .FormulaR1C1 = "=R[-1]C"            'add value from cell above via formula
    .Value = .Value                     'remove formulas
End With

Application.ScreenUpdating = True       'back to normal, update the screen
End Sub

 

[Apple2_1979]

Good Morning Jerry,

Thank-you for Jerry for your reply it works great so far with one glitch if you will.

<TABLE style="WIDTH: 781pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1040><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 151pt; mso-width-source: userset; mso-width-alt: 7350" width=201><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 6692" width=183><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 16.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81 height=22 width=75><?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:date ls="trans" Month="01" Day="05" Year="2012">01-05-2012</st1:date> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 151pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 width=201>Welby</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80 width=99>98734</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 137pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80 width=183>Fuel</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80 width=80>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=78>$112.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl99 width=80>$1.12</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d9d9d9; WIDTH: 55pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl92 width=73>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d9d9d9; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl97 width=74>98534</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d9d9d9; WIDTH: 73pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl94 width=97>42</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=20><st1:date ls="trans" Month="01" Day="13" Year="2012">01-13-2012</st1:date> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>Welby</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>99927</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>Fuel</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>$111.50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl91>$1.12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95>001</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl96>98534</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>42</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=21><st1:date ls="trans" Month="01" Day="19" Year="2012">01-19-2012</st1:date> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>Welby</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>100927</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>Fuel</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>90</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>$108.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl91>$1.20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95>001</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl96>98534</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>42</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81 height=21><st1:date ls="trans" Month="01" Day="03" Year="2012">01-03-2012</st1:date> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>93200</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>Fuel</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74>$110.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76>98.2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl75>$1.12</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d9d9d9; WIDTH: 55pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl77 width=73>003</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl78>92083</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl93>43</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=20><st1:date ls="trans" Month="01" Day="09" Year="2012">01-09-2012</st1:date> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>94000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>Fuel</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>$97.35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88>100.0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl89>$0.97</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95>001</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl96>98534</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=20><st1:date ls="trans" Month="01" Day="16" Year="2012">01-16-2012</st1:date> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>94999</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>Fuel</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>$99.85</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88>99.0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl89>$1.01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95>001</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl96>42</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=20><st1:date ls="trans" Month="01" Day="02" Year="2012">01-02-2012</st1:date> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>92110</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84>Oil Change</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>$59.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl89> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95>98534</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl96>42</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=20><st1:date ls="trans" Month="01" Day="09" Year="2012">01-09-2012</st1:date> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79>94000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84>Car Wash</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>$7.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl89> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95>#N/A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl96>#N/A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95>#N/A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl96>#N/A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>#N/A</TD></TR></TBODY></TABLE>
The last three columns are H, I, J. The grey filled cells are where they need to be. The unfilled cells are kinda wacky. I'm gonna try a fix and will inform you of the results. But if you have an idea that'd be great too!

Cheers!!!

 

[jbeaucaire]

Your original cells have formulas in them instead flat values? Those formulas would need to be removed as the data is copied...

This...

    Range("A6:G" & LR).Copy wsMERGE.Range("A" & NR)         'copy data
    Range("B2,E2,G2").Copy wsMERGE.Range("H" & NR)          'copy extra cells
    wbDATA.Close False                                      'close found file, no save

...would have to become:

    Range("A6:G" & LR).Copy wsMERGE.Range("A" & NR)         'copy data
    Range("B2,E2,G2").Copy                                  'copy extra cells
    wsMERGE.Range("H" & NR).PasteSpecial xlPasteValues      'paste values only
    wbDATA.Close False                                      'close found file, no save