Help with VBA Macro to similate VLookup - Excel 2013 (15.0.4675.1001)

H

hellscheshirecat

New Member
Joined
Jul 24, 2012
Messages
12
Hi All!

I'm not really sure what to call the code I am hoping for but I will do my very best to describe what I'm looking to do and HOPEFULLY, someone can help me figure it out :) I have several reports of inventory data that I want to combine automatically by running a macro, so that buyers have the data they need on hand. I don't want my employees to have to mess with formulas and having to drag values.

Right now my workbook (file name MrExcelHelp.xlsm) has four sheets in it "Master2","On Hand","Jan 14","RJan 14"... but when complete there will be many many more sheets with data from additional months (all of 2014 and will have 2015 data added)

Master2 is where I want to gather all the data into
On Hand has a report of what inventory is in stock based on SKU
Jan 14 has the sales data for the month of January 2014 based on ASIN
RJan 14 is the record of what the warehouse received during January 2014

My main reasons for not just using formulas are:
1) fear that someone will mess them up without realizing it and then place orders based on bad data *this is a big one*
2) the number of items will change every time the "On Hand" sheet is updated (daily)
3) likewise, during the month we are currently in that months sheets will change regularly

The Master Sheet has 8 columns (for now) and the headers are in row 2 (I have instructions in row 1) It looks like this:

[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]ITEMS IN RED ARE DUPLICATES
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YELLOW IS IN STOCK
[/TD]
[TD]GREEN HAS QTY SOLD

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]sku[/TD]
[TD]asin
[/TD]
[TD]product-name
[/TD]
[TD]condition
[/TD]
[TD]your-price
[/TD]
[TD]FBAINV
[/TD]
[TD]JAN2014
[/TD]
[TD]REC JAN2014
[/TD]
[/TR]
</tbody>[/TABLE]










I have macros to pull the data from On Hand into Master 2, and a macro to run them all at once:
Code: 
Sub runallmacros()
skusfromonhand
asinsfromonhand
titlefromonhand
conditionfromonhand
pricefromonhand
qtyfromonhand
End Sub
Sub skusfromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("A" & Rows.Count).Row
    Sheets("Master2").Range("A3:A" & lastRow).Value = Sheets("On Hand").Range("A2:A" & lastRow).Value

End Sub
Sub asinsfromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("C" & Rows.Count).Row
    Sheets("Master2").Range("B3:B" & lastRow).Value = Sheets("On Hand").Range("C2:C" & lastRow).Value

End Sub
Sub titlefromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("D" & Rows.Count).Row
    Sheets("Master2").Range("C3:C" & lastRow).Value = Sheets("On Hand").Range("D2:D" & lastRow).Value

End Sub
Sub conditionfromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("E" & Rows.Count).Row
    Sheets("Master2").Range("D3:D" & lastRow).Value = Sheets("On Hand").Range("E2:E" & lastRow).Value

End Sub
Sub pricefromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("F" & Rows.Count).Row
    Sheets("Master2").Range("E3:E" & lastRow).Value = Sheets("On Hand").Range("F2:F" & lastRow).Value

End Sub
Sub qtyfromonhand()

    Dim lastRow As Long
    lastRow = Sheets("On Hand").Range("K" & Rows.Count).Row
    Sheets("Master2").Range("F3:F" & lastRow).Value = Sheets("On Hand").Range("K2:K" & lastRow).Value

End Sub

It's not the most elegant but it's what I was able to piece together. I do have one issue with this... IF for example the first time the macro was ran there were 30 rows of data, if there are less rows (lets say 20) the next time the macro is run it will replace the data in rows 1-20 but leave rows 20-30... I'd like it to delete them.

The data for Column G on Master2 needs to come from Sheet "Jan 14", column E, header is in row 1 (Units Ordered). The connecting data for this in column A (Asin).

The "Jan 14" sheet looks like this:
[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Asin[/TD]
[TD]Title
[/TD]
[TD]SKU
[/TD]
[TD]Buy Box Percentage
[/TD]
[TD]Units Ordered
[/TD]
[/TR]
</tbody>[/TABLE]




So If I were to use a formula, on Sheet "Master2", in cell G3 I would write =IFERROR(VLOOKUP(B3,'Jan 14'!$A$1:$E$17,5,FALSE),"0") and then copy it down to all the rows that had data.

Lastly, The data for Column H on Master2 needs to come from Sheet "RJan 14". The data I want to pull is from Column E (quantity), the connecting data is in column C (sku)... BUT... the skus may repeat on multiple rows (meaning they may have been recieved into the warehouse more than once in a month) so I would want the values combined. (ex. SKU1... 6 peices were recieved jan 10th, and 4 peices were recieved Jan 20th, so I would want the returned value for SKU1 to be 10)

"RJan14" sheet looks like this:
[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]received-date
[/TD]
[TD]fnsku
[/TD]
[TD]sku
[/TD]
[TD]product-name
[/TD]
[TD]quantity
[/TD]
[TD]fba-shipment-id
[/TD]
[TD]fulfillment-center-id
[/TD]
[/TR]
</tbody>[/TABLE]





If I were using a formula I would use =IFERROR(VLOOKUP(A3,RJan14!C2:G24,3,FALSE),"0"), then copy it down but that wouldn't account for the duplicate values.

So... what I think I need is two macros, one to pull the data from sheet "Jan 14" and one to pull the data from sheet "RJan 14" and combine the values... or one crazy macro that combines that with the macros I already have.

It would be great too if you could help me understand how to do it myself. Thank you so very, very much for reading all that. I hope someone can help me!
 
Sort by date Sort by votes

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Thank you so much for all your help. I was able to figure out how to add the additional data I wanted. This is what I did:
Code: 
Sub MM1Import()
    Dim lastRow As Long, lr2 As Long, lr3 As Long
    lastRow = Sheets("Master2").Range("A1").SpecialCells(xlLastCell).Row
    lr2 = Sheets("On Hand").Range("A1").SpecialCells(xlLastCell).Row
    lr3 = Sheets("RJan14").Range("A1").SpecialCells(xlLastCell).Row
    
    Sheets("Master2").Range("A3:A" & lastRow).ClearContents
    Sheets("Master2").Range("B3:H" & lastRow).ClearContents
    
    Sheets("On Hand").Range("A2:A" & lr2).Copy Destination:=Sheets("Master2").Range("A3")
    Sheets("On Hand").Range("C2:F" & lr2).Copy Destination:=Sheets("Master2").Range("B3")
    Sheets("On Hand").Range("K2:K" & lr2).Copy Destination:=Sheets("Master2").Range("F3")
    
    Sheets("RJan14").Range("H2:H" & lr3).Formula = "=sumproduct((C$2:C$" & lr3 & "=C2)*E$2:E$" & lr3 & ")"
    Sheets("Master2").Range("G3:G" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,'Jan14'!$C:$E,3,FALSE),0)"
    Sheets("Master2").Range("H3:H" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,'RJan14'!$C:$H,6,FALSE),0)"
    
    Sheets("RFeb14").Range("H2:H" & lr3).Formula = "=sumproduct((C$2:C$" & lr3 & "=C2)*E$2:E$" & lr3 & ")"
    Sheets("Master2").Range("I3:I" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,'Feb14'!$C:$E,3,FALSE),0)"
    Sheets("Master2").Range("J3:J" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,'RFeb14'!$C:$H,6,FALSE),0)"
End Sub

I was actually curious though. I based my request off of taking reports for each month of the year separately but now thinking about it, would there be a way for the macro to look at a date range and then put the information in separate columns on it's own?

Thanks again!
Jennifer
 
Upvote 0
OK! I was able to add all my data to my main sheet and start working with it. Is there a way to make it run faster? This is the code after everything was added:

Code: 
Sub populatemaster()
    Dim lastRow As Long, lr2 As Long, lr3 As Long
    lastRow = Sheets("Master").Range("A1").SpecialCells(xlLastCell).Row
    lr2 = Sheets("On Hand").Range("A1").SpecialCells(xlLastCell).Row
    lr3 = Sheets("RJan14").Range("A1").SpecialCells(xlLastCell).Row
    lr4 = Sheets("RFeb14").Range("A1").SpecialCells(xlLastCell).Row
    lr5 = Sheets("RMar14").Range("A1").SpecialCells(xlLastCell).Row
    lr6 = Sheets("RApr14").Range("A1").SpecialCells(xlLastCell).Row
    lr7 = Sheets("RMay14").Range("A1").SpecialCells(xlLastCell).Row
    lr8 = Sheets("RJun14").Range("A1").SpecialCells(xlLastCell).Row
    lr9 = Sheets("RJul14").Range("A1").SpecialCells(xlLastCell).Row
    lr10 = Sheets("RAug14").Range("A1").SpecialCells(xlLastCell).Row
    lr11 = Sheets("RSep14").Range("A1").SpecialCells(xlLastCell).Row
    lr12 = Sheets("ROct14").Range("A1").SpecialCells(xlLastCell).Row
    lr13 = Sheets("RNov14").Range("A1").SpecialCells(xlLastCell).Row
    lr14 = Sheets("RDec14").Range("A1").SpecialCells(xlLastCell).Row
    lr15 = Sheets("RJan15").Range("A1").SpecialCells(xlLastCell).Row
    lr16 = Sheets("RFeb15").Range("A1").SpecialCells(xlLastCell).Row
    
    Sheets("Master").Range("A3:A" & lastRow).ClearContents
    Sheets("Master").Range("B3:H" & lastRow).ClearContents
    
    Sheets("On Hand").Range("A2:A" & lr2).Copy Destination:=Sheets("Master").Range("A3")
    Sheets("On Hand").Range("C2:F" & lr2).Copy Destination:=Sheets("Master").Range("B3")
    Sheets("On Hand").Range("K2:K" & lr2).Copy Destination:=Sheets("Master").Range("F3")
    
    Sheets("RJan14").Range("H2:H" & lr3).Formula = "=sumproduct((C$2:C$" & lr3 & "=C2)*E$2:E$" & lr3 & ")"
    Sheets("Master").Range("G3:G" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Jan14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("H3:H" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RJan14!$C:$H,6,FALSE),0)"
    
    Sheets("RFeb14").Range("H2:H" & lr4).Formula = "=sumproduct((C$2:C$" & lr4 & "=C2)*E$2:E$" & lr4 & ")"
    Sheets("Master").Range("I3:I" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Feb14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("J3:J" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RFeb14!$C:$H,6,FALSE),0)"
    
    Sheets("RMar14").Range("H2:H" & lr5).Formula = "=sumproduct((C$2:C$" & lr5 & "=C2)*E$2:E$" & lr5 & ")"
    Sheets("Master").Range("K3:K" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Mar14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("L3:L" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RMar14!$C:$H,6,FALSE),0)"
    
    Sheets("RApr14").Range("H2:H" & lr6).Formula = "=sumproduct((C$2:C$" & lr6 & "=C2)*E$2:E$" & lr6 & ")"
    Sheets("Master").Range("M3:M" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Apr14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("N3:N" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RApr14!$C:$H,6,FALSE),0)"
    
    Sheets("RMay14").Range("H2:H" & lr7).Formula = "=sumproduct((C$2:C$" & lr7 & "=C2)*E$2:E$" & lr7 & ")"
    Sheets("Master").Range("O3:O" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'May14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("P3:P" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RMay14!$C:$H,6,FALSE),0)"
    
    Sheets("RJun14").Range("H2:H" & lr8).Formula = "=sumproduct((C$2:C$" & lr8 & "=C2)*E$2:E$" & lr8 & ")"
    Sheets("Master").Range("Q3:Q" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Jun14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("R3:R" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RJun14!$C:$H,6,FALSE),0)"
    
    Sheets("RJul14").Range("H2:H" & lr9).Formula = "=sumproduct((C$2:C$" & lr9 & "=C2)*E$2:E$" & lr9 & ")"
    Sheets("Master").Range("S3:S" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Jul14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("T3:T" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RJul14!$C:$H,6,FALSE),0)"
    
    Sheets("RAug14").Range("H2:H" & lr10).Formula = "=sumproduct((C$2:C$" & lr10 & "=C2)*E$2:E$" & lr10 & ")"
    Sheets("Master").Range("U3:U" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Aug14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("V3:V" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RAug14!$C:$H,6,FALSE),0)"
    
    Sheets("RSep14").Range("H2:H" & lr11).Formula = "=sumproduct((C$2:C$" & lr11 & "=C2)*E$2:E$" & lr11 & ")"
    Sheets("Master").Range("W3:W" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Sep14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("X3:X" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RSep14!$C:$H,6,FALSE),0)"
    
    Sheets("ROct14").Range("H2:H" & lr12).Formula = "=sumproduct((C$2:C$" & lr12 & "=C2)*E$2:E$" & lr12 & ")"
    Sheets("Master").Range("Y3:Y" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Oct14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("Z3:Z" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,ROct14!$C:$H,6,FALSE),0)"
    
    Sheets("RNov14").Range("H2:H" & lr13).Formula = "=sumproduct((C$2:C$" & lr13 & "=C2)*E$2:E$" & lr13 & ")"
    Sheets("Master").Range("AA3:AA" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Nov14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("AB3:AB" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RNov14!$C:$H,6,FALSE),0)"
    
    Sheets("RDec14").Range("H2:H" & lr14).Formula = "=sumproduct((C$2:C$" & lr14 & "=C2)*E$2:E$" & lr14 & ")"
    Sheets("Master").Range("AC3:AC" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Dec14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("AD3:AD" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RDec14!$C:$H,6,FALSE),0)"
    
    Sheets("RJan15").Range("H2:H" & lr15).Formula = "=sumproduct((C$2:C$" & lr15 & "=C2)*E$2:E$" & lr15 & ")"
    Sheets("Master").Range("AE3:AE" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Jan15'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("AF3:AF" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RJan15!$C:$H,6,FALSE),0)"
    
    Sheets("RFeb15").Range("H2:H" & lr16).Formula = "=sumproduct((C$2:C$" & lr16 & "=C2)*E$2:E$" & lr16 & ")"
    Sheets("Master").Range("AG3:AG" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Feb15'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("AH3:AH" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RFeb15!$C:$H,6,FALSE),0)"
    
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
End Sub
 
Upvote 0
Don't have Excel at the moment, but try this for speed......and maybe someone else can jump in and modify the code to make it shorter.
I don't think you need a Lastrow for each sheet.
Code: 
Sub populatemaster()
    Dim lastRow As Long, lr2 As Long, lr3 As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
    lastRow = Sheets("Master").Range("A1").SpecialCells(xlLastCell).Row
    lr2 = Sheets("On Hand").Range("A1").SpecialCells(xlLastCell).Row
    lr3 = Sheets("RJan14").Range("A1").SpecialCells(xlLastCell).Row
    lr4 = Sheets("RFeb14").Range("A1").SpecialCells(xlLastCell).Row
    lr5 = Sheets("RMar14").Range("A1").SpecialCells(xlLastCell).Row
    lr6 = Sheets("RApr14").Range("A1").SpecialCells(xlLastCell).Row
    lr7 = Sheets("RMay14").Range("A1").SpecialCells(xlLastCell).Row
    lr8 = Sheets("RJun14").Range("A1").SpecialCells(xlLastCell).Row
    lr9 = Sheets("RJul14").Range("A1").SpecialCells(xlLastCell).Row
    lr10 = Sheets("RAug14").Range("A1").SpecialCells(xlLastCell).Row
    lr11 = Sheets("RSep14").Range("A1").SpecialCells(xlLastCell).Row
    lr12 = Sheets("ROct14").Range("A1").SpecialCells(xlLastCell).Row
    lr13 = Sheets("RNov14").Range("A1").SpecialCells(xlLastCell).Row
    lr14 = Sheets("RDec14").Range("A1").SpecialCells(xlLastCell).Row
    lr15 = Sheets("RJan15").Range("A1").SpecialCells(xlLastCell).Row
    lr16 = Sheets("RFeb15").Range("A1").SpecialCells(xlLastCell).Row
    
    Sheets("Master").Range("A3:A" & lastRow).ClearContents
    Sheets("Master").Range("B3:H" & lastRow).ClearContents
    
    Sheets("On Hand").Range("A2:A" & lr2).Copy Destination:=Sheets("Master").Range("A3")
    Sheets("On Hand").Range("C2:F" & lr2).Copy Destination:=Sheets("Master").Range("B3")
    Sheets("On Hand").Range("K2:K" & lr2).Copy Destination:=Sheets("Master").Range("F3")
    
    Sheets("RJan14").Range("H2:H" & lr3).Formula = "=sumproduct((C$2:C$" & lr3 & "=C2)*E$2:E$" & lr3 & ")"
    Sheets("Master").Range("G3:G" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Jan14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("H3:H" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RJan14!$C:$H,6,FALSE),0)"
    
    Sheets("RFeb14").Range("H2:H" & lr4).Formula = "=sumproduct((C$2:C$" & lr4 & "=C2)*E$2:E$" & lr4 & ")"
    Sheets("Master").Range("I3:I" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Feb14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("J3:J" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RFeb14!$C:$H,6,FALSE),0)"
    
    Sheets("RMar14").Range("H2:H" & lr5).Formula = "=sumproduct((C$2:C$" & lr5 & "=C2)*E$2:E$" & lr5 & ")"
    Sheets("Master").Range("K3:K" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Mar14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("L3:L" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RMar14!$C:$H,6,FALSE),0)"
    
    Sheets("RApr14").Range("H2:H" & lr6).Formula = "=sumproduct((C$2:C$" & lr6 & "=C2)*E$2:E$" & lr6 & ")"
    Sheets("Master").Range("M3:M" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Apr14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("N3:N" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RApr14!$C:$H,6,FALSE),0)"
    
    Sheets("RMay14").Range("H2:H" & lr7).Formula = "=sumproduct((C$2:C$" & lr7 & "=C2)*E$2:E$" & lr7 & ")"
    Sheets("Master").Range("O3:O" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'May14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("P3:P" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RMay14!$C:$H,6,FALSE),0)"
    
    Sheets("RJun14").Range("H2:H" & lr8).Formula = "=sumproduct((C$2:C$" & lr8 & "=C2)*E$2:E$" & lr8 & ")"
    Sheets("Master").Range("Q3:Q" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Jun14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("R3:R" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RJun14!$C:$H,6,FALSE),0)"
    
    Sheets("RJul14").Range("H2:H" & lr9).Formula = "=sumproduct((C$2:C$" & lr9 & "=C2)*E$2:E$" & lr9 & ")"
    Sheets("Master").Range("S3:S" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Jul14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("T3:T" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RJul14!$C:$H,6,FALSE),0)"
    
    Sheets("RAug14").Range("H2:H" & lr10).Formula = "=sumproduct((C$2:C$" & lr10 & "=C2)*E$2:E$" & lr10 & ")"
    Sheets("Master").Range("U3:U" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Aug14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("V3:V" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RAug14!$C:$H,6,FALSE),0)"
    
    Sheets("RSep14").Range("H2:H" & lr11).Formula = "=sumproduct((C$2:C$" & lr11 & "=C2)*E$2:E$" & lr11 & ")"
    Sheets("Master").Range("W3:W" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Sep14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("X3:X" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RSep14!$C:$H,6,FALSE),0)"
    
    Sheets("ROct14").Range("H2:H" & lr12).Formula = "=sumproduct((C$2:C$" & lr12 & "=C2)*E$2:E$" & lr12 & ")"
    Sheets("Master").Range("Y3:Y" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Oct14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("Z3:Z" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,ROct14!$C:$H,6,FALSE),0)"
    
    Sheets("RNov14").Range("H2:H" & lr13).Formula = "=sumproduct((C$2:C$" & lr13 & "=C2)*E$2:E$" & lr13 & ")"
    Sheets("Master").Range("AA3:AA" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Nov14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("AB3:AB" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RNov14!$C:$H,6,FALSE),0)"
    
    Sheets("RDec14").Range("H2:H" & lr14).Formula = "=sumproduct((C$2:C$" & lr14 & "=C2)*E$2:E$" & lr14 & ")"
    Sheets("Master").Range("AC3:AC" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Dec14'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("AD3:AD" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RDec14!$C:$H,6,FALSE),0)"
    
    Sheets("RJan15").Range("H2:H" & lr15).Formula = "=sumproduct((C$2:C$" & lr15 & "=C2)*E$2:E$" & lr15 & ")"
    Sheets("Master").Range("AE3:AE" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Jan15'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("AF3:AF" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RJan15!$C:$H,6,FALSE),0)"
    
    Sheets("RFeb15").Range("H2:H" & lr16).Formula = "=sumproduct((C$2:C$" & lr16 & "=C2)*E$2:E$" & lr16 & ")"
    Sheets("Master").Range("AG3:AG" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(B3,'Feb15'!$A:$E,5,FALSE),0)"
    Sheets("Master").Range("AH3:AH" & lr2 + 1).Formula = "=IFERROR(VLOOKUP(A3,RFeb15!$C:$H,6,FALSE),0)"
    
    With Cells
    .Value = .Value
    End With
    
    Range("A1").Select
    Application.CutCopyMode = False
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Split Excel Workbook to separate .xlsx files for each unique value in Column A, also copy worksheet for Drop Down reference
Replies
9
Views
454
Alex Blakenburg
A
C
  • Question Question
How to automatically paste the data from one sheet to another using VBA
Replies
4
Views
235
Alex Blakenburg
A
D
  • Solved
Error 1004
2
Replies
18
Views
352
dmt32
D
K
  • Question Question
Help Needed
2
Replies
13
Views
319
kwavic
K
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
494
mlan1604
M

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

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