Macro for Copying/combing data

[howard]

I need a macro that will copy/combine data for several workbooks from sheets *.Newvehicles (each work sheet will have a different worksheet ending in for eg newton.newvehicles columnns O:U

I the current workbook "Consolidated New Vehicles" Sheet1, the data must be copied/consolidated into this new workbook, except the data containing the words Total for eg Total Units, Total Value

See sample data below-In this example the data must be copied up to line 20. Row 1 must be copied in the first worksheet and thereafter from row row, excluding the totals

Your assistance in this regard will me most appreciated

Howard

Newton.newvehicles.xls
	O	P	Q	R	S	T	U
19	69	FOCUS2.5ST5DRMAN	11,250	267	11,517	newton	190
20	682	FIESTA1.6DAMBIENTE5DR	11,360	281	11,641	newton	240
21
22
23	TotalUnits	19
24
25	TotalValue		213,860	5,045	218,905
Newton.newvehicles

 

[venkat1926]

normally the macro given below should work. But I thing your rows 21 and 22
column O and to right are NOT blanks. If keep the cursor at O19 and hit end-down arrow it should go to O20 but it goes to O23. there is some entry or formula in rows 21 and 22.

clarify this point

preliminary macro (not teted ) is
with the proviso that rows 21 and 22 are blank the copying is done row 2 in the "consolidateed ..." file row 1 is blank for entering column headings.

Sub test()
Dim wbdest As Workbook
Dim rng As Range
Set wbdest = Workbooks("Consolidated New Vehicles.xls")
With ThisWorkbook.Worksheets("Newton.newvehicles")
Set rng = Range(.Range("O19"), .Range("O19").End(xlToRight).End(xlDown))
rng.Copy
With wbdest.Worksheets("sheet1")
.Cells(Rows.Count, "a").End(xlUp).Offset(1#).PasteSpecial
End With
End With

End Sub

 

[howard]

Hi Venkat

thanks for the help. Macro is not working properly. Have emailed you my file. Once working properly, please post soloution on MrExcel.com

Regards

Howard