copy and paste special

[bonovox1]

hi i have a macro that i run after i run this macro i have to copy and paste the 3 sheets of my file. Could somebody suggest code that could do this in the macro itself.

 

[Lewiy]

Which sheets do you want to copy and where do you want to paste them?

 

[bonovox1]

After i run the macro i copy and paste special values on all 3 sheets in the file. Simply i want to copy and paste special values on each sheet using vba code instead of having to do it manually after the macro has run.

 

[Lewiy]

Try this:

Dim wb As Workbook
Dim sh As Worksheet
Set wb = ThisWorkbook
For Each sh In wb.Worksheets
    sh.UsedRange.Value = sh.UsedRange.Value
Next sh

 

[bonovox1]

hi, i tried this but the macro runs perfectly but it doesn`t copy and paste special values at the end. Any ideas?

 

[Lewiy]

What does it do (if anything)?

 

[bonovox1]

it does nothing, macro runs fine and all.

 

[Lewiy]

Just to make sure it’s definitely running the code, try this:

Dim wb As Workbook
Dim sh As Worksheet
Set wb = ThisWorkbook
For Each sh In wb.Worksheets
    MsgBox sh.Name
    sh.UsedRange.Value = sh.UsedRange.Value
Next sh

You should get a box pop up with the name of each sheet as it does the conversion.