Copying rows from Subsidary Files To Mastersheet with Formulas

[Volker Hormuth]

Hallo NG,

the solution from Don/Merjet is also useful for me, but i need a little
modification.
(Thread: Copying rows from Subsidary Files To Mastersheet

How is the code to change, that also formulas are copied into the
Master-Table ?

Thanks !
Volker

 

[Bob Phillips]

Volker,

The last question suggested that you needed to switch from sheets to
workbooks, is this still the case?

Post your current code and explain exactly what you want to change.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Volker Hormuth]

Hallo Bob

1. I want to copy rows including formulas from several sheets in sheet
"Master" ,

2. I also can need the code for copying from different files, sheets always
have the same name "Selektion", to a master-file, sheet is named "Sammlung"

Thanks
Volker

This is the code from NG (only values are copied)


Sub Combine()
Dim ws As Worksheet
Dim iRow1 As Long
Dim iRow2 As Long
Dim iCol As Integer

iRow1 = Sheets("Master").Range("A65536").End(xlUp).Row
For Each ws In Worksheets
If ws.Name <> "Master" Then
Debug.Print ws.Name
iRow2 = 1
Do Until ws.Cells(iRow2, "B") = ""
'If ws.Cells(iRow2, "A") = "Y" Then
If UCase(ws.Cells(iRow2, "A")) = "Y" Then
iRow1 = iRow1 + 1
For iCol = 1 To 5
Sheets("Master").Cells(iRow1, iCol) = ws.Cells(iRow2, iCol)
Next iCol
End If
iRow2 = iRow2 + 1
Loop
End If
Next ws

End Sub

 

[Bob Phillips]

Volker,

Here is the code to copy from several sheets to 1 using the code you
supplied, including formulae

Sub Combine()
Dim ws As Worksheet
Dim iRow1 As Long
Dim iRow2 As Long
Dim iCol As Integer

iRow1 = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
For Each ws In Worksheets
If ws.Name <> "Master" Then
Debug.Print ws.Name
iRow2 = 1
Do Until ws.Cells(iRow2, "B") = ""
If UCase(ws.Cells(iRow2, "A")) = "Y" Then
iRow1 = iRow1 + 1
ws.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy
Destination:=Sheets("Master").Cells(iRow1, 1)
End If
iRow2 = iRow2 + 1
Loop
End If
Next ws

End Sub


And this copies from several workbooks to 1, including formulae. I load the
workbook names into an aray, so just change to suit your files.

Sub Combine2()
Dim aryWBs(4)
Dim ThisBookMaster As Worksheet
Dim ws As Worksheet
Dim iWB As Long
Dim iRow1 As Long
Dim iRow2 As Long
Dim iCol As Integer

aryWBs(0) = "C:\MyTest\TestFile_1.xls"
aryWBs(1) = "C:\MyTest\TestFile_2.xls"
aryWBs(2) = "C:\MyTest\TestFile_3.xls"
aryWBs(3) = "C:\MyTest\TestFile_4.xls"
aryWBs(4) = "C:\MyTest\TestFile_5.xls"

Set ThisBookMaster = ThisWorkbook.Sheets("Master")
iRow1 = ThisBookMaster.Range("A" & Rows.Count).End(xlUp).Row
For iWB = 0 To 4
Workbooks.Open Filename:=aryWBs(iWB)
iRow2 = 1
With ActiveWorkbook.Worksheets("Selektion")
Do Until .Cells(iRow2, "B") = ""
If UCase(.Cells(iRow2, "A")) = "Y" Then
iRow1 = iRow1 + 1
.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy
Destination:=ThisBookMaster.Cells(iRow1, 1)
End If
iRow2 = iRow2 + 1
Loop
End With
ActiveWorkbook.Close savechanges:=False
Next iWB

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Volker,

Looking at this post since it hit the NG, the crucial line has wrap-around.

Replace
ws.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy
Destination:=Sheets("Master").Cells(iRow1, 1)

with
ws.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
Destination:=Sheets("Master").Cells(iRow1, 1)

and

.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy
Destination:=ThisBookMaster.Cells(iRow1, 1)

with

.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Volker Hormuth]

Hallo Bob,

I got a mistake 1004 at this line:

ws.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy
Destination:=Sheets("Master").Cells(iRow1, 1)

What can I change ?

Volker

 

[Volker Hormuth]

Bob,

this line-wrap I had changed before I saw your message, but this is not the
mistake .

Volker

 

[Bob Phillips]

Volker,

Is it because your sheet is not called Master, but Sammlung?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Volker,

I was grasping at straws then, I knew it wouldn't really be the problem.

I think I have found the real problem. Change the line to

ws.Range(ws.Cells(iRow2, 1), ws.Cells(iRow2, 5)).Copy _
Destination:=Sheets("Master").Cells(iRow1, 1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Volker Hormuth]

Hallo Bob,

this was the solution.
The second code I will test tomorrow.
Thanks for your help.

Volker

 

[Volker Hormuth]

Hi Bob,

now I have tested the second code (Combine2), after changing the lines.
The book and the sheet are named "Master".

I get an error-message 91 (with-blockvariable not defined) at this line.

ws.Range(ws.Cells(iRow2, 1), ws.Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)

What is the correct code ?

Thanks
Volker

 

[Bob Phillips]

Volker,

You must be doing something wrong, Combine2 doesn't have that line of code
in it. Re-check the post in the thread.

The only thing I can think is that you copied my correction, which should
only have applied to Combine, into Combine2 as well. If this is the case,
replace the offending line with

.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Volker Hormuth]

Bob,

yes I had changed the code from "Combine2" analog "Combine".
Thats now the first code, with error 1004 after I have replaced your last
lines:

Sub Combine2()

Dim aryWBs(4)
Dim ThisBookMaster As Worksheet
Dim ws As Worksheet
Dim iWB As Long
Dim iRow1 As Long
Dim iRow2 As Long
Dim iCol As Integer

aryWBs(0) = "C:\MyTest\TestFile_1.xls"
aryWBs(1) = "C:\MyTest\TestFile_2.xls"
aryWBs(2) = "C:\MyTest\TestFile_3.xls"
aryWBs(3) = "C:\MyTest\TestFile_4.xls"
aryWBs(4) = "C:\MyTest\TestFile_5.xls"

Set ThisBookMaster = ThisWorkbook.Sheets("Master")
iRow1 = ThisBookMaster.Range("A" & Rows.Count).End(xlUp).Row
For iWB = 0 To 4
Workbooks.Open Filename:=aryWBs(iWB)
iRow2 = 1
With ActiveWorkbook.Worksheets("Selektion")
Do Until .Cells(iRow2, "B") = ""
If UCase(.Cells(iRow2, "A")) = "Y" Then
iRow1 = iRow1 + 1
.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)
End If
iRow2 = iRow2 + 1
Loop
End With
ActiveWorkbook.Close savechanges:=False
Next iWB

End Sub

mfg
Volker

 

[Bob Phillips]

Sorry Volker, are you saying you are getting Error 1004 now, or is all okay?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Volker Hormuth]

Hallo Bob,

first I got error 91, after replacing the lines error 1004.
The code is not running.

mfg
Volker

 

[Bob Phillips]

Volker,

I have just tested the code that you re-posted, and it works okay for me.

What line do you get that error on?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Volker Hormuth]

Bob,

thats the line:

.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)

Volker

Volker,

I have just tested the code that you re-posted, and it works okay for me.

What line do you get that error on?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hallo Bob,

first I got error 91, after replacing the lines error 1004.
The code is not running.

mfg
Volker

5)).Copy

_

line

of

code
in it. Re-check the post in the thread.

The only thing I can think is that you copied my correction, which
should
only have applied to Combine, into Combine2 as well. If this is the
case,
replace the offending line with

.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi Bob,

now I have tested the second code (Combine2), after changing the
lines.
The book and the sheet are named "Master".

I get an error-message 91 (with-blockvariable not defined) at this
line.

ws.Range(ws.Cells(iRow2, 1), ws.Cells(iRow2,
5)).Copy
_
Destination:=ThisBookMaster.Cells(iRow1, 1)

What is the correct code ?

Thanks
Volker



Hallo Bob,

this was the solution.
The second code I will test tomorrow.
Thanks for your help.

Volker



Newsbeitrag
Volker,

I was grasping at straws then, I knew it wouldn't really be the
problem.

I think I have found the real problem. Change the line to

ws.Range(ws.Cells(iRow2, 1), ws.Cells(iRow2,
5)).Copy
_
Destination:=Sheets("Master").Cells(iRow1,
1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

message
Volker,

Is it because your sheet is not called Master, but Sammlung?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hallo Bob,

I got a mistake 1004 at this line:

ws.Range(Cells(iRow2, 1), Cells(iRow2,
5)).Copy
Destination:=Sheets("Master").Cells(iRow1, 1)

What can I change ?

Volker

"Bob Phillips" <[email protected]>

schrieb
im

Newsbeitrag
Volker,

Here is the code to copy from several sheets to 1

using
the

code
you
supplied, including formulae

Sub Combine()
Dim ws As Worksheet
Dim iRow1 As Long
Dim iRow2 As Long
Dim iCol As Integer

iRow1 = Sheets("Master").Range("A" &
Rows.Count).End(xlUp).Row
For Each ws In Worksheets
If ws.Name <> "Master" Then
Debug.Print ws.Name
iRow2 = 1
Do Until ws.Cells(iRow2, "B") = ""
If UCase(ws.Cells(iRow2, "A")) = "Y" Then
iRow1 = iRow1 + 1
ws.Range(Cells(iRow2, 1), Cells(iRow2,
5)).Copy
Destination:=Sheets("Master").Cells(iRow1, 1)
End If
iRow2 = iRow2 + 1
Loop
End If
Next ws

End Sub


And this copies from several workbooks to 1, including
formulae.
I
load
the
workbook names into an aray, so just change to suit your
files.

Sub Combine2()
Dim aryWBs(4)
Dim ThisBookMaster As Worksheet
Dim ws As Worksheet
Dim iWB As Long
Dim iRow1 As Long
Dim iRow2 As Long
Dim iCol As Integer

aryWBs(0) = "C:\MyTest\TestFile_1.xls"
aryWBs(1) = "C:\MyTest\TestFile_2.xls"
aryWBs(2) = "C:\MyTest\TestFile_3.xls"
aryWBs(3) = "C:\MyTest\TestFile_4.xls"
aryWBs(4) = "C:\MyTest\TestFile_5.xls"

Set ThisBookMaster = ThisWorkbook.Sheets("Master")
iRow1 = ThisBookMaster.Range("A" &
Rows.Count).End(xlUp).Row
For iWB = 0 To 4
Workbooks.Open Filename:=aryWBs(iWB)
iRow2 = 1
With ActiveWorkbook.Worksheets("Selektion")
Do Until .Cells(iRow2, "B") = ""
If UCase(.Cells(iRow2, "A")) = "Y" Then
iRow1 = iRow1 + 1
.Range(Cells(iRow2, 1), Cells(iRow2,
5)).Copy
Destination:=ThisBookMaster.Cells(iRow1, 1)
End If
iRow2 = iRow2 + 1
Loop
End With
ActiveWorkbook.Close savechanges:=False
Next iWB

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

message
Hallo Bob

1. I want to copy rows including formulas from several
sheets
in
sheet
"Master" ,

2. I also can need the code for copying from different
files,
sheets
always
have the same name "Selektion", to a master-file,

sheet

is
named
"Sammlung"

Thanks
Volker

This is the code from NG (only values are copied)


Sub Combine()
Dim ws As Worksheet
Dim iRow1 As Long
Dim iRow2 As Long
Dim iCol As Integer

iRow1 = Sheets("Master").Range("A65536").End(xlUp).Row
For Each ws In Worksheets
If ws.Name <> "Master" Then
Debug.Print ws.Name
iRow2 = 1
Do Until ws.Cells(iRow2, "B") = ""
'If ws.Cells(iRow2, "A") = "Y" Then
If UCase(ws.Cells(iRow2, "A")) = "Y" Then
iRow1 = iRow1 + 1
For iCol = 1 To 5
Sheets("Master").Cells(iRow1, iCol) = ws.Cells(iRow2,
iCol)
Next iCol
End If
iRow2 = iRow2 + 1
Loop
End If
Next ws

End Sub

im
Newsbeitrag
Volker,

The last question suggested that you needed to switch
from
sheets
to
workbooks, is this still the case?

Post your current code and explain exactly what

you
want

 

[Bob Phillips]

Volker,

I am using a With clause to reference the sheets to copy from, so if it were
that it would have already failed. So I guess it's the Master sheet. This is
referenced by the object variable ThisbookMaster, and is setup in the line

Set ThisBookMaster = ThisWorkbook.Sheets("Master")

So at this point the only thing I can suggest is that you sheet is not
called Master (Sammlung?), and you have not changed that line to reflect it?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,

thats the line:

.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)

Volker

Volker,

I have just tested the code that you re-posted, and it works okay for me.

What line do you get that error on?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

5)).Copy line

5)).Copy

 

[Bob Phillips]

Volker,

Sorry, I was being particularly dense. It's the same intrinsic problem as
before (must figure out why it worked in my test though!).

Change
.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)
to
.Range(.Cells(iRow2, 1), .Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)

Sorry for taking so long over it.

Regards

Bob

Bob,

I have checked your suggestions, but can`t find what is wrong.
I know it isn`t right zu send an attachment, but thats the shortest way to
get a result.

mfg
Volker

Volker,

I am using a With clause to reference the sheets to copy from, so if it were
that it would have already failed. So I guess it's the Master sheet.

This

 

[Wohnungsbau GmbH]

Hi Bob,

the code will do the job.
I save many hours !
Thanks ...

Volker

Volker,

Sorry, I was being particularly dense. It's the same intrinsic problem as
before (must figure out why it worked in my test though!).

Change
.Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)
to
.Range(.Cells(iRow2, 1), .Cells(iRow2, 5)).Copy _
Destination:=ThisBookMaster.Cells(iRow1, 1)

Sorry for taking so long over it.

Regards

Bob

Bob,

I have checked your suggestions, but can`t find what is wrong.
I know it isn`t right zu send an attachment, but thats the shortest way to
get a result.

mfg
Volker


it
were This reflect
it?

or

is

all
okay?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,

yes I had changed the code from "Combine2" analog "Combine".
Thats now the first code, with error 1004 after I have replaced
your
last
lines:

Sub Combine2()

Dim aryWBs(4)
Dim ThisBookMaster As Worksheet
Dim ws As Worksheet
Dim iWB As Long
Dim iRow1 As Long
Dim iRow2 As Long
Dim iCol As Integer

aryWBs(0) = "C:\MyTest\TestFile_1.xls"
aryWBs(1) = "C:\MyTest\TestFile_2.xls"
aryWBs(2) = "C:\MyTest\TestFile_3.xls"
aryWBs(3) = "C:\MyTest\TestFile_4.xls"
aryWBs(4) = "C:\MyTest\TestFile_5.xls"

Set ThisBookMaster = ThisWorkbook.Sheets("Master")
iRow1 = ThisBookMaster.Range("A" & Rows.Count).End(xlUp).Row
For iWB = 0 To 4
Workbooks.Open Filename:=aryWBs(iWB)
iRow2 = 1
With ActiveWorkbook.Worksheets("Selektion")
Do Until .Cells(iRow2, "B") = ""
If UCase(.Cells(iRow2, "A")) = "Y" Then
iRow1 = iRow1 + 1
.Range(Cells(iRow2, 1), Cells(iRow2,
5)).Copy
_
Destination:=ThisBookMaster.Cells(iRow1,
1)
End If
iRow2 = iRow2 + 1
Loop
End With
ActiveWorkbook.Close savechanges:=False
Next iWB

End Sub

mfg
Volker



Newsbeitrag
Volker,

You must be doing something wrong, Combine2 doesn't have that
line
of
code
in it. Re-check the post in the thread.

The only thing I can think is that you copied my correction,
which
should
only have applied to Combine, into Combine2 as well. If

this
is

the
case,
replace the offending line with

.Range(Cells(iRow2, 1), Cells(iRow2,
5)).Copy
_
Destination:=ThisBookMaster.Cells(iRow1,
1)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi Bob,

now I have tested the second code (Combine2), after changing
the
lines.
The book and the sheet are named "Master".

I get an error-message 91 (with-blockvariable not

defined)
at

this
line.

ws.Range(ws.Cells(iRow2, 1),
ws.Cells(iRow2,
5)).Copy
_

Destination:=ThisBookMaster.Cells(iRow1,
1)

What is the correct code ?

Thanks
Volker



Newsbeitrag
Hallo Bob,

this was the solution.
The second code I will test tomorrow.
Thanks for your help.

Volker



im
Newsbeitrag
Volker,

I was grasping at straws then, I knew it wouldn't really
be
the
problem.

I think I have found the real problem. Change the

line
to