Macro: Paste Values

[Jennifer]

I'm using this macro to make one column values only:
Sub ValuesOnly( )

Dim rRange As Range

On Error Resume Next

Set rRange = Application.InputBox(Prompt:="Select the formulas",
Title:="VALUES ONLY", Type:=8)

If rRange Is Nothing Then Exit Sub

rRange = rRange.Value

End Sub

This works great for one worksheet but how do I apply it to several
worksheets? The column that I'm select to paste values only is the same for
each worksheet.

Basically I would like to select a range of cells and have it apply to that
same range of cells for every worksheet.

 

[Gary''s Student]

a tiny trick:

Sub ValuesOnly()
Dim rRange As Range
On Error Resume Next

Set rRange = Application.InputBox(Prompt:="Select the formulas",
Title:="VALUES ONLY", Type:=8)
If rRange Is Nothing Then Exit Sub
ad = rRange.Address
For Each sh In Sheets
sh.Range(ad).Value = sh.Range(ad).Value
Next
End Sub

 

[Dave Peterson]

Option Explicit
Sub ValuesOnly2()

Dim sAddr As String
Dim wks As Worksheet

sAddr = ""
On Error Resume Next
sAddr = Application.InputBox(Prompt:="Select the formulas", _
Title:="VALUES ONLY", Type:=8).Address
On Error GoTo 0

If sAddr = "" Then
Exit Sub
End If

For Each wks In ActiveWorkbook.Worksheets
With wks.Range(sAddr)
.Value = .Value
End With
Next wks

End Sub

 

[Jennifer]

This works great! Thanks so much.

One question:
As far as I can tell it did the paste values into all the correct
worksheets. Can you explain to me how (or which part of the code) did this?
This sounds a little ambiguous, I mean that there are worksheets I didn't
want to do the paste special and the code seemed to do that. Can you explain?

 

[Dave Peterson]

For Each wks In ActiveWorkbook.Worksheets

With wks.Range(sAddr)
.Value = .Value
End With
Next wks

Is the part that pasted the values. And you'll notice that it did the work for
each worksheet in the activeworkbook.

If you had sheets that had formulas in that range's address, then they got
converted to values, too. Double check your workbook before you save it!

If you don't want all the worksheets, you can group the sheets first (click on
the first tab and ctrl-click on subsequent tabs).

But change the code in the macro to just process the selected sheets:

for each wks in activewindow.selectedsheets

Remember to ungroup the worksheets when you're done.