VBA How To Paste With Blank One Column Over

0 votes

How can I change my code such that a blank column appears between pastes? This was pasted onto a different sheet after looping each filter. The code below is functional, however, it should start in column B, not C.

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
5 days ago in Others by Kithuzzz
• 27,740 points
41 views

1 answer to this question.

0 votes

Try this:

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 5 days ago by narikkadan
• 51,240 points

Related Questions In Others

0 votes
0 answers

How to drop all tables from a database with one SQL query?

What if we don't want to type ...READ MORE

May 28, 2022 in Others by Sohail
• 2,960 points
68 views
0 votes
0 answers

How to drop all tables from a database with one SQL query?

drop all tables without typing name. Is ...READ MORE

Jun 7, 2022 in Others by polo
• 1,480 points
64 views
0 votes
1 answer

How to insert a picture into Excel at a specified cell position with VBA

Try this: With xlApp.ActiveSheet.Pictures.Insert(PicPath) With ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 51,240 points
397 views
0 votes
1 answer

How To Use VBA To Share Excel File With Fellow Office User?

PowerApps were referenced; if you have a ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 51,240 points
131 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,382 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
110 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
276 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 51,240 points
222 views
0 votes
1 answer

Transpose column on one sheet to row on another, with linking

select A1:J1 in worksheet B and enter: =TRANSPOSE('worksheet ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 51,240 points
124 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP