Paste All Cells as Values on a Hidden Sheet - VBA

[eoinymc]

Hi,

I want to copy and paste all cells as values on a hidden sheet.

When the sheet is not hidden, I currently do this:

Cells.Select                Selection.Copy
                Selection.PasteSpecial Paste:=xlPasteValues

However, I know you cannot use select on a hidden sheet.

Has anyone got any ideas how to do this?

Cheers,

E

 

[VoG]

Try like this

With Sheets("Sheet1").UsedRange
    .Value = .Value
End With

 

[mrhopko]

You answered your own question really. Don't use Select. Something like:

<code>
sheets("myworksheet").Range("myrange").Copy
sheets("myworksheet2").Range("myrange2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
</code>

hope that helps

mrHopko

 

[eoinymc]

Thanks Peter and mrHopko,

Peter, you're is quite simple and works great!

One other related question you may be able to help me with...

How do I paste special formulas without using select?

Range(Cells(4, 11), Cells(55, 11)).Copy
Range(Cells(4, 11), Cells(55, 23)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas

Thanks,

E

 

[VoG]

Try

Range(Cells(4, 11), Cells(55, 11)).Copy
Range(Cells(4, 11), Cells(55, 23)).PasteSpecial Paste:=xlPasteFormulas

 

[eoinymc]

That's perfect Peter....Great...

One final one and my code will have been transformed to allow all sheets to be hidden!

This works when not hidden, can it be transformed to work with hidden?

Range(Cells(i, 11 + CumMonth - 1), Cells(i, 23)).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.599993896298105
            .PatternTintAndShade = 0
        End With

Thank you so much for all your help!

E

 

[Rick Rothstein]

That's perfect Peter....Great...

One final one and my code will have been transformed to allow all sheets to be hidden!

This works when not hidden, can it be transformed to work with hidden?

Range(Cells(i, 11 + CumMonth - 1), Cells(i, 23)).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.599993896298105
            .PatternTintAndShade = 0
        End With

Thank you so much for all your help!

E

Think about it... whatever you select becomes the selection, so just use what you selected in place of the selection directly...

With Range(Cells(i, 11 + CumMonth - 1), Cells(i, 23)).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent3
    .TintAndShade = 0.599993896298105
    .PatternTintAndShade = 0
End With

 

[VoG]

Try

With Range(Cells(i, 11 + CumMonth - 1), Cells(i, 23)).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent3
    .TintAndShade = 0.599993896298105
    .PatternTintAndShade = 0
End With

 

[eoinymc]

ha...ok, I get it now!

Thanks guys!