Help with VBA code to paste values and format

[Jass]

Hello guys and thanks for looking to assist.

I am working on a massive report containing 25 different tabs which is linked to a soure tab (data). I need a macro that will copy/paste value and format the way each tabs are which will remove all linking and references, however, there is a unique situation.

If you look at the picture attached, in one of the tabs (x.Detail) i have pivot table to the left and some calculated data (non pivot) to the right with rows hidden. I want to be able to copy that tab and paste exactly as it is with hidden rows.

I'd appreciate if someone kind enough to help me with this. Please let me know any questions you may have.

 

[Chihiro]

So you want to copy the entire sheet as is?

Easiest way to do it by copying entire sheet. See code below and change "Sheet1" with source tab and "MySheet" with what ever you want the new sheet to be.

Sub Copier1()
    ActiveWorkbook.Sheets("Sheet1").Copy _
      after:=ActiveWorkbook.Sheets("Sheet1")
    ActiveSheet.Name = "MySheet"
End Sub

 

[Jass]

Chihiro,

That code copy the sheet and paste as is (with pivot). I need code toremove pivot from left side and paste only values and formatting. That code should do the same for all tabs w/ data in the workbook.

 

[Chihiro]

Without sample it's hard to give you exact code. But try something like this for copy values portion.

Sheets("Sheet1").Select
Cells.Select
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

For format something like...

Sheets("Sheet1").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

However, do note it won't copy and paste format of Pivot Tables when you copy and paste entire sheet. You must select Pivot Table range (without filter portion) to copy and paste format of Pivot Table.