PDA

View Full Version : Copy two sheets (or more sheets) data into new sheet



dpatel
08-04-2011, 04:29 AM
Hello,

On the attachement, I have two Sheets contains with vaules. The following code merge sheet3 vaules into sheet2. But how can i crate a new sheet and merge the data?



Sub MergeSheets()
Dim i As Integer, k As Long, c As Integer
With Worksheets(1).Cells(1)
c = .End(xlToRight).Column
k = .End(xlDown).Row + 1
End With
For i = 2 To ThisWorkbook.Worksheets.Count
With Worksheets(i).Cells(1).CurrentRegion.Offset(1, 0)
.Copy Worksheets(1).Cells(k, 1)
k = k + .Rows.Count - 1
End With
Next i
End Sub

Look forward to see any advise.
Thanks,

shrivallabha
08-04-2011, 10:45 AM
Something like this?
Sub MergeSheets()
Dim i As Integer, k As Integer
k = ThisWorkbook.Worksheets.Count
Set ws = Worksheets.Add(After:=Sheets(k))
For i = 2 To k
With Worksheets(i).Cells(1).CurrentRegion.Offset(1, 0)
.Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
End With
Next i
End Sub