Copying rows from Subsidary Files To Mastersheet with Formulas

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


0
11/30/2003 11:05:04 AM
excel.misc 78881 articles. 5 followers. Follow

20 Replies
379 Views

Similar Articles

[PageSpeed] 23

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" <volker.hormuth@t-online.de> wrote in message
news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> 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
>
>


0
bob.phillips1 (6510)
11/30/2003 1:53:00 PM
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" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> 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" <volker.hormuth@t-online.de> wrote in message
> news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> >
>
>


0
11/30/2003 5:41:05 PM
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)

"Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
> news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > 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" <volker.hormuth@t-online.de> wrote in message
> > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
11/30/2003 6:05:10 PM
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)

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> 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)
>
> "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
Newsbeitrag
> > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > 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" <volker.hormuth@t-online.de> wrote in message
> > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
11/30/2003 6:13:17 PM
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" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> 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)
>
> "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
Newsbeitrag
> > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > 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" <volker.hormuth@t-online.de> wrote in message
> > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
11/30/2003 6:38:36 PM
Bob,

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

Volker


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
news:Ootyi22tDHA.2440@TK2MSFTNGP12.phx.gbl...
> 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)
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > 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)
> >
> > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> Newsbeitrag
> > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
11/30/2003 6:58:12 PM
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)

"Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
> news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > 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)
> >
> > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> Newsbeitrag
> > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
11/30/2003 7:38:13 PM
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)

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> 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)
>
> "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
Newsbeitrag
> > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > 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)
> > >
> > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > Newsbeitrag
> > > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
11/30/2003 7:45:59 PM
Hallo Bob,

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

Volker



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> 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)
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > 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)
> >
> > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> Newsbeitrag
> > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > 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)
> > > >
> > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > Newsbeitrag
> > > > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
11/30/2003 10:22:11 PM
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



"Volker Hormuth" <volker.hormuth@t-online.de> schrieb im Newsbeitrag
news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> Hallo Bob,
>
> this was the solution.
> The second code I will test tomorrow.
> Thanks for your help.
>
> Volker
>
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
> news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > 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)
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > 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)
> > >
> > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > Newsbeitrag
> > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > 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)
> > > > >
> > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > > Newsbeitrag
> > > > > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
12/1/2003 6:10:15 PM
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" <volker.hormuth@t-online.de> wrote in message
news:%239%23CjZDuDHA.2060@TK2MSFTNGP10.phx.gbl...
> 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
>
>
>
> "Volker Hormuth" <volker.hormuth@t-online.de> schrieb im Newsbeitrag
> news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> > Hallo Bob,
> >
> > this was the solution.
> > The second code I will test tomorrow.
> > Thanks for your help.
> >
> > Volker
> >
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
Newsbeitrag
> > news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > 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)
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > > 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)
> > > >
> > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > Newsbeitrag
> > > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > > 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)
> > > > > >
> > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > > > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > > > Newsbeitrag
> > > > > > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > > 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" <volker.hormuth@t-online.de> wrote in
message
> > > > > > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
12/1/2003 6:49:19 PM
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" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
news:uRBNVvDuDHA.1224@TK2MSFTNGP09.phx.gbl...
> 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" <volker.hormuth@t-online.de> wrote in message
> news:%239%23CjZDuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> >
> >
> > "Volker Hormuth" <volker.hormuth@t-online.de> schrieb im Newsbeitrag
> > news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> > > Hallo Bob,
> > >
> > > this was the solution.
> > > The second code I will test tomorrow.
> > > Thanks for your help.
> > >
> > > Volker
> > >
> > >
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> Newsbeitrag
> > > news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > > 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)
> > > >
> > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > > > 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)
> > > > >
> > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > > Newsbeitrag
> > > > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > > > 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)
> > > > > > >
> > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > > > > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb
im
> > > > > > Newsbeitrag
> > > > > > > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > 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" <volker.hormuth@t-online.de> wrote in
> message
> > > > > > > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > 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
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
12/1/2003 7:39:49 PM
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" <volker.hormuth@t-online.de> wrote in message
news:OCS6iLEuDHA.2060@TK2MSFTNGP10.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
> news:uRBNVvDuDHA.1224@TK2MSFTNGP09.phx.gbl...
> > 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" <volker.hormuth@t-online.de> wrote in message
> > news:%239%23CjZDuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > 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
> > >
> > >
> > >
> > > "Volker Hormuth" <volker.hormuth@t-online.de> schrieb im Newsbeitrag
> > > news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> > > > Hallo Bob,
> > > >
> > > > this was the solution.
> > > > The second code I will test tomorrow.
> > > > Thanks for your help.
> > > >
> > > > Volker
> > > >
> > > >
> > > >
> > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> > Newsbeitrag
> > > > news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > > > 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)
> > > > >
> > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
message
> > > > > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > > > > 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)
> > > > > >
> > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > > > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > > > Newsbeitrag
> > > > > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > > > > 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)
> > > > > > > >
> > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in
message
> > > > > > > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb
> im
> > > > > > > Newsbeitrag
> > > > > > > > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > 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" <volker.hormuth@t-online.de> wrote in
> > message
> > > > > > > > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > 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
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
12/1/2003 7:58:55 PM
Hallo Bob,

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

mfg
Volker


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
news:%236WSOWEuDHA.4060@TK2MSFTNGP11.phx.gbl...
> 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" <volker.hormuth@t-online.de> wrote in message
> news:OCS6iLEuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
Newsbeitrag
> > news:uRBNVvDuDHA.1224@TK2MSFTNGP09.phx.gbl...
> > > 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" <volker.hormuth@t-online.de> wrote in message
> > > news:%239%23CjZDuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > 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
> > > >
> > > >
> > > >
> > > > "Volker Hormuth" <volker.hormuth@t-online.de> schrieb im Newsbeitrag
> > > > news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> > > > > Hallo Bob,
> > > > >
> > > > > this was the solution.
> > > > > The second code I will test tomorrow.
> > > > > Thanks for your help.
> > > > >
> > > > > Volker
> > > > >
> > > > >
> > > > >
> > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > Newsbeitrag
> > > > > news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > > > > 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)
> > > > > >
> > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
> message
> > > > > > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > > > > > 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)
> > > > > > >
> > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > > > > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb
im
> > > > > > Newsbeitrag
> > > > > > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > > > > > 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)
> > > > > > > > >
> > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in
> message
> > > > > > > > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > > > > > 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" <bob.phillips@notheretiscali.co.uk>
schrieb
> > im
> > > > > > > > Newsbeitrag
> > > > > > > > > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > 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" <volker.hormuth@t-online.de> wrote in
> > > message
> > > > > > > > > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > 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
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
12/1/2003 8:54:02 PM
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" <volker.hormuth@t-online.de> wrote in message
news:OA2oA1EuDHA.1872@TK2MSFTNGP09.phx.gbl...
> Hallo Bob,
>
> first I got error 91, after replacing the lines error 1004.
> The code is not running.
>
> mfg
> Volker
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
> news:%236WSOWEuDHA.4060@TK2MSFTNGP11.phx.gbl...
> > 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" <volker.hormuth@t-online.de> wrote in message
> > news:OCS6iLEuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> Newsbeitrag
> > > news:uRBNVvDuDHA.1224@TK2MSFTNGP09.phx.gbl...
> > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > news:%239%23CjZDuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > > > "Volker Hormuth" <volker.hormuth@t-online.de> schrieb im
Newsbeitrag
> > > > > news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> > > > > > Hallo Bob,
> > > > > >
> > > > > > this was the solution.
> > > > > > The second code I will test tomorrow.
> > > > > > Thanks for your help.
> > > > > >
> > > > > > Volker
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > > Newsbeitrag
> > > > > > news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > > > > > 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)
> > > > > > >
> > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
> > message
> > > > > > > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > > > > > > 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)
> > > > > > > >
> > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in
message
> > > > > > > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb
> im
> > > > > > > Newsbeitrag
> > > > > > > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > > > > > > 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)
> > > > > > > > > >
> > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in
> > message
> > > > > > > > > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > > > > > > 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" <bob.phillips@notheretiscali.co.uk>
> schrieb
> > > im
> > > > > > > > > Newsbeitrag
> > > > > > > > > > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > 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" <volker.hormuth@t-online.de> wrote
in
> > > > message
> > > > > > > > > > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > 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
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
12/1/2003 9:11:59 PM
Bob,

thats the line:

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

Volker


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
news:uW5BD$EuDHA.2260@TK2MSFTNGP09.phx.gbl...
> 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" <volker.hormuth@t-online.de> wrote in message
> news:OA2oA1EuDHA.1872@TK2MSFTNGP09.phx.gbl...
> > Hallo Bob,
> >
> > first I got error 91, after replacing the lines error 1004.
> > The code is not running.
> >
> > mfg
> > Volker
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
Newsbeitrag
> > news:%236WSOWEuDHA.4060@TK2MSFTNGP11.phx.gbl...
> > > 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" <volker.hormuth@t-online.de> wrote in message
> > > news:OCS6iLEuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > Newsbeitrag
> > > > news:uRBNVvDuDHA.1224@TK2MSFTNGP09.phx.gbl...
> > > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > > news:%239%23CjZDuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> schrieb im
> Newsbeitrag
> > > > > > news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> > > > > > > Hallo Bob,
> > > > > > >
> > > > > > > this was the solution.
> > > > > > > The second code I will test tomorrow.
> > > > > > > Thanks for your help.
> > > > > > >
> > > > > > > Volker
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > > > Newsbeitrag
> > > > > > > news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > > > > > > 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)
> > > > > > > >
> > > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
> > > message
> > > > > > > > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > > > > > > > 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)
> > > > > > > > >
> > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in
> message
> > > > > > > > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > > > > > > > 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" <bob.phillips@notheretiscali.co.uk>
schrieb
> > im
> > > > > > > > Newsbeitrag
> > > > > > > > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > > > > > > > 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)
> > > > > > > > > > >
> > > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in
> > > message
> > > > > > > > > > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > > > > > > > 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" <bob.phillips@notheretiscali.co.uk>
> > schrieb
> > > > im
> > > > > > > > > > Newsbeitrag
> > > > > > > > > > > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > 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" <volker.hormuth@t-online.de>
wrote
> in
> > > > > message
> > > > > > > > > > > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > > 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
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
12/1/2003 10:31:10 PM
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)

"Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
news:ORKJTrFuDHA.2380@TK2MSFTNGP09.phx.gbl...
> Bob,
>
> thats the line:
>
>           .Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
>                        Destination:=ThisBookMaster.Cells(iRow1, 1)
>
> Volker
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
> news:uW5BD$EuDHA.2260@TK2MSFTNGP09.phx.gbl...
> > 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" <volker.hormuth@t-online.de> wrote in message
> > news:OA2oA1EuDHA.1872@TK2MSFTNGP09.phx.gbl...
> > > Hallo Bob,
> > >
> > > first I got error 91, after replacing the lines error 1004.
> > > The code is not running.
> > >
> > > mfg
> > > Volker
> > >
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> Newsbeitrag
> > > news:%236WSOWEuDHA.4060@TK2MSFTNGP11.phx.gbl...
> > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > news:OCS6iLEuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > Newsbeitrag
> > > > > news:uRBNVvDuDHA.1224@TK2MSFTNGP09.phx.gbl...
> > > > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > > > news:%239%23CjZDuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> schrieb im
> > Newsbeitrag
> > > > > > > news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> > > > > > > > Hallo Bob,
> > > > > > > >
> > > > > > > > this was the solution.
> > > > > > > > The second code I will test tomorrow.
> > > > > > > > Thanks for your help.
> > > > > > > >
> > > > > > > > Volker
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb
im
> > > > > > Newsbeitrag
> > > > > > > > news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > 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)
> > > > > > > > >
> > > > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote
in
> > > > message
> > > > > > > > > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > > > > > > > > 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)
> > > > > > > > > >
> > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in
> > message
> > > > > > > > > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > > > > > > > > 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" <bob.phillips@notheretiscali.co.uk>
> schrieb
> > > im
> > > > > > > > > Newsbeitrag
> > > > > > > > > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > > > > > > > > Volker,
> > > > > > > > > > > >
> > > > > > > > > > > > Here is the code to copy from several sheets to 

> 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)
> > > > > > > > > > > >
> > > > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote
in
> > > > message
> > > > > > > > > > > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > > > > > > > > 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" <bob.phillips@notheretiscali.co.uk>
> > > schrieb
> > > > > im
> > > > > > > > > > > Newsbeitrag
> > > > > > > > > > > > > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > > 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" <volker.hormuth@t-online.de>
> wrote
> > in
> > > > > > message
> > > > > > > > > > > > > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > > > 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
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
12/1/2003 10:45:03 PM
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


"Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
news:O$ys19FuDHA.1740@TK2MSFTNGP12.phx.gbl...
> 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
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
> news:ukuYDzFuDHA.1876@TK2MSFTNGP09.phx.gbl...
> > 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)
> >
> > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > news:ORKJTrFuDHA.2380@TK2MSFTNGP09.phx.gbl...
> > > Bob,
> > >
> > > thats the line:
> > >
> > >           .Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
> > >                        Destination:=ThisBookMaster.Cells(iRow1, 1)
> > >
> > > Volker
> > >
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> Newsbeitrag
> > > news:uW5BD$EuDHA.2260@TK2MSFTNGP09.phx.gbl...
> > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > news:OA2oA1EuDHA.1872@TK2MSFTNGP09.phx.gbl...
> > > > > Hallo Bob,
> > > > >
> > > > > first I got error 91, after replacing the lines error 1004.
> > > > > The code is not running.
> > > > >
> > > > > mfg
> > > > > Volker
> > > > >
> > > > >
> > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > Newsbeitrag
> > > > > news:%236WSOWEuDHA.4060@TK2MSFTNGP11.phx.gbl...
> > > > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > > > news:OCS6iLEuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > > > Newsbeitrag
> > > > > > > news:uRBNVvDuDHA.1224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > 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" <volker.hormuth@t-online.de> wrote in
message
> > > > > > > > news:%239%23CjZDuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> schrieb im
> > > > Newsbeitrag
> > > > > > > > > news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > Hallo Bob,
> > > > > > > > > >
> > > > > > > > > > this was the solution.
> > > > > > > > > > The second code I will test tomorrow.
> > > > > > > > > > Thanks for your help.
> > > > > > > > > >
> > > > > > > > > > Volker
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk>
schrieb
> > im
> > > > > > > > Newsbeitrag
> > > > > > > > > > news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > 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)
> > > > > > > > > > >
> > > > > > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk>
wrote
> > in
> > > > > > message
> > > > > > > > > > > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > > > > > > > > > > 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)
> > > > > > > > > > > >
> > > > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote
in
> > > > message
> > > > > > > > > > > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > > > > > > > > > > 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" <bob.phillips@notheretiscali.co.uk>
> > > schrieb
> > > > > im
> > > > > > > > > > > Newsbeitrag
> > > > > > > > > > > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > > > > > > > > > > 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)
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de>
> wrote
> > in
> > > > > > message
> > > > > > > > > > > > > > news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > > > > > > > > > > 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"
> <bob.phillips@notheretiscali.co.uk>
> > > > > schrieb
> > > > > > > im
> > > > > > > > > > > > > Newsbeitrag
> > > > > > > > > > > > > > >
news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > > > > 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"
<volker.hormuth@t-online.de>
> > > wrote
> > > > in
> > > > > > > > message
> > > > > > > > > > > > > > > >
> news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > > > > > 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
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>


0
bob.phillips1 (6510)
12/1/2003 11:21:40 PM
Hi Bob,

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

Volker


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
news:u5VpkHGuDHA.2456@TK2MSFTNGP12.phx.gbl...
> 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
>
>
> "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> news:O$ys19FuDHA.1740@TK2MSFTNGP12.phx.gbl...
> > 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
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
Newsbeitrag
> > news:ukuYDzFuDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > 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)
> > >
> > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > news:ORKJTrFuDHA.2380@TK2MSFTNGP09.phx.gbl...
> > > > Bob,
> > > >
> > > > thats the line:
> > > >
> > > >           .Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
> > > >                        Destination:=ThisBookMaster.Cells(iRow1, 1)
> > > >
> > > > Volker
> > > >
> > > >
> > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> > Newsbeitrag
> > > > news:uW5BD$EuDHA.2260@TK2MSFTNGP09.phx.gbl...
> > > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > > news:OA2oA1EuDHA.1872@TK2MSFTNGP09.phx.gbl...
> > > > > > Hallo Bob,
> > > > > >
> > > > > > first I got error 91, after replacing the lines error 1004.
> > > > > > The code is not running.
> > > > > >
> > > > > > mfg
> > > > > > Volker
> > > > > >
> > > > > >
> > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > > Newsbeitrag
> > > > > > news:%236WSOWEuDHA.4060@TK2MSFTNGP11.phx.gbl...
> > > > > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > > > > news:OCS6iLEuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb
im
> > > > > > Newsbeitrag
> > > > > > > > news:uRBNVvDuDHA.1224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > 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" <volker.hormuth@t-online.de> wrote in
> message
> > > > > > > > > news:%239%23CjZDuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > > > > > > > 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
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> schrieb im
> > > > > Newsbeitrag
> > > > > > > > > > news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > Hallo Bob,
> > > > > > > > > > >
> > > > > > > > > > > this was the solution.
> > > > > > > > > > > The second code I will test tomorrow.
> > > > > > > > > > > Thanks for your help.
> > > > > > > > > > >
> > > > > > > > > > > Volker
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk>
> schrieb
> > > im
> > > > > > > > > Newsbeitrag
> > > > > > > > > > > news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > 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)
> > > > > > > > > > > >
> > > > > > > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk>
> wrote
> > > in
> > > > > > > message
> > > > > > > > > > > > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > > > > > > > > > > > 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)
> > > > > > > > > > > > >
> > > > > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de>
wrote
> in
> > > > > message
> > > > > > > > > > > > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > > > > > > > > > > > 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"
<bob.phillips@notheretiscali.co.uk>
> > > > schrieb
> > > > > > im
> > > > > > > > > > > > Newsbeitrag
> > > > > > > > > > > > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > > > > > > > > > > > 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)
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de>
> > wrote
> > > in
> > > > > > > message
> > > > > > > > > > > > > > >
news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > > > > > > > > > > > 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"
> > <bob.phillips@notheretiscali.co.uk>
> > > > > > schrieb
> > > > > > > > im
> > > > > > > > > > > > > > Newsbeitrag
> > > > > > > > > > > > > > > >
> news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > > > > > 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"
> <volker.hormuth@t-online.de>
> > > > wrote
> > > > > in
> > > > > > > > > message
> > > > > > > > > > > > > > > > >
> > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > > > > > > 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
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >
>
>


0
12/4/2003 6:22:07 AM
Volker,

Glad we finally cracked it.

Regards

Bob

"Wohnungsbau GmbH" <wohnungsbau-gmbh-worms@t-online.de> wrote in message
news:ebFbN7iuDHA.3744@TK2MSFTNGP11.phx.gbl...
> Hi Bob,
>
> the code will do the job.
> I save many hours !
> Thanks ...
>
> Volker
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag
> news:u5VpkHGuDHA.2456@TK2MSFTNGP12.phx.gbl...
> > 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
> >
> >
> > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > news:O$ys19FuDHA.1740@TK2MSFTNGP12.phx.gbl...
> > > 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
> > >
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> Newsbeitrag
> > > news:ukuYDzFuDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > 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)
> > > >
> > > > "Volker Hormuth" <volker.hormuth@t-online.de> wrote in message
> > > > news:ORKJTrFuDHA.2380@TK2MSFTNGP09.phx.gbl...
> > > > > Bob,
> > > > >
> > > > > thats the line:
> > > > >
> > > > >           .Range(Cells(iRow2, 1), Cells(iRow2, 5)).Copy _
> > > > >                        Destination:=ThisBookMaster.Cells(iRow1, 1)
> > > > >
> > > > > Volker
> > > > >
> > > > >
> > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > Newsbeitrag
> > > > > news:uW5BD$EuDHA.2260@TK2MSFTNGP09.phx.gbl...
> > > > > > 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" <volker.hormuth@t-online.de> wrote in message
> > > > > > news:OA2oA1EuDHA.1872@TK2MSFTNGP09.phx.gbl...
> > > > > > > Hallo Bob,
> > > > > > >
> > > > > > > first I got error 91, after replacing the lines error 1004.
> > > > > > > The code is not running.
> > > > > > >
> > > > > > > mfg
> > > > > > > Volker
> > > > > > >
> > > > > > >
> > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im
> > > > > Newsbeitrag
> > > > > > > news:%236WSOWEuDHA.4060@TK2MSFTNGP11.phx.gbl...
> > > > > > > > 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" <volker.hormuth@t-online.de> wrote in
message
> > > > > > > > news:OCS6iLEuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > > > > > > 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" <bob.phillips@notheretiscali.co.uk> schrieb
> im
> > > > > > > Newsbeitrag
> > > > > > > > > news:uRBNVvDuDHA.1224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > 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" <volker.hormuth@t-online.de> wrote in
> > message
> > > > > > > > > > news:%239%23CjZDuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > > > > > > > > > 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
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de> schrieb
im
> > > > > > Newsbeitrag
> > > > > > > > > > > news:uku7oB5tDHA.2088@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > Hallo Bob,
> > > > > > > > > > > >
> > > > > > > > > > > > this was the solution.
> > > > > > > > > > > > The second code I will test tomorrow.
> > > > > > > > > > > > Thanks for your help.
> > > > > > > > > > > >
> > > > > > > > > > > > Volker
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk>
> > schrieb
> > > > im
> > > > > > > > > > Newsbeitrag
> > > > > > > > > > > > news:%2379BWq3tDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > 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)
> > > > > > > > > > > > >
> > > > > > > > > > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk>
> > wrote
> > > > in
> > > > > > > > message
> > > > > > > > > > > > > news:u9iYBm3tDHA.2148@TK2MSFTNGP12.phx.gbl...
> > > > > > > > > > > > > > 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)
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > "Volker Hormuth" <volker.hormuth@t-online.de>
> wrote
> > in
> > > > > > message
> > > > > > > > > > > > > > news:OVPZsE3tDHA.556@TK2MSFTNGP11.phx.gbl...
> > > > > > > > > > > > > > > Hallo Bob,
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > I got a mistake 1004 at thi
 line:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >                     ws.Range(Cells(iRow2, 1),
> > > > > Cells(iRow2,
> > > > > > > > > > 5)).Copy
> > > > > > > > > > > > > > > Destination:=Sheets("Master").Cells(iRow1, 1)
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > What can I change ?
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Volker
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > "Bob Phillips"
> <bob.phillips@notheretiscali.co.uk>
> > > > > schrieb
> > > > > > > im
> > > > > > > > > > > > > Newsbeitrag
> > > > > > > > > > > > > > > news:O6WiAy2tDHA.536@tk2msftngp13.phx.gbl...
> > > > > > > > > > > > > > > > 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)
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > "Volker Hormuth"
<volker.hormuth@t-online.de>
> > > wrote
> > > > in
> > > > > > > > message
> > > > > > > > > > > > > > > >
> news:%23lLHkk2tDHA.3416@tk2msftngp13.phx.gbl...
> > > > > > > > > > > > > > > > > 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"
> > > <bob.phillips@notheretiscali.co.uk>
> > > > > > > schrieb
> > > > > > > > > im
> > > > > > > > > > > > > > > Newsbeitrag
> > > > > > > > > > > > > > > > >
> > news:%23aZ7Gl0tDHA.1876@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > > > > > > 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"
> > <volker.hormuth@t-online.de>
> > > > > wrote
> > > > > > in
> > > > > > > > > > message
> > > > > > > > > > > > > > > > > >
> > > news:uw%234PHztDHA.2224@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > > > > > > > > > > > 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
> > > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
12/4/2003 8:22:03 PM
Reply:

Similar Artilces:

#Delete Mark in Bound Memo filed
I have form that has bound memo field, sometime, no sure how it happen, the memo filed is filled with #Delete. My application is a stand alone program. Kindly advise what can cause this and how to avoid it from happening. -- TS Lim When was the last time you performed a compact and repair? Is you db split? Does each user have their own copy of the front-end? Please checkout http://www.granite.ab.ca/access/corruptmdbs.htm http://www.granite.ab.ca/access/corruption/symptoms.htm http://office.microsoft.com/en-ca/access/HA011865661033.aspx - No very helpful but directly from MS ...

CSV File Problem
Can any one explain why the following problem occurs please? A colleague has a small list of data which is a csv file exported from SQL. Column One is a serial number from 1 to 29. Column 2 is a number of transactions which have occurred.(it should look like example A below in Excel) 1 475 1,475 2 1732 21,732 3 1670 31,670 4 2176 42,176 5 608 5,608 6 1579 61,579 7 43101 743,101 8 54512 854,512 9 51258 951,258 10 61050 1,061,050 A B If I double click on the csv file, it opens in Excel but c...

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

CFile (delete file)
How can I delete all files that end with ".temp" in some folder? CFile::Remove remove unlink -- cheers, Alok Gupta Blogs: http://wdevs.com/thatsalok "Petar Popara" <my.fake@mail.net> wrote in message news:Op6#URKfFHA.2644@TK2MSFTNGP09.phx.gbl... > > How can I delete all files that end with ".temp" in some folder? > > SHFileOperation() will and it supports wildcards! DeleteFile() will operate on one file at a time. "Petar Popara" <my.fake@mail.net> wrote in message news:Op6%23URKfFHA.2644@TK2MSFTNGP09.phx.gbl... > >...

Opening an *.MSG file with it's respective attachment.
Hello, The file *.MSG is a message file. This can be viewed by any editor, like Notepad. However, when there's an attachment to it, we see garbage. Is there a way to open the attachments present inside the *.MSG files ? Thank you Yes, by opening it with Outlook. -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Create your own fully customized Toolbar -Creating a Classic View in Outlook 2003 Subscribe to the newsletter to receive news and tips & tricks in your mailbox!...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

import a 123 file
My excel version cannot open a Lotus 123 file. Is there an import feature to move the data from Lotus 123 to Excel? Thanks No, you need to convert the file either from Lotus 123 or get commercial converter -- Regards, Peo Sjoblom "Frank" <Frank@discussions.microsoft.com> wrote in message news:59B55F98-4C79-4917-928B-F1B309660730@microsoft.com... > My excel version cannot open a Lotus 123 file. Is there an import feature > to > move the data from Lotus 123 to Excel? > Thanks > > ...

Where is the cache files folder located for windows media player?
Does windows media player have a cache files folder? Where is the cache files folder located for windows media player? Or how do I find the cache files folder for windows media player? Thank You for your Help. Bob Background info: I have a desktop AMD 4400+ 64X2 4GB memory 320GB HD(way bigger than I need) Acer computer (AM3100-U3201A) I purchased new February 2008 running 32-bit MS Vista Home Premium SP2 box. I use MS auto updates. I think it has onboard shared memory for video and sound. My ISP is Comcast 12mbps with Motorola SURFboard SB5101 Cable Modem direct to...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Public Folder link to Excel File
Hi - I have a user that claims that he had a shortcut to a spreadsheet "in his public folders" at his old job. Obviously, he's an end user that may or may not know what he's talking about.... I'm trying to figure out the best way to accomodate him. He basically wants an Excel file stored on a shared folder to be available to all user as either a shortcut, or a link in the public folders (can't post to a folder as he will be updating the sheet regularly). When a user clicks on the shortcut/link, it would just launch Excel and display the file. Anybody have any ideas...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

Multiple copies of email in "Forward to"contact
HIe, I am running Exchnage 2003. My CEO has a conatct in our organisation that refer to his blackberry contact. I have set the forwarding to also keep a copy in his mailbox. What happens is that three copies of his forwarded email hit his blackberry account! Only one hits his internal mailbox. What I ahve determined is that the front end Exchange server seems to be creating the additional copies. Is this how its meant to be (don think so cos its such a nuisance and my CEO keeps complaining about it). How do I tell the front end server that this guy's mail server is the one that...

Renaming Partial File Names
Is it possible to rename part of a file name? (almost like a find and replace) My database pulls in the excel file names from a directory, with it's subfolders and contents as well. Every file begins with "Kay Form". Kay no longer works in our department, and I'd like to run a loop that replaces "Kay Form" with "Featured Track". I don't know how to do a partial replace though. Thank you in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1 Checkout the Replace function. I...

Opening pub files created with older Publisher versions #2
I have just upgraded to Publisher 2003 from 2000 and am having trouble with pub files sent to me for our chuch newsletter which is using Publisher 97 The text is not wrapping around graphics boxes. Can I fix this? I really don't want to go back to Pub 200 Thank Richard this is caused by Publisher 97 not been printer independent. Even if you went back to Publisher 2000, unless you have the identical font versions and printer driver you would have issues with formatting. It sounds like the person sending you the file has a garbage HP inkjet printer. Get them to install the HP5P laser p...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

unknown .edb file
I have error messages in the log files that references a corrupt database named C:\Program Files\exchsrvr\Exchangeserver_Outlook.edb. "Outlook" is the name of the server. The problem is that this database is not listed in any of the Mail Stores or Public stores. I want to run the repair utilities on it, but since I'm not sure what it is used for, I don't know how I can restore it if the repair goes bad. Does anyone know what this is used for or how I can determine that myself? BTW, the database always has a current time stamp, so I assume that it is used by something....

Copying dbx files from a CDR back into Outlook
My computer was recently hijakced and I had to reinstall everything. I did however save all of my folders in Outlook onto a CD Rom and I do not know how to transfer them back so I can use them. Also I had an animated version of outlook where you could choose loads of diffent stationery and I cannot seem to find that upgrade anaywhere. Can you help me? Tee <tsahai33@hotmail.com> wrote: > My computer was recently hijakced and I had to reinstall > everything. I did however save all of my folders in > Outlook onto a CD Rom and I do not know how to transfer > them back so...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

printing imported gif files in Excel
When printing gif file with transparent background on the grey(25%) background of Excel non-transparent part of the picture has white border around it. Is there any way to prevent it? Thank you in advance... Tim ...

DB_Backup.MDB file has appeared beside DB.MDB.
I have a local DB.MDB which accesses a DATA.MDB on a network drive. I use a user-level security file. It has been working AOK for a year or so. Yesterday one of the users reported an error dialog when attempting to open the DB.MDB (unfortunately the wording of the dialog was not captured!) After this 'event', the database started working correctly once again. The result was a DB_Backup.MDB file appeared beside the DB.MDB. Any ideas? That backup file is created when ACCESS "crashes" during use..... -- Ken Snell <MS ACCESS MVP> "Andrew (UGL)&quo...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Archiving Outlook.pst files to Novell Netware file server
Hi all We are trying to perform a manual Outlook inbox archive which we are tryingto save to a Novell file server - but it dowsn't work. Any ideas why? (works fine on the MS2000 server but we don't have the space for everyone) We are currently running Outlook 2003 and the Novell file server is 4.8.3 Any help would be appreciated Thanks!! kwan <kevin.kelly@levittbernstein.co.uk> wrote: > We are trying to perform a manual Outlook inbox archive which we are > tryingto save to a Novell file server - but it dowsn't work. Any ideas > why? (works fine on the MS2000 s...