Macro to copy all sheets into a new sheet

[HowardC]

I would have a workbook containing several sheets. All the sheets contain the same headings which are in Columns A to L and rows 1 to 4

I would like a macro to copy all the sheets into a new sheet called “Consolidate” . None of the headings are to be copied into the sheet "consolidate", except sheet1 as all the headings on the other sheets are the same as sheet 1

Your assistance in this regard is most appreciated

[sdckapr]

Does the code in http://windowssecrets.com/forums/sho...l=1#post460565 work?

If you are using XL2007 or later you should change the "65536" to "1048576" since the number of rows has increased.

Steve

[HowardC]

Hi Steve

Thanks for pointing me in the right direction-The code works perfectly. I made a small change to the code to increase the range of the columns that needed to be selected

Regards

Howard

[RetiredGeek]

Steve & Howard,

How about?

For Each wshCur In wbkCur.Worksheets
		lngRow = wshNew.Range("A"+format(rows.Count)").End(xlUp).Row + 1
		wshCur.Range(wshCur.Range("A2"), wshCur.Range("K"+format(rows.Count)).End(xlUp)).Copy _
			Destination:=wshNew.Range("A" & lngRow)
	Next wshCur

Which doesn't care which version of Excel.

[HowardC]

Hi RG

Thanks for the code. When running the code it comes up with a syntax error and the following code is highlighted

lngRow = wshNew.Range("A"+format(rows.Count)").End(xlUp).Ro w + 1

Regards

Howard

[sdckapr]

Remove the dbl-quote after Count and remove the space within the word Row

lngRow = wshNew.Range("A"+format(rows.Count)).End(xlUp).Row + 1

Steve

[HowardC]

Hi Steve

Thanks for the advise

Regards

Howard