# combine sheet1,sheet2,sheet3,sheet4 into 1sheet

what I want is to combine sheet1,sheet2,sheet3,sheet4 into 1sheet
so for the same
I have putted sheet1 ,sheet2,sheet3,sheet4  all cells data in the same place after putting (,)
so all this I have to do by vba
Book1.xlsm
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel & VBA ExpertCommented:
You may try something like this...

``````Sub CombineSheets()
Dim lr As Long, lc As Long
Dim arr1, arr2, arr3, arr4, arr5()
Dim i As Long, j As Long

lr = Sheet1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lc = Sheet1.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

arr1 = Sheet1.Range("A1", Sheet1.Cells(lr, lc)).Value
arr2 = Sheet2.Range("A1", Sheet2.Cells(lr, lc)).Value
arr3 = Sheet3.Range("A1", Sheet3.Cells(lr, lc)).Value
arr4 = Sheet4.Range("A1", Sheet4.Cells(lr, lc)).Value

ReDim arr5(1 To UBound(arr1, 1), 1 To lc)
For i = 1 To UBound(arr1, 1)
For j = 1 To UBound(arr1, 2)
arr5(i, j) = arr1(i, j)
If arr2(i, j) <> "" Then arr5(i, j) = arr5(i, j) & "," & arr2(i, j)
If arr3(i, j) <> "" Then arr5(i, j) = arr5(i, j) & "," & arr3(i, j)
If arr4(i, j) <> "" Then arr5(i, j) = arr5(i, j) & "," & arr4(i, j)
Next j
Next i
Sheet5.Cells.Clear
Sheet5.Range("A1").Resize(lr, lc).Value = arr5
End Sub
``````
0

Experts Exchange Solution brought to you by