Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Copy and Paste Problem (Excel 2002/2003)

    HI

    I have attached a macro that is giving me a problem, if I run this macro it groups worksheets( Chemicals and Installation Request) Unprotects them copies them to a new workbook and pastes the data, and objects namely form controls

    The problem arises, I think because in the Chemicals there is an on change event, which tries to run and come to a halt. It also leave the main workbook with the Chemicals and Installation Request still grouped

    What I really need is just the Values and Formats, absolutely no objects and any connections to the original sheets.

    Any Ideas Please

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy and Paste Problem (Excel 2002/2003)

    Try this:

    Sub Copy_1()
    Dim wbS As Workbook
    Dim wbT As Workbook
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set wbS = ActiveWorkbook
    Set wbT = Workbooks.Add(xlWBATWorksheet)
    CopySheet wbS.Worksheets("Chemicals"), wbT.Worksheets(1)
    CopySheet wbS.Worksheets("Installation Request"), wbT.Worksheets.Add
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

    Sub CopySheet(wsS As Worksheet, wsT As Worksheet)
    wsS.Unprotect Password:="wahsdarb"
    wsT.Name = wsS.Name
    wsS.UsedRange.Copy
    wsT.Range("A1").PasteSpecial xlPasteFormats
    wsT.Range("A1").PasteSpecial xlPasteValues
    wsT.Range("A1").PasteSpecial xlPasteColumnWidths
    wsT.Range("A1").Select
    wsS.Protect Password:="wahsdarb"
    Application.CutCopyMode = False
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Copy and Paste Problem (Excel 2002/2003)

    Hi Hans

    It copied the chemicals worksheet OK although there was no data the sheet was blank.

    Please See Attached.

    it shows where the Macro Stopped.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy and Paste Problem (Excel 2002/2003)

    Did you get an error message? If so, what did it say?

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Copy and Paste Problem (Excel 2002/2003)

    Hi Hans

    I know you are going to berate me, because it was the old merged cells problem, I know I know, you have warned me before.

    So I beg your humble forgiveness <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Copy and Paste Problem (Excel 2002/2003)

    Hi Hans

    You kindly provided me with this code, to wich I have made a few additions, however instead copy and pasting the whole sheet (Installation Request ) I would just like the range A1:AS116 copied

    but I can't seem to get the code correct, code is attached

    Many Thanks

    Bradddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy and Paste Problem (Excel 2002/2003)

    See attached version.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Copy and Paste Problem (Excel 2002/2003)

    Hi Hans

    Thanks for the prompt reply but I get an error please see attached, Object doesn't support this property or method.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy and Paste Problem (Excel 2002/2003)

    Sorry, I should have seen that. You'll have to unprotect the worksheet, clear the cells, then protect it again. Try this:

    Sub Copy_1()
    Dim wbS As Workbook
    Dim wbT As Workbook
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set wbS = ActiveWorkbook
    Set wbT = Workbooks.Add(xlWBATWorksheet)
    CopySheet wbS.Worksheets("Chemicals"), wbT.Worksheets(1)
    CopySheet wbS.Worksheets("Installation Request"), wbT.Worksheets.Add, "A1:AS116"
    With wbT.Worksheets("Chemicals")
    .Unprotect Password:="wahsdarb"
    .Range("AU1:IV2000").Clear
    .Protect Password:="wahsdarb"
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Copy and Paste Problem (Excel 2002/2003)

    Hi Hans

    No apology necessary, that works just fine now.


    Once again many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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