Copy Visible Columns VALUES and FORMAT - is this possible?

[9tanstaafl9]

I have to copy data from sheet 2 to sheet 1. Sheet 2 has a bunch of hidden columns (I don't know which they will be ahead of time).

This works to paste formulas, but I need values:

Sheet2.Range("B8:AM28").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet1.Range("A12")

I must past values not formulas. IDEALLY I'd paste Values, Formats, Column Widths and Row Heights. But at least getting Values is critical.

Any help would be greatly appecated.

 

[9tanstaafl9]

Nevermind. I didn't realize pastespecial didn't work with ranges. I got rid of the destination and used:ActiveCell.PasteSpecial xlPasteValues and it worked.