Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying sheets to a new workbook (200)

    All

    Please find below some code which was written by somebody else and in the main works apart from the 2 sections anotated with comments.
    Can anybody please advise on how best to rectify the anotated problems.

    The object of the code is that the user enters selected sheet names from the current workbook into a column within the the FireMacro sheet and then executes the macro to create a fresh workbook containing the selected sheets

    Thanks

    Alex
    ____________________________________________
    Public sheetnum As String
    Public n As Integer
    Public m As Integer
    Public Thisbudgetworkbook As String

    Sub CopySheetToNewWorkBook()

    Dim workbooktemp As String
    Dim temp As String

    Thisbudgetworkbook = ActiveWorkbook.Name
    Workbooks(Thisbudgetworkbook).Worksheets("Fire macros").Range("d8").Select
    Selection.CurrentRegion.Select
    '***The above line will only work with more than one item in the column. I have tried to create an if statement for the one item senario but without success.****
    Call Initialtemp1
    End Sub

    Private Sub Initialtemp1()

    Dim arrSheets() 'variable to hold the sheet numbers

    arrSheets() = Application.Transpose(Selection)
    '**Sometimes an eroneous sheet is copied into the new workbook, mainly instead of one of those requested.
    '**This seems to happen when the macro has been run a few times and I guess that this variable is not being properly intialised.
    '**However so far I have not been able to write the statement to properly initialise the variable.
    Sheets(arrSheets()).Copy
    ActiveWorkbook.SaveAs Filename:="Documents and SettingsMy DocumentsTestSheetCatcher.xls"
    n = ActiveWorkbook.Sheets.Count
    m = 1
    Do While m < n + 1
    Sheets(m).Activate
    Rows("8:17").Select
    ActiveSheet.Unprotect ("ProtectTheInocent")
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    SendKeys "{home}"
    ActiveSheet.Protect Password:="ProtectTheInocent"
    m = m + 1
    Loop

    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying sheets to a new workbook (200)

    If the object is as you describe, does it have to be done by code? why not
    Select (using shift and/or ctrl) the desired sheets in the sheet tabs
    Right click - move or copy
    Under "To book" select "New Book"
    Check "Create a copy"
    <ok>

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying sheets to a new workbook (200)

    Steve

    The budget workbook contains many sheets in respect of budget and project workstreams, with different sheets being allocated to different project managers, team leaders, senior managers, etc, etc.

    Even though it does not work 100% the current macro saves a phenominal amount of time and is far more accurate, instead of doing the job manually.

    Alex

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying sheets to a new workbook (200)

    Without redoing all the code, try these changes:

    <pre>Dim arrSheets 'variable to hold the sheet numbers
    arrSheets = Application.Transpose(Selection)

    Sheets(arrSheets).Copy</pre>


    Steve

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying sheets to a new workbook (200)

    Thanks very much. This seems to have solved one of the problems. Has anybody got any ideas about the other problem?

    As a note of explanation, my end objective is to create a table headed with recipient names and lists of the various worksheets which each recipient is to receive. I then want to put the existing code in a loop which will:
    Create the copy workbook including those sheets shown in a table column
    Create an e-mail with the copy workbook embedded in it, using the send to recipient as attachment method
    Include in the e-mail the recipient shown at the head of the column
    loop until there are no more recipients in the table.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying sheets to a new workbook (200)

    What is the other problem exactly?

    I got the code to work with the modifications I suggested.

    I could have 1 sheetname in 'Fire macros'!D8

    or a list in (eg) 'Fire macros'!D810

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying sheets to a new workbook (200)

    What is the function of the
    Sendkeys "{home}"

    ? Seems to me it should be omitted. It can lead to unexpected results.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying sheets to a new workbook (200)

    All

    Does it make difference that my worksheets are all numbered: 1, 2, 5.5 etc, with the numbers formatted as text.

    When I only have one item in the list I get a "type missmatch error 13", with "end" being the only option available.

    The home key line can be deleted. I was just trying something out.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying sheets to a new workbook (200)

    If you name the worksheets "1", "2", etc these are text
    If you enter numbers into the cells to grab, you will get the index not the sheet.
    If your sheets are ordered "MainSheet", "SecondSheet", "1", "2", "5.5" (eg) and you enter 1,2 in the cell D8, D9 you will copy "MainSHeet" and "SecondSheet" the #1 and #2 sheet not the sheets named "1", "2" and the number 5.5 will yield a runtime error.

    If you want to use numbers you must have excel convert them to text use something like:

    <pre>Dim arrSheets() 'variable to hold the sheet numbers
    Dim x As Long
    ReDim arrSheets(1 To Selection.Count)
    For x = 1 To Selection.Count
    arrSheets(x) = CStr(Selection.Cells(x))
    Next
    'arrSheets = Application.Transpose(Selection) DELETE
    Sheets(arrSheets).Copy </pre>


    Steve

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying sheets to a new workbook (200)

    All

    I attach a very cut down copy of the current budget file - called 2004 5+7 for your perusal. Unfortunately so as to be within the file size limit, I could only include one of the many workstream sheets. However i have tested that the macro still produces the same failure as previously stated.

    Also, given that I do not understand the current code, I thought I would try rewriting the method with my own code. See below.

    My code fails on the final line. Can somebody please advise on the best method for assigning a worksheet to a variable so that it can later be reassigned to a new workbook. Also how can I create a new workbook with no sheets in it so that I can then add the copied sheets.

    Many thanks for all your help

    Alex
    _________________________________
    Option Explicit
    Type EmailRecord
    Recipient As String
    SheetName(20) As String
    SheetCopied(20) As Worksheets
    End Type

    Public Sub CopySheets()



    Dim Mymail As EmailRecord

    Mymail.Recipient = ThisWorkbook.Worksheets("Sheet1").Range("B3")
    Mymail.SheetName(1) = ThisWorkbook.Worksheets("Sheet1").Range("B4")
    Mymail.SheetCopied(1) = ThisWorkbook.Worksheets(1) '******fails here*****


    End Sub

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying sheets to a new workbook (200)

    Steve the sheet numbers were converted to text by formatting the whole FireMacros sheet to text.

    Please see the attachment to my previous message.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying sheets to a new workbook (200)

    <P ID="edit" class=small>(Edited by sdckapr on 20-Feb-04 14:53. expanded some items)</P>Your code does not have any of the changes I suggested. When I make the changes to your code I get no errors.
    If what you type in "fire macros" will always be text then the changes I suggested in <post#=345603>post 345603</post#> will fix the problem.

    If it could be text or numbers then use the changes the suggested in <post#=345956>post 345956</post#> which will ensure that they are text.
    Steve

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying sheets to a new workbook (200)

    Regarding your last question:
    You cannot create a workbook with no sheets. You can

    Create a workbook (and it will have the default number of sheets) you can delete all but the first, then copy the others to it and then delete the first one.

    Copy the sheets directly to a new workbook (as you did in your original code) without specifying a "before or after sheet"
    <pre>Sheets(arrSheets).Copy </pre>


    Steve

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying sheets to a new workbook (200)

    1) I think you want to define the var type for SheetCopied as a worksheet not as a worksheet collection (worksheets)

    2) You must SET a worksheet object

    <pre>Option Explicit
    Type EmailRecord
    Recipient As String
    SheetName(20) As String
    SheetCopied(20) As Worksheet ' not Worksheet<font color=red>s</font color=red>
    End Type

    Public Sub CopySheets()
    Dim Mymail As EmailRecord

    Mymail.Recipient = ThisWorkbook.Worksheets("Sheet1").Range("B3")
    Mymail.SheetName(1) = ThisWorkbook.Worksheets("Sheet1").Range("B4")
    <font color=red>Set</font color=red> Mymail.SheetCopied(1) = ThisWorkbook.Worksheets(1) </pre>


    I don't see how this has anything to do with the previous code, however.
    Steve

Posting Permissions

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