Copy/Paste Values entire Workbook

[RunRabbit13]

I was using this code which Andrew Poulsom posted back in 2002. http://www.mrexcel.com/forum/excel-...ations-code-copy-worksheets-paste-values.html It works great, but I want it in my own Personal Macro workbook. When I place the code in Personal it no longer works. It is only when the code is introduced as new to the workbook that it works. What am I missing/doing wrong?

Code:
Sub Test()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
If Sh.Visible = True Then
Sh.Activate
Sh.Cells.Copy
Sh.Range("A1").PasteSpecial Paste:=xlValues
Sh.Range("A1").Select
End If
Next Sh
Application.CutCopyMode = False
End Sub

 

[MARK858]

Edit, probably cells will be too much so untested but try

Sub Test()
 Dim Sh As Worksheet
 For Each Sh In ActiveWorkbook.Worksheets
 If Sh.Visible = True Then
 Sh.UsedRange.Value = Sh.UsedRange.Value
 End If
 Next Sh
 End Sub

 

[JoeMo]

I was using this code which Andrew Poulsom posted back in 2002. http://www.mrexcel.com/forum/excel-...ations-code-copy-worksheets-paste-values.html It works great, but I want it in my own Personal Macro workbook. When I place the code in Personal it no longer works. It is only when the code is introduced as new to the workbook that it works. What am I missing/doing wrong?

Code:
Sub Test()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
If Sh.Visible = True Then
Sh.Activate
Sh.Cells.Copy
Sh.Range("A1").PasteSpecial Paste:=xlValues
Sh.Range("A1").Select
End If
Next Sh
Application.CutCopyMode = False
End Sub

Change ThisWorkbook to ActiveWorkbook. If the code is in your Personal workbook then ThisWorkBook is your Personal workbook.

 

[Ken Cowen]

This code copies all the sheets in the workbook that it resides in to a new workbook. If it resides in a workbook other than the one you would like to copy, you need to replace the line

For Each Sh In ThisWorkbook.Worksheets

with one that properly identifies the workbook you would like to copy. If you always intend to copy the active workbook, you can probably just go with

For Each Sh In ActiveWorkbook.Worksheets

if there is an active workbook, and it is the one you want to copy, that change should take care of your problem.

I hope this helps.

Ken

 

[RunRabbit13]

JoeMo and Ken Cowen - Thank you both. Usually it is something so simple that I miss. I appreciate you both ending my frustration. Final code is below if someone ever wants it. I give the user the option to copy/paste values on all sheets or only on visible sheets (in case you want to skip hidden). I am sure it is bulkier than need be but it gets the job done.

Sub RemoveFormulas()
'This will copy/paste all values in the entire workbook (with option for hidden sheets)
Application.ScreenUpdating = False
Dim Sh As Worksheet
Dim Q1, Q2 As Integer
Q1 = MsgBox("Would you like to remove all formulas from this workbook?", _
vbQuestion + vbYesNo)
If Q1 = vbNo Then Exit Sub

If Q1 = vbYes Then Q2 = MsgBox("Would you like to include hidden worksheets?", _
vbQuestion + vbYesNo)



If Q2 = vbYes Then
For Each Sh In ActiveWorkbook.Worksheets
Sh.Activate
Sh.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Sh.Range("A1").Select
Next Sh
End If

If Q2 = vbNo Then
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = True Then
Sh.Activate
Sh.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Sh.Range("A1").Select
End If
Next Sh

End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[MARK858]

JoeMo and Ken Cowen - Thank you both. Usually it is something so simple that I miss. I appreciate you both ending my frustration. Final code is below if someone ever wants it. I give the user the option to copy/paste values on all sheets or only on visible sheets (in case you want to skip hidden). I am sure it is bulkier than need be but it gets the job done.

Sub RemoveFormulas()
'This will copy/paste all values in the entire workbook (with option for hidden sheets)
Application.ScreenUpdating = False
Dim Sh As Worksheet
Dim Q1, Q2 As Integer
Q1 = MsgBox("Would you like to remove all formulas from this workbook?", _
vbQuestion + vbYesNo)
If Q1 = vbNo Then Exit Sub

If Q1 = vbYes Then Q2 = MsgBox("Would you like to include hidden worksheets?", _
vbQuestion + vbYesNo)



If Q2 = vbYes Then
For Each Sh In ActiveWorkbook.Worksheets
Sh.Activate
Sh.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Sh.Range("A1").Select
Next Sh
End If

If Q2 = vbNo Then
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = True Then
Sh.Activate
Sh.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Sh.Range("A1").Select
End If
Next Sh

End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Why do you need to activate the sheet???