Combine worksheet

[Guest]

I got a workbook which has 6 worksheets include 1 for total and 5 for staff to input the cheque no, amount and record no. Then I have to combine all the input to total, however, the no of item is not the same everytimes. So please help to use macro to copy and paste value of all input to one worksheet and automatically put under the next blank row, then count the no of cheque and sum the total of amount. Thanks!!

 

[pikus]

Sup?

Dim x As Integer
Dim y As Integer

For n = 2 To Sheets.Count 'If the "Totals" sheet is the last sheet the
this line should be "For n = 1 To Workbook.Sheets.Count - 1

With Worksheets(n)
x = .UsedRange.Row - 1 + .UsedRange.Rows.Count
y = .UsedRange.Column - 1 + .UsedRange.Columns.Count

.Range(.Cells(1, 1), .Cells(x, y)).Copy
End With

With Worksheets(1)
x = .UsedRange.Row + .UsedRange.Rows.Count
.Cells(x, 1).Insert
End With

Next n

- Piku

 

[Guest]

hi
As my 5 worksheets within the workbook are not the same in size, e.g. (1) has 100 rows, (2) has 200 row. How to amend the macro to cater the different size and paste to the same worksheet "Total"

----- pikus > wrote: ----

Sup

Dim x As Intege
Dim y As Intege

For n = 2 To Sheets.Count 'If the "Totals" sheet is the last sheet the
this line should be "For n = 1 To Workbook.Sheets.Count -

With Worksheets(n
x = .UsedRange.Row - 1 + .UsedRange.Rows.Coun
y = .UsedRange.Column - 1 + .UsedRange.Columns.Coun

.Range(.Cells(1, 1), .Cells(x, y)).Cop
End Wit

With Worksheets(1
x = .UsedRange.Row + .UsedRange.Rows.Coun
.Cells(x, 1).Inser
End Wit

Next

- Piku

 

[pikus]

This works completely no matter the number or rows in any give sheet.
In fact it doesn't even care about the number of columns either. Thi
part:

With Worksheets(n)
x = .UsedRange.Row - 1 + .UsedRange.Rows.Count
y = .UsedRange.Column - 1 + .UsedRange.Columns.Count

.Range(.Cells(1, 1), .Cells(x, y)).Copy
End With

selects the entire used range of each page and copies it.
"UsedRange.Row" finds the FIRST row with information (presumably thi
would be the first row, but you can never be too careful.) The
"UsedRange.Rows.Count" returns the number of rows used and adds the
together. Now after you've added the first row (say 1) and the numbe
of used rows (say ten) the result (11) is actually the number of th
row immediately after the last used row so we must subtract 1 from th
sum.

After that's done,

With Worksheets(1)
x = .UsedRange.Row + .UsedRange.Rows.Count

finds the first empty row in the totals sheet (notice the 1 is no
subtracted) and pastes in the information.

If this is copying more cells than you want it to, I'd say you probabl
have information in cells you don't know about. Maybe some spaces or i
a font that's the same color as the background. If that does not solv
your roblem, just post more specifics. - Piku

 

[Guest]

Hi
Thanks for your explanation. When I try to run the macro, excel has the following message:" The information cannot be pasted because the copy arean and the paste area are not the same size and shape. try one of the following: - click a single cell, then paste, - select a rectangle that's the same size and shape , and then paste." Please help

Regards
Janmy