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

Hi Akash,

Your code works for me. Perhaps. however, the instruction:

wrksh.Range("A1").CurrentRegion

is not retuning the data on subsequent worksheets.

However, for more robust code, see Ron de Bruin's sample
code at:

Merge cells from all or some worksheets into one Master sheet
http://www.rondebruin.nl/copy2.htm

Note also Ron's use of his LastRow Function to update the
destination range.

Incidentally, I would strongly encourage you to always explicitly
declare all variables.


---
Regards,
Norman



"Akash" wrote in message
oups.com...
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

Hi Norman,

Thanks for the suggestion but i wanted in some other way.

Right now whats happening is its comming Vertically... I want
horizontally.

Right Now its comming like.

Data Of Sheet 1
Data Of Sheet 2
Data Of Sheet 3
Data Of Sheet 4

I want in this passion

Data Of Sheet 1 | Data Of Sheet 2 | Data Of Sheet 3 |
Data Of Sheet 4

Is it possible what changes should i do in my 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



Pls do help me in this regards.

Thanks

Akash






On Apr 25, 1:47 pm, "Norman Jones"
wrote:
HiAkash,

Your code works for me. Perhaps. however, the instruction:

wrksh.Range("A1").CurrentRegion

is not retuning the data on subsequent worksheets.

However, for more robust code, see Ron de Bruin's sample
code at:

Merge cells from all or some worksheets into one Master sheet
http://www.rondebruin.nl/copy2.htm

Note also Ron's use of his LastRow Function to update the
destination range.

Incidentally, I would strongly encourage you to always explicitly
declare all variables.

---
Regards,
Norman

"Akash" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

Akash

Make a variable for column just as you have for row. After the data
is copied, use the mod function to tell whether to increment to the
next column or to the next row. To find a new row on the destination
sheet, you must find the rows used on the destination sheet after the
third copy.

If i mod 3 is 0, col = 1 : row =
destination_sheet.UsedRange.Rows.Count +2
Else (row is unchanged) : col = col +
source_sheet.Range("A1").CurrentRegion.Rows.Count +2

This macro will show you the values:
Sub Temp4Akash()
Dim rw As Long, col As Long, wrksh As Integer
Debug.Print "SHEET", "MOD", "ACTION"
' In this example, 7 sheets are copied to Sheet8
For wrksh = 1 To 7
' copy your data here
If wrksh 1 And wrksh Mod 3 = 0 Then
Debug.Print "Sheet" & wrksh, wrksh Mod 3, "col = 1, new row"
col = 1
' uncomment when ready to implement
' rw = rw + Sheets("Sheet8").Range("A1").CurrentRegion.Rows.Co unt
Else
Debug.Print "Sheet" & wrksh, wrksh Mod 3, "new col, same row"
' uncomment when ready to implement
' col = col + Sheets("Sheet" &
wrksh).Range("A1").CurrentRegion.Columns.Count
' rw unchanged
End If
Next wrksh
End Sub

Carl

On May 4, 6:45 am, Akash wrote:
Hi Norman,

Thanks for the suggestion but i wanted in some other way.

Right now whats happening is its comming Vertically... I want
horizontally.

Right Now its comming like.

Data Of Sheet 1
Data Of Sheet 2
Data Of Sheet 3
Data Of Sheet 4

I want in this passion

Data Of Sheet 1 | Data Of Sheet 2 | Data Of Sheet 3 |
Data Of Sheet 4

Is it possible what changes should i do in my 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

Pls do help me in this regards.

Thanks

Akash

On Apr 25, 1:47 pm, "Norman Jones"
wrote:



HiAkash,

Your code works for me. Perhaps. however, the instruction:

wrksh.Range("A1").CurrentRegion

is not retuning the data on subsequent worksheets.

However, for more robust code, see Ron de Bruin's sample
code at:

Merge cells from all or some worksheets into one Master sheet
http://www.rondebruin.nl/copy2.htm

Note also Ron's use of his LastRow Function to update the
destination range.

Incidentally, I would strongly encourage you to always explicitly
declare all variables.

---
Regards,
Norman

"Akash" wrote in message

roups.com...

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Sorry, mistake in new rw statement. Should be
' rw = Sheets("Sheet8").UsedRange.Rows.Count + 2
Instead of incrementing, would be simpler to also use this technique
for new col:
' col = Sheets("Sheet8").UsedRange.Columns.Count + 2

Carl.

On May 5, 7:35 am, wrote:
Akash

Make a variable for column just as you have for row. After the data
is copied, use the mod function to tell whether to increment to the
next column or to the next row. To find a new row on the destination
sheet, you must find the rows used on the destination sheet after the
third copy.

If i mod 3 is 0, col = 1 : row =
destination_sheet.UsedRange.Rows.Count +2
Else (row is unchanged) : col = col +
source_sheet.Range("A1").CurrentRegion.Rows.Count +2

This macro will show you the values:
Sub Temp4Akash()
Dim rw As Long, col As Long, wrksh As Integer
Debug.Print "SHEET", "MOD", "ACTION"
' In this example, 7 sheets are copied to Sheet8
For wrksh = 1 To 7
' copy your data here
If wrksh 1 And wrksh Mod 3 = 0 Then
Debug.Print "Sheet" & wrksh, wrksh Mod 3, "col = 1, new row"
col = 1
' uncomment when ready to implement
' rw = rw + Sheets("Sheet8").Range("A1").CurrentRegion.Rows.Co unt
Else
Debug.Print "Sheet" & wrksh, wrksh Mod 3, "new col, same row"
' uncomment when ready to implement
' col = col + Sheets("Sheet" &
wrksh).Range("A1").CurrentRegion.Columns.Count
' rw unchanged
End If
Next wrksh
End Sub

Carl

On May 4, 6:45 am, Akash wrote:



Hi Norman,

Thanks for the suggestion but i wanted in some other way.

Right now whats happening is its comming Vertically... I want
horizontally.

Right Now its comming like.

Data Of Sheet 1
Data Of Sheet 2
Data Of Sheet 3
Data Of Sheet 4

I want in this passion

Data Of Sheet 1 | Data Of Sheet 2 | Data Of Sheet 3 |
Data Of Sheet 4

Is it possible what changes should i do in my 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

Pls do help me in this regards.

Thanks

Akash

On Apr 25, 1:47 pm, "Norman Jones"
wrote:

HiAkash,

Your code works for me. Perhaps. however, the instruction:

wrksh.Range("A1").CurrentRegion

is not retuning the data on subsequent worksheets.

However, for more robust code, see Ron de Bruin's sample
code at:

Merge cells from all or some worksheets into one Master sheet
http://www.rondebruin.nl/copy2.htm

Note also Ron's use of his LastRow Function to update the
destination range.

Incidentally, I would strongly encourage you to always explicitly
declare all variables.

---
Regards,
Norman

"Akash" wrote in message

roups.com...

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -