conditionally combine the worksheets

[luvbite38]

Hi guys,

I don't know if this is even possible in Excel.

I export the data from another software in excel. One of the worksheet I usually work with is name as "Elec". The problem is depending on the size of the project the number of Elec worksheets can be variable. I can have a Elec (1) to Elec .

Right now I copy each elec file individually and combine them togather (which is really a painfull process.... specially, if I have to re-run the model)....

Is there a way, a macro to create a worksheet naming "combined" and copy and paste from each worksheet which has text string Elec and paste them in Combined worksheet???

Hope it makes sense.

Note: The number of rows and columns (A1:J32002) in each Elec worksheet is identical.

Any suggest and help will be much much much appreciated.

Kindest Regards,

 

[John Davis]

Hi guys,

I don't know if this is even possible in Excel.

I export the data from another software in excel. One of the worksheet I usually work with is name as "Elec". The problem is depending on the size of the project the number of Elec worksheets can be variable. I can have a Elec (1) to Elec .

Right now I copy each elec file individually and combine them togather (which is really a painfull process.... specially, if I have to re-run the model)....

Is there a way, a macro to create a worksheet naming "combined" and copy and paste from each worksheet which has text string Elec and paste them in Combined worksheet???

Hope it makes sense.

Note: The number of rows and columns (A1:J32002) in each Elec worksheet is identical.

Any suggest and help will be much much much appreciated.

Kindest Regards,

Maybe:

Sub luvbite38()

Dim ws As Worksheet

Sheets.Add.Name = "Combined"

For Each ws In ThisWorkbook.Worksheets

    If Left(ws.Name, 4) = "Elec" Then
    
        ws.Range("A1:J32002").Copy Sheets("Combined").Range("A" & Rows.Count).End(3)(2)
    
    End If

Next ws

End Sub

 

[luvbite38]

Thanks a million,

I tried the code, but it only creates the blank worksheet and does copy the ranges from each Elec worksheets,

Pls let me know, if I am doing something wrong?

 

[John Davis]

Thanks a million,

I tried the code, but it only creates the blank worksheet and does copy the ranges from each Elec worksheets,

Pls let me know, if I am doing something wrong?

Where is it copying the ranges too?? Maybe I'm misunderstanding, but I thought that's what you wanted. What is it not doing that you want it too do?

 

[luvbite38]

it is just creating a blank worksheet,

Desired.

create a blank worksheet >> name it Combined (if the sheet is already present, delte it and make another)

copy the range from each elec sheet in sequence...like elec (1) then elec (2)... and paste them in the combined worksheet. In general, I want to combine the info from every elec sheet in to combined sheet.

Also, if the combined run out the space, show the message saying something like out of space .


Gracious.....