please help me to consolidate all sheets into single.

[sampath]

Hello,

I have number of sheets in excel work book. and need to consolidate all the sheets into single sheet in same workbook(last sheet). here I have using below mentioned code. but some time, No of sheets can be varied.

Sub MergeMe()
Dim i As Integer
For i = 1 To Worksheets.Count - 1
Sheets(i).[a1].CurrentRegion.Copy Sheet4.Range("IV1").End(xlToLeft)(, 2)
Next i
End Sub


So need below condition for that macro.

1. No of pages input dialogue box can open while using macro.

2. consolidation data can be stored in last sheet of workbook.

Kindly clarify for this.

Regards,
Sampath.s

 

[Marc L]

Hi,

so first don't use Sheets but WorkSheets …

Instead of Sheet4 use Worksheets(Worksheets.Count)
for last worksheet …

_______________________________________________
Je suis Charlie
​

 

[Smallman]

Hi Sampath

I almost certainly wrote the code you are using. The naming convention and the way it is written has my style all over it. I could probably find the post where it was first entered.

Ensure you have the worksheet code name (sheet4) as the very left most sheet. It will run as intended that way.

Here is an example of the code in a worksheet. I have not altered it in any way.

Runs like a dream.

Take care

Smallman