+ Reply to Thread
Results 1 to 5 of 5

Create new workbook, temporarily name it and copy sheets to it

  1. #1
    DerbyJim
    Guest

    Create new workbook, temporarily name it and copy sheets to it

    My VBA is basic to say the least however I am learning from my mistakes
    pretty quick! :o)

    I'm trying to copy some sheets from an existing workbook to a new workbook
    in their entirety i.e. page breaks, colours etc.

    The error occurs as I do not know how to rename the newly opened workbook
    and it comes unstuck when it refers back to Book1 or Book2 or Book3 etc.

    I've looked at previous questions on this topic and am a little confused so
    would appreciate some clarity...

    Below is an extract from the macro if it helps

    Many thanks in advance

    Jim


    Workbooks.Add
    Windows.CompareSideBySideWith "EXISTING NAMED WORKBOOK.xls"
    Windows("EXISTING NAMED WORKBOOK.xls").Activate
    Sheets("Existing Data").Select
    Sheets("Existing Data").Copy After:=Workbooks("Book2").Sheets(3)
    Windows("EXISTING NAMED WORKBOOK.xls").Activate
    Sheets("4. All Sales Execs Report").Select
    Sheets("4. All Sales Execs Report").Copy
    Before:=Workbooks("Book2").Sheets(4)
    Windows("EXISTING NAMED WORKBOOK.xls").Activate
    Sheets("3. Sales Exec Report").Select
    Sheets("3. Sales Exec Report").Copy Before:=Workbooks("Book2").Sheets(4)
    Windows("EXISTING NAMED WORKBOOK.xls").Activate


  2. #2
    Norman Jones
    Guest

    Re: Create new workbook, temporarily name it and copy sheets to it

    Hi Jim,

    Try:

    '=============>>
    Public Sub Tester()
    Dim WB1 As Workbook
    Dim WB2 As Workbook

    Set WB1 = Workbooks("EXISTING NAMED WORKBOOK.xls")
    Set WB2 = Workbooks.Add

    WB1.Sheets("Existing Data").Copy _
    After:=WB2.Sheets(3)
    WB1.Sheets("4. All Sales Execs Report").Copy _
    Before:=WB2.Sheets(4)
    WB1.Sheets("3. Sales Exec Report").Copy _
    Before:=WB2.Sheets(4)
    End Sub
    '<<=============

    ---
    Regards,
    Norman


    "DerbyJim" <[email protected]> wrote in message
    news:[email protected]...
    > My VBA is basic to say the least however I am learning from my mistakes
    > pretty quick! :o)
    >
    > I'm trying to copy some sheets from an existing workbook to a new workbook
    > in their entirety i.e. page breaks, colours etc.
    >
    > The error occurs as I do not know how to rename the newly opened workbook
    > and it comes unstuck when it refers back to Book1 or Book2 or Book3 etc.
    >
    > I've looked at previous questions on this topic and am a little confused
    > so
    > would appreciate some clarity...
    >
    > Below is an extract from the macro if it helps
    >
    > Many thanks in advance
    >
    > Jim
    >
    >
    > Workbooks.Add
    > Windows.CompareSideBySideWith "EXISTING NAMED WORKBOOK.xls"
    > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > Sheets("Existing Data").Select
    > Sheets("Existing Data").Copy After:=Workbooks("Book2").Sheets(3)
    > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > Sheets("4. All Sales Execs Report").Select
    > Sheets("4. All Sales Execs Report").Copy
    > Before:=Workbooks("Book2").Sheets(4)
    > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > Sheets("3. Sales Exec Report").Select
    > Sheets("3. Sales Exec Report").Copy
    > Before:=Workbooks("Book2").Sheets(4)
    > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    >




  3. #3
    DerbyJim
    Guest

    Re: Create new workbook, temporarily name it and copy sheets to it

    Thanks Norman.

    I tried what you said and it actually made some sense to me... however I am
    now getting an Expected End Sub error, is it something to do with the Public
    Sub tester?

    The full code is as follows:

    Sub Run_Extract()
    '
    ' Run_Extract Macro
    ' Output an extract based on current information
    '

    '

    Public Sub Tester()
    Dim WB1 As Workbook
    Dim WB2 As Workbook

    Set WB1 = Workbooks("FS Complaints Report - All.xls")
    Set WB2 = Workbooks.Add

    WB1.Sheets("XXXX Extract").Copy _
    After:=WB2.Sheets(3)
    WB1.Sheets("4. All Sales Execs Report").Copy _
    Before:=WB2.Sheets(4)
    WB1.Sheets("3. Sales Exec Report").Copy _
    Before:=WB2.Sheets(4)
    WB1.Sheets("2. Venue Report").Copy _
    Before:=WB2.Sheets(4)
    WB1.Sheets("0. Summary Report").Copy _
    Before:=WB2.Sheets(4)

    End Sub



    "Norman Jones" wrote:

    > Hi Jim,
    >
    > Try:
    >
    > '=============>>
    > Public Sub Tester()
    > Dim WB1 As Workbook
    > Dim WB2 As Workbook
    >
    > Set WB1 = Workbooks("EXISTING NAMED WORKBOOK.xls")
    > Set WB2 = Workbooks.Add
    >
    > WB1.Sheets("Existing Data").Copy _
    > After:=WB2.Sheets(3)
    > WB1.Sheets("4. All Sales Execs Report").Copy _
    > Before:=WB2.Sheets(4)
    > WB1.Sheets("3. Sales Exec Report").Copy _
    > Before:=WB2.Sheets(4)
    > End Sub
    > '<<=============
    >
    > ---
    > Regards,
    > Norman
    >
    >
    > "DerbyJim" <[email protected]> wrote in message
    > news:[email protected]...
    > > My VBA is basic to say the least however I am learning from my mistakes
    > > pretty quick! :o)
    > >
    > > I'm trying to copy some sheets from an existing workbook to a new workbook
    > > in their entirety i.e. page breaks, colours etc.
    > >
    > > The error occurs as I do not know how to rename the newly opened workbook
    > > and it comes unstuck when it refers back to Book1 or Book2 or Book3 etc.
    > >
    > > I've looked at previous questions on this topic and am a little confused
    > > so
    > > would appreciate some clarity...
    > >
    > > Below is an extract from the macro if it helps
    > >
    > > Many thanks in advance
    > >
    > > Jim
    > >
    > >
    > > Workbooks.Add
    > > Windows.CompareSideBySideWith "EXISTING NAMED WORKBOOK.xls"
    > > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > > Sheets("Existing Data").Select
    > > Sheets("Existing Data").Copy After:=Workbooks("Book2").Sheets(3)
    > > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > > Sheets("4. All Sales Execs Report").Select
    > > Sheets("4. All Sales Execs Report").Copy
    > > Before:=Workbooks("Book2").Sheets(4)
    > > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > > Sheets("3. Sales Exec Report").Select
    > > Sheets("3. Sales Exec Report").Copy
    > > Before:=Workbooks("Book2").Sheets(4)
    > > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > >

    >
    >
    >


  4. #4
    Norman Jones
    Guest

    Re: Create new workbook, temporarily name it and copy sheets to it

    Hi Jim,

    Delete all the lines before:

    > Public Sub Tester()


    However, by all means change the name of the sub from Tester to a a name of
    your choice.


    ---
    Regards,
    Norman



    "DerbyJim" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks Norman.
    >
    > I tried what you said and it actually made some sense to me... however I
    > am
    > now getting an Expected End Sub error, is it something to do with the
    > Public
    > Sub tester?
    >
    > The full code is as follows:
    >
    > Sub Run_Extract()
    > '
    > ' Run_Extract Macro
    > ' Output an extract based on current information
    > '
    >
    > '
    >
    > Public Sub Tester()
    > Dim WB1 As Workbook
    > Dim WB2 As Workbook
    >
    > Set WB1 = Workbooks("FS Complaints Report - All.xls")
    > Set WB2 = Workbooks.Add
    >
    > WB1.Sheets("XXXX Extract").Copy _
    > After:=WB2.Sheets(3)
    > WB1.Sheets("4. All Sales Execs Report").Copy _
    > Before:=WB2.Sheets(4)
    > WB1.Sheets("3. Sales Exec Report").Copy _
    > Before:=WB2.Sheets(4)
    > WB1.Sheets("2. Venue Report").Copy _
    > Before:=WB2.Sheets(4)
    > WB1.Sheets("0. Summary Report").Copy _
    > Before:=WB2.Sheets(4)
    >
    > End Sub
    >
    >
    >
    > "Norman Jones" wrote:
    >
    >> Hi Jim,
    >>
    >> Try:
    >>
    >> '=============>>
    >> Public Sub Tester()
    >> Dim WB1 As Workbook
    >> Dim WB2 As Workbook
    >>
    >> Set WB1 = Workbooks("EXISTING NAMED WORKBOOK.xls")
    >> Set WB2 = Workbooks.Add
    >>
    >> WB1.Sheets("Existing Data").Copy _
    >> After:=WB2.Sheets(3)
    >> WB1.Sheets("4. All Sales Execs Report").Copy _
    >> Before:=WB2.Sheets(4)
    >> WB1.Sheets("3. Sales Exec Report").Copy _
    >> Before:=WB2.Sheets(4)
    >> End Sub
    >> '<<=============
    >>
    >> ---
    >> Regards,
    >> Norman
    >>
    >>
    >> "DerbyJim" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > My VBA is basic to say the least however I am learning from my mistakes
    >> > pretty quick! :o)
    >> >
    >> > I'm trying to copy some sheets from an existing workbook to a new
    >> > workbook
    >> > in their entirety i.e. page breaks, colours etc.
    >> >
    >> > The error occurs as I do not know how to rename the newly opened
    >> > workbook
    >> > and it comes unstuck when it refers back to Book1 or Book2 or Book3
    >> > etc.
    >> >
    >> > I've looked at previous questions on this topic and am a little
    >> > confused
    >> > so
    >> > would appreciate some clarity...
    >> >
    >> > Below is an extract from the macro if it helps
    >> >
    >> > Many thanks in advance
    >> >
    >> > Jim
    >> >
    >> >
    >> > Workbooks.Add
    >> > Windows.CompareSideBySideWith "EXISTING NAMED WORKBOOK.xls"
    >> > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    >> > Sheets("Existing Data").Select
    >> > Sheets("Existing Data").Copy After:=Workbooks("Book2").Sheets(3)
    >> > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    >> > Sheets("4. All Sales Execs Report").Select
    >> > Sheets("4. All Sales Execs Report").Copy
    >> > Before:=Workbooks("Book2").Sheets(4)
    >> > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    >> > Sheets("3. Sales Exec Report").Select
    >> > Sheets("3. Sales Exec Report").Copy
    >> > Before:=Workbooks("Book2").Sheets(4)
    >> > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    >> >

    >>
    >>
    >>




  5. #5
    DerbyJim
    Guest

    Re: Create new workbook, temporarily name it and copy sheets to it

    Now working, thanks Norman!!


    "Norman Jones" wrote:

    > Hi Jim,
    >
    > Delete all the lines before:
    >
    > > Public Sub Tester()

    >
    > However, by all means change the name of the sub from Tester to a a name of
    > your choice.
    >
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "DerbyJim" <[email protected]> wrote in message
    > news:[email protected]...
    > > Thanks Norman.
    > >
    > > I tried what you said and it actually made some sense to me... however I
    > > am
    > > now getting an Expected End Sub error, is it something to do with the
    > > Public
    > > Sub tester?
    > >
    > > The full code is as follows:
    > >
    > > Sub Run_Extract()
    > > '
    > > ' Run_Extract Macro
    > > ' Output an extract based on current information
    > > '
    > >
    > > '
    > >
    > > Public Sub Tester()
    > > Dim WB1 As Workbook
    > > Dim WB2 As Workbook
    > >
    > > Set WB1 = Workbooks("FS Complaints Report - All.xls")
    > > Set WB2 = Workbooks.Add
    > >
    > > WB1.Sheets("XXXX Extract").Copy _
    > > After:=WB2.Sheets(3)
    > > WB1.Sheets("4. All Sales Execs Report").Copy _
    > > Before:=WB2.Sheets(4)
    > > WB1.Sheets("3. Sales Exec Report").Copy _
    > > Before:=WB2.Sheets(4)
    > > WB1.Sheets("2. Venue Report").Copy _
    > > Before:=WB2.Sheets(4)
    > > WB1.Sheets("0. Summary Report").Copy _
    > > Before:=WB2.Sheets(4)
    > >
    > > End Sub
    > >
    > >
    > >
    > > "Norman Jones" wrote:
    > >
    > >> Hi Jim,
    > >>
    > >> Try:
    > >>
    > >> '=============>>
    > >> Public Sub Tester()
    > >> Dim WB1 As Workbook
    > >> Dim WB2 As Workbook
    > >>
    > >> Set WB1 = Workbooks("EXISTING NAMED WORKBOOK.xls")
    > >> Set WB2 = Workbooks.Add
    > >>
    > >> WB1.Sheets("Existing Data").Copy _
    > >> After:=WB2.Sheets(3)
    > >> WB1.Sheets("4. All Sales Execs Report").Copy _
    > >> Before:=WB2.Sheets(4)
    > >> WB1.Sheets("3. Sales Exec Report").Copy _
    > >> Before:=WB2.Sheets(4)
    > >> End Sub
    > >> '<<=============
    > >>
    > >> ---
    > >> Regards,
    > >> Norman
    > >>
    > >>
    > >> "DerbyJim" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > My VBA is basic to say the least however I am learning from my mistakes
    > >> > pretty quick! :o)
    > >> >
    > >> > I'm trying to copy some sheets from an existing workbook to a new
    > >> > workbook
    > >> > in their entirety i.e. page breaks, colours etc.
    > >> >
    > >> > The error occurs as I do not know how to rename the newly opened
    > >> > workbook
    > >> > and it comes unstuck when it refers back to Book1 or Book2 or Book3
    > >> > etc.
    > >> >
    > >> > I've looked at previous questions on this topic and am a little
    > >> > confused
    > >> > so
    > >> > would appreciate some clarity...
    > >> >
    > >> > Below is an extract from the macro if it helps
    > >> >
    > >> > Many thanks in advance
    > >> >
    > >> > Jim
    > >> >
    > >> >
    > >> > Workbooks.Add
    > >> > Windows.CompareSideBySideWith "EXISTING NAMED WORKBOOK.xls"
    > >> > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > >> > Sheets("Existing Data").Select
    > >> > Sheets("Existing Data").Copy After:=Workbooks("Book2").Sheets(3)
    > >> > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > >> > Sheets("4. All Sales Execs Report").Select
    > >> > Sheets("4. All Sales Execs Report").Copy
    > >> > Before:=Workbooks("Book2").Sheets(4)
    > >> > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > >> > Sheets("3. Sales Exec Report").Select
    > >> > Sheets("3. Sales Exec Report").Copy
    > >> > Before:=Workbooks("Book2").Sheets(4)
    > >> > Windows("EXISTING NAMED WORKBOOK.xls").Activate
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1