I have a workbook that has over 6 worksheets in it. What I would like
to do is copy data from each of the worksheets into one master
worksheet so I can export it cleanly.

My idea is to make a 7th worksheet and reference each sheet via a
table look-up, then run a loop to copy cells until there are two more
cells that are empty(we have blank cells in between) then move on to
the next worksheet.

In addition to it i am using the below mentioned code.

Sub aki1()
Set SrcBook = ActiveWorkbook
Set nwbook = Workbooks.Add
If nwbook.Worksheets.Count < 1 Then
nwbook.Worksheets.Add
End If
SrcBook.Activate
i = 1
For Each wrksh In SrcBook.Worksheets
wrksh.Range("A1").CurrentRegion.Copy _
Destination:=nwbook.Worksheets(1).Cells(i, 1)
i = i + wrksh.Range("A1").CurrentRegion.Rows.Count + 0
'change zero above to the number of blank rows you want
'between data from each sheet
Next wrksh
End Sub

But when i tried to run this Macro only the data for Sheet1 is beign
copied. I want the to copy the data of the rest of the 5 Sheets in
Sheet7. How can we do that.

The method I am using seems to me a little crude, does anyone have any
better ideas?

Thanks in Advance.

Akash