Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cells Select/Paste Value (XP; 03)

    Why does macro one fail? While macro two works just fine.

    Sub Macro1 ()
    With Worksheets("Sheet1")
    .Cells.Select
    .Copy
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 'Fails here
    :=False, Transpose:=False
    End With
    End Sub


    Sub Macro2()
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub

    Thanks,
    John

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells Select/Paste Value (XP; 03)

    I got Macro1 to work by changing it to:

    With Worksheets("Sheet1")
    .Cells.Copy
    .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End With
    End Sub

    Sorry I couldn't see the tree through the forest.

    John

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells Select/Paste Value (XP; 03)

    One more request...

    What would be the best approach if I wanted to pastespecial values in three worksheets out of fine in a master workbook and then use code to 'SaveCopyAs'. My intent is to have the thee worksheets only contain values and yet maintain the integrity of the master file.

    Thanks,
    John

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

    Re: Cells Select/Paste Value (XP; 03)

    Do you mean "three worksheets out of five"? Just repeat the above code for each of the three sheets, then use

    ActiveWorkbook.SaveAs Filename:="filename"

    or

    ActiveWorkbook.SaveCopyAs Filename:="filename"
    ActiceWorkbook.Close SaveChanges:=False

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells Select/Paste Value (XP; 03)

    Hans,

    I should have said I have a master workbook containing five sheets. I would like to pastespecial value three out of the five sheets. As an example sheet1 would be pastespecial valued back on to itself and so forth. Then 'SaveCopyAs".

    I have a routine that creates numerous Excel files from the master workbook. Your suggestion of closing the workbook would not work well under this senario.

    Regards,
    John

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

    Re: Cells Select/Paste Value (XP; 03)

    You could save a copy of the workbook *before* modifying it, then open the copy, modify it, save it and close it. Repeat as often as necessary.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells Select/Paste Value (XP; 03)

    Hans,

    After giving it some thought I am considering 'adding a new workbook' and adding new sheets to the new workbook, pastevalueing things in and then saving the new workbook as another name.

    I was trying to avoid opening/closing the master file numerous times.

    Regards,
    John

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

    Re: Cells Select/Paste Value (XP; 03)

    Using the method from my previous reply, you only need to open the master workbook once. But pasting values into a new workbook will work too.

Posting Permissions

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