Copying a Range from Multiple Worksheets

[AB33]

I have got the following code from Ron de Bruin’s site. I would like to make an adjustment to this code, but got stuck. Change to be made is highlighted in yellow. After all copies from individual sheets are done in the destination sheet, I would like the name of each files to appear in Column “A” of destination sheet not in Column “H” as per Ron’s code. I have added the following line, inorder to insert a new column in Column “A”. Please help.

Please Login or Register  to view this content.

In the following steps, you copy a range of data from all worksheets in a workbook and consolidate the

Please Login or Register  to view this content.

[nigelog]

would it not be easier to amend the end of the macro copy column "H" and insert it at "A"

[AB33]

nigelog,
I did try it, but it did not copy the column "A" data from each sheet, and it does not input the name of the sheet on Column "A" either. I am newbie to VBA. This is my assumption.
After the code copies all cells from each sheet to destination sheet, I thought if I insert the line

DestSh.Columns("A:B").Insert Shift:=xlToRight
It will insert a new column and copies the names in to column "A"- the new inserted column, and have also changed DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name in to
DestSh.Cells(Last + 1, "A").Resize(CopyRng.Rows.Count).Value = sh.Name. In doing so, the code actaully does not do anything- i.e does not copy the cells.

[AB33]

Could some one please be any help?