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
Avinash SinghAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)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

Open in new window

0
 
Avinash SinghAuthor Commented:
Thnx Neeraj sir for ur great support  and thnx alot for giving ur precious time to this post
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Avinash!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.