Pasting two named tabs one below the other
 

What I have is a work book with named tabs that i need to make a macro for
that will take the data from two different pages and paste it to another
page that would have the combined data of the pasted pages. The amount of
data can very as well that is on each page. I would also like to be able to
have it place a total line blow the pasted data for each of the pasted
columns. Please help.

--
Message posted via http://www.officekb.com

Pasting two named tabs one below the other
 

hi jln

have built you something:

It's designed for a Workbook with 3 sheets which are named:
"1","2" and "3", if you want to summarize more then 2 sheets
you just have to enlarge the array!
'---------------------------------------------
Sub summarizer()

Dim i As Integer
i = 1

Dim arr_sheets(1) As String

arr_sheets(0) = "1" '1st Sheet
arr_sheets(1) = "2"'2nd Sheet

For j = 0 To 1
For Each row_ In Sheets(arr_sheets(j)).Rows
Debug.Print row_.Cells(1, 1)

If row_.Cells(1, 1) < "" Then
row_.Copy
Worksheets("3").Rows(i).Insert 'Insert in 3rd sheet
i = i + 1
Else
Exit For
End If

Next row_
Next j


'Total at the End
For k = 1 To 256
If Worksheets("3").Cells(i - 1, k) < "" Then
Worksheets("3").Cells(i, k).FormulaR1C1 = "=SUM(R1C" & k & ":R" & i
- 1 & "C" & k & ")"
Else
Exit For
End If
Next k

End Sub
'------------------------------------------------------
Hth

Cheers Carlo


"jln via OfficeKB.com" wrote:

What I have is a work book with named tabs that i need to make a macro for
that will take the data from two different pages and paste it to another
page that would have the combined data of the pasted pages. The amount of
data can very as well that is on each page. I would also like to be able to
have it place a total line blow the pasted data for each of the pasted
columns. Please help.

--
Message posted via http://www.officekb.com

Pasting two named tabs one below the other
 

Carlo the code looks great
Im getting an app defined or object error
Sub Test()
Dim i As Integer
i = 1

Dim arr_sheets(1) As String

arr_sheets(0) = "MGICLPMI" '1st Sheet
arr_sheets(1) = "RMICLPMI" '2nd Sheet

For j = 0 To 1
For Each row_ In Sheets(arr_sheets(j)).Rows
Debug.Print row_.Cells(1, 1)

If row_.Cells(1, 1) < "" Then
row_.Copy
Worksheets("Combine LPMI").Rows(i).Insert 'Insert in 3rd sheet
i = i + 1
Else
Exit For
End If

Next row_
Next j

'Total at the End
For k = 1 To 256

If Worksheets("Combine LPMI").Cells(i - 1, k) < "" Then
' Here is where im getting the probelm I dont see why it shouldnt work.

Worksheets("Combine LPMI").Cells(i, k).FormulaR1C1 = "=SUM(R1C" & k &
":R" & i - 1 & "C" & k & ")"
Else
Exit For
End If
Next k
End Sub

--
Message posted via http://www.officekb.com

Pasting two named tabs one below the other
 

hmm...thats strange, the first time you refer to your 3rd Worksheet,
it works!!

some questions to evaluate the problem:
What value does "i"/"k" have on the position where you get the error?
what do you get if put following lines in front of the if, where the error
comes:
debug.print Worksheets("Combine LPMI").Cells(i - 1, k)
debug.print Worksheets("Combine LPMI").Name

hope this helps to resolve the issue

Carlo

"jln via OfficeKB.com" wrote:

Carlo the code looks great
Im getting an app defined or object error
Sub Test()
Dim i As Integer
i = 1

Dim arr_sheets(1) As String

arr_sheets(0) = "MGICLPMI" '1st Sheet
arr_sheets(1) = "RMICLPMI" '2nd Sheet

For j = 0 To 1
For Each row_ In Sheets(arr_sheets(j)).Rows
Debug.Print row_.Cells(1, 1)

If row_.Cells(1, 1) < "" Then
row_.Copy
Worksheets("Combine LPMI").Rows(i).Insert 'Insert in 3rd sheet
i = i + 1
Else
Exit For
End If

Next row_
Next j

'Total at the End
For k = 1 To 256

If Worksheets("Combine LPMI").Cells(i - 1, k) < "" Then
' Here is where im getting the probelm I dont see why it shouldnt work.

Worksheets("Combine LPMI").Cells(i, k).FormulaR1C1 = "=SUM(R1C" & k &
":R" & i - 1 & "C" & k & ")"
Else
Exit For
End If
Next k
End Sub

--
Message posted via http://www.officekb.com

Pasting two named tabs one below the other
 

Carlo Thanks I got it working it was a mistake on my end not the code.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1