Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Copy sheets from one workbook to another

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    England
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all,
    I am trying to copy some sheets from one workbook into another. It works if I specify a destination workbook name but the name of the destination workbook will change depending on the week of the year. This name is stored in a variable. When i use the variable in the copy the VBA code falls over.

    The code I am using is similar to this :-

    Sheets(Array("Sheet1","Sheet2")).Copy Before:=Workbooks("Workbook2").Sheets (3)

    What I want to be able to do is replace workbook to with a variable. Something like:-

    WrkBook2="Week 2"

    Sheets(Array("Sheet1","Sheet2")).Copy Before:=Workbooks(Wrkbook2).Sheets (3)

    Thanks for any help
    Alan


  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Define your string with the .xls extension.

    Sub tester()

    Dim Wrkbook2 As String

    Wrkbook2 = "Week 2.xls"
    Sheets(Array("Sheet1", "Sheet2")).Copy Before:=Workbooks(Wrkbook2).Sheets(3)

    End Sub

    worked for me.

    If you are going to prompt for the name of the destination workbook by using .GetOpenFilename or similar, you will have to convert the string by removing the full path before continuing. Disregard this if not an issue.

    Bye,
    Jay

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    England
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for that. It was a bit of user error on my part Doh!. I wonderedwhy it wouldn't work as I had also dimensioned a string for the workbook name.

    Regards,
    Alan

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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