Cells Select/Paste Value (XP; 03)

[jstevens]

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

[jstevens]

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

[jstevens]

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

[HansV]

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

[jstevens]

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

[HansV]

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.

[jstevens]

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

[HansV]

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.