Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Copy Method of Worksheet Fails

  1. #11
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Plus Nimrod's from Vancouver! A great town, only a few hours from Chilliwack, the green heart of BC (I'm a Rosedale jr. high alum, where I hold the school record for fastest mile (4 minutes, 54 seconds)).

    I think you can copy if you refer to the index number. I assume LastMadeSheet is defined as a worksheet, so you won't need to set this as I have, just use LastMadeSheet.index. The second line is for Mark O'Brien.

    Code:
    Sub cpy()
    'Macro recorded by Nate
    Dim n As Worksheet
    Set n = sheets(LastMadeSheet)
        Sheets(SalesMasterSheet).Copy After:=Sheets(n.Index)
    End Sub
    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-11 12:05 ]

  2. #12
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi NateO !
    Thanks for your suggestion(s). Unfortunately my problem hasn't gone away. If you have any ideas they would be appreciated ... I'm running out of ideas and would rather be outside on this sunny day instead of finishing this project !

    Again thanks for all the help

  3. #13
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Show your code, want to see how you're working with LastSheet.....This methodology will work.

  4. #14
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Code:
    Private Sub CopyMasterSheet()
    
        'THIS IS SO YOU DON'T HAVE POP-UP ALERTS FOR DELETES ETC
        Application.DisplayAlerts = False
        
        '  you try to delete a sheet that does exist it resumes on line just after the errro line
        On Error Resume Next
        
        ' delete the sheet if it exists
        Worksheets(SheetInfo.Label).Delete
       
        'copy
        'Worksheets(SalesMasterSheet).Copy after:=Worksheets(SetUpSheetName)
        Worksheets(SalesMasterSheet).Copy After:=Worksheets(LastMadeSheet)
        
        'rename
        ActiveSheet.Name = SheetInfo.Label
        LastMadeSheet = SheetInfo.Label
    
    Application.DisplayAlerts = True
    
    End Sub
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  5. #15
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You're using global variables (I hope), that I can't see. Otherwise you need to refer to sheet names with quotes, i.e., sheets("sheet1"). But I'll guess. I haven't tested this, but give her a whirl:

    Code:
    Private Sub CopyMasterSheet()
    Dim n As Worksheet
    Set n = Worksheets(LastMadeSheet)
        'THIS IS SO YOU DON'T HAVE POP-UP ALERTS FOR DELETES ETC
        Application.DisplayAlerts = False
        
        '  you try to delete a sheet that does exist it resumes on line just after the errro line
        On Error Resume Next
        
        ' delete the sheet if it exists
        Worksheets(SheetInfo.Label).Delete
       
        'copy
        'Worksheets(SalesMasterSheet).Copy after:=Worksheets(SetUpSheetName)
        Worksheets(SalesMasterSheet).Copy After:=Sheets(n.Index)
        
        'rename
        ActiveSheet.Name = SheetInfo.Label
        LastMadeSheet = SheetInfo.Label
    
    Application.DisplayAlerts = True
    
    End Sub
    Also, I think
    LastMadeSheet = SheetInfo.Label
    is going to fail, if LastMadeSheet is a variable, I think you'll need to set it, like line 3 above.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-11 12:45 ]

  6. #16
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The workaround I gave was for Xl97
    Have a look here for the workaround in xl2K

    http://support.microsoft.com/search/...;en-us;Q210684

    Kind Regards,
    Ivan F Moala From the City of Sails

  7. #17
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan, NateO:
    Thanks for all the help. Yes it seems to be a limitation of Excel when copiing sheets that have links. I've taken the source sheet and placed it in new workbook with the following code:
    Code:
    For cnt = 1 To 100
        
        Sheets("MASTER").Select
        Sheets("MASTER").Copy After:=Sheets(3)
    next
    Sure enough even with this minimal code in the workbook the copy process always stops at loop 46!
    And as the document Ivan has pointed me to says, the only way to copy more is to close the workbook.
    So I guess I'm going to have to overhaul my program. Again thanks for all the help you've given me.


  8. #18
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You actually don't need to need to select the sheets. I got this to loop through 100, (although it is xl2000).

    Code:
    For cnt = 1 To 100
        Sheets("MASTER").Copy After:=Sheets(3)
    next
    Might be worth a shot.


    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-11 17:44 ]

  9. #19
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



    [ This Message was edited by: TsTom on 2002-05-11 22:34 ]

  10. #20
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The other one is wrong...
    This worked for 524 sheets copied.
    Total of 527 with the default three.
    I stopped it after that.
    I thought you could only have 256 sheets in one workbook?



    Public Declare Function GetActiveWindow Lib _
    "user32" () As Long
    Public Declare Function OpenClipboard Lib _
    "user32" (ByVal hwnd As Long) As Long
    Public Declare Function EmptyClipboard Lib _
    "user32" () As Long
    Public Declare Function CloseClipboard Lib _
    "user32" () As Long

    Sub TryThis()
    Dim cnt
    For cnt = 1 To 1000
    Sheets("Sheet1").Copy After:=Sheets(3)
    Call OpenClipboard(GetActiveWindow())
    Call EmptyClipboard
    Call CloseClipboard
    Next
    End Sub


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
  •