VBA Macro Consolidate Data From Discontiguous Cells In Multiple Sheets To One Master

[Mike H]

Hi,

Change the line for J43 fro the single line of

Sheets("Sheet4").Range("j43").Copy Destination:=Sheets("summary").Range("m" & j)

To this

Sheets("Sheet4").Range("j43").Copy
Sheets("summary").Range("m" & j).PasteSpecial Paste:=xlPasteValues

[tinamiller1]

It does not work right. It first asks if I want to replace the contents of the cells and it will ask until it goes thru all sheets and I click cancel or ok. The 2nd issue is it displays it like this:

737978 737978 737978 291023785101 71424025 $1,050.00 $74,995,226,250.00 Implant Pass-through: Auto Invoice Pricing (AIP) Implant Pass-through: Auto Invoice Pricing (AIP) 12/8/2011
291023785101 71424003 $2,280.00 $162,846,726,840.00 Implant Pass-through: Auto Invoice Pricing (AIP)
291023785101 71421183 $5,211.00 $372,175,784,613.00 Implant Pass-through: Auto Invoice Pricing (AIP)


When I do my code without that summation piece, commented out, it copies like this:

289597855601 737978 4/29/2011 7/12/2012 CEMENT BONE PALACOS 00-1113-140-01 2 $322.86 $645.72 Implant Pass-through: Auto Invoice Pricing (AIP)
289597855601 737978 4/29/2011 7/12/2012 BUNDLED PRICING $- Implant Pass-through: Auto Invoice Pricing (AIP)
289597855601 737978 4/29/2011 7/12/2012 GSF FLX CEM FM/CEM TIB/PRLNG SUR/XLPE PT 98-0002-500-28 1 $4,150.00 $4,150.00 Implant Pass-through: Auto Invoice Pricing (AIP)
289597855601 737978 4/29/2011 7/12/2012 NATURAL KNEE GSF NP FLEX FEM SIZE 3-LT 00-5414-016-01 1 $- Implant Pass-through: Auto Invoice Pricing (AIP)
289597855601 737978 4/29/2011 7/12/2012 NATURAL KNEE FLX PROLNG PATELLA, SIZE 1, 8MM 00-5420-008-01 1 $- Implant Pass-through: Auto Invoice Pricing (AIP)
289597855601 737978 4/29/2011 7/12/2012 NK FLX PRLNG UL CONG ART 00-5428-011-09 1 $- Implant Pass-through: Auto Invoice Pricing (AIP)
289597855601 737978 4/29/2011 7/12/2012 NKII NP STEM TIBIAL BASEPLATE LT, SIZE 2 6307-00-220 1 $- Implant Pass-through: Auto Invoice Pricing (AIP)
289597855601 737978 4/29/2011 7/12/2012 S&H Total: $50.00 $50.00 Implant Pass-through: Auto Invoice Pricing (AIP)


I am having a really tought time trying to figure this out and have posted to several boards but no one has been able to help me figure it out. I might just create a separate macro to write to a separte sheet and combine after the fact.