VBA How To Paste With Blank One Column Over?

Question

How do I edit my code to paste with a blank column between each paste? This is from a loop of each filter and pasting to another sheet. The code below works but it starts at C column when I want it to start at B.

Set shtcopy = Sheets("Summary Copying")
Set shtpaste = Sheets("Summary")
        
        shtcopy.PivotTables
            pt.TableRange1.Copy
            shtpaste.Cells(10, Columns.Count).End(xlToLeft).Offset(, 2).PasteSpecial Paste:=xlPasteValues
            shtpaste.Cells(10, Columns.Count).End(xlToLeft).Offset(, -4).PasteSpecial Paste:=xlPasteFormats
        shtpaste.Cells.Columns.AutoFit

n = n What is that line for ? Also the With is doing nothing, maybe you meant Set shtcopy = .Sheets("Summary Copying") and Set shtpaste = .Sheets("Summary") — CDP1802
– CDP1802, Commented Jan 25, 2023 at 18:22
@CDP1802 You were correct, I removed with and n=n. When I do .sheets("Summary") it's an invalid reference, works without it though. There's other parts of the code as well. Still just need the pastes to column B rather than C. — ojmayo
– ojmayo, Commented Jan 25, 2023 at 18:38

CDP1802 · Accepted Answer · 2023-01-25 18:44:54Z

1

Option Explicit

Sub CopyPaste()

    Dim pt As PivotTable, wb As Workbook, n As Long
    Dim shtCopy As Worksheet, shtpaste As Worksheet
    
    Set wb = ThisWorkbook
    With wb
        Set shtCopy = .Sheets("Summary Copying")
        Set shtpaste = .Sheets("Summary")
    End With
         
    Set pt = shtCopy.PivotTables(1)
    pt.TableRange1.Copy
         
    With shtpaste
        n = .Cells(10, .Columns.Count).End(xlToLeft).Column
        If n < 2 Then
            n = 2
        Else
            n = n + 2
        End If
    
        .Cells(10, n).PasteSpecial Paste:=xlPasteValues
        .Cells(10, n).PasteSpecial Paste:=xlPasteFormats
        .Cells.Columns.AutoFit
    End With
  
End Sub

answered Jan 25, 2023 at 18:44

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA How To Paste With Blank One Column Over?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related