Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PasteSpecial class failed. (Excel 97)

    Look at this code and let me know what is going on. I keep getting a "PasteSpecial" class failed.

    Sub Process_sheet(f_name As Variant, f_count As Integer)

    Dim Page_title As String
    Dim Win_name As String
    Dim Copy_name As String
    Dim New_name As String

    Page_title = "Budget (" + CStr(f_count - 1) + ")"
    Application.StatusBar = "Adding: " + f_name
    Workbooks.Open Filename:=f_name
    ' Store off what windows is calling it.
    Win_name = ActiveWorkbook.Name
    ' Make it the active window
    Windows(Win_name).Activate
    Sheets("Budget").Select
    ' Added 11/05/2001 sds
    Range("A1:AC133").Select
    Range("A1:AC133").Copy

    ' Here are going to copy the contents to memory
    Sheets("Budget").Copy Before:=Workbooks("Rollup.XLS").Sheets(HoldSheet)
    ' Copy Absolutes...sds
    Windows("Rollup.XLS").Activate
    ActiveWorkbook.Unprotect
    Range("A1:AC133").PasteSpecial

    HoldSheet = HoldSheet + 1
    Windows("Rollup.XLS").Activate
    Sheets("Budget").Name = Page_title
    ' Clean Up
    Application.DisplayAlerts = False
    ' Sheets(New_name).Delete
    ' Close the data file....
    Windows(Win_name).Activate
    Windows(Win_name).Close
    Application.DisplayAlerts = True
    Windows(Holdbook).Activate

    End Sub

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: PasteSpecial class failed. (Excel 97)

    Hi Daniel,
    I believe that your copying of the Budget sheet is clearing the Range("A1:AC133") from the clipboard. Therefore when you try to pastespecial, there either isn't actually anything to paste, or the only thing on the clipboard is the Budget worksheet, which you can't paste to a Range.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial class failed. (Excel 97)

    Not sure I follow very clearly. Could you show me how to correct this?

    Also, at the end you say I can't paste to a Range. => Why am I not able to paste to a Range?

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: PasteSpecial class failed. (Excel 97)

    Daniel

    OK what is in the clipboard after you execute this line:

    ' Here are going to copy the contents to memory
    Sheets("Budget").Copy Before:=Workbooks("Rollup.XLS").Sheets(HoldSheet)

    Nothing!!! What you copied in

    ' Added 11/05/2001 sds
    Range("A1:AC133").Select
    Range("A1:AC133").Copy

    is gone from the clipboard and now you have nothing to paste.

    I suggest you re-write your code so that the copy/paste kinda go back to back.

    Plus if you do not mind me giving you a couple of tips to enhance your code:

    1) I prefer using the & to concatinate strings in VBA. The + works OK but I reserve it for adding numbers. You do not need the type conversion, because you are ending into a string variable. Its nice that you thought about it, but its not needed.

    2) Instead of holding the name of the opened workbook like in:
    ' Store off what windows is calling it.
    Win_name = ActiveWorkbook.Name

    How about setting a reference to it like Set wbBudget = ActiveWorkbook then you can use wbBudget to work with that workbook like in wbBudget.Save.

    3) You have already opened the workbook so it is the active one at that time, you do not need these lines:
    ' Make it the active window
    Windows(Win_name).Activate Plus you could have used wbBudget.Activate if you needed to, but its the active workbook. I don't like using Active??? because the User can change it. I would rather set reference to the object I want to work with, this way the User can do what ever and it would still paste where it should.

    4) You don't really need to select an object to work with that object. For example:
    ' Added 11/05/2001 sds
    Range("A1:AC133").Select
    Range("A1:AC133").Copy

    could simply be written like this Range("A1:AC133").Copy.

    I hope I did not turn you off, I was just trying to help.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial class failed. (Excel 97)

    I am not turned off as you say. In fact I am very happy with it. Your comments help me learn more about what I am trying to learn with VBA. Your comments and remarks are most welcomed.

    Thank you

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: PasteSpecial class failed. (Excel 97)

    Daniel

    As Rory and I said, once you copy the worksheet, you lose the range that you copied and the clipboard is emptied. Look at the Paste button or menu item, it would be grayed out, and that is because there is nothing to paste.

    You need to rearrange your code from being logical to being what I call VBA-centric.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: PasteSpecial class failed. (Excel 97)

    I think Wassim has covered pretty much everything but I have one quick question: are you using copy and paste so that you get all the formatting etc.? If so, that's fine but if not, it would be easier and faster to just set one range equal to the other.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial class failed. (Excel 97)

    Yes, I'm trying to save the integerity of the worksheet. Please understand
    that someone else didi the spreadsheets, with locked cells and sheets and
    then I am to put them all together and roll them up....

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial class failed. (Excel 97)

    Guess this is not what I wanted. Turns out I need the Value and not the links copied.

Posting Permissions

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