Export to Excel and Insert Column - Export works, insert doesn't

[KBertsch]

Hello,

I need to export from a program called QV into Excel, and insert a column before the existing column "X".

Here's my code so far:

Sub ExportToExcel()
Const xlShiftToRight = -4161
Set XLApp = CreateObject("Excel.Application") 'Creating excel instance in VB Script
XLApp.Visible = True 'Setting the value to False, so this happens in background
Set XLDoc = XLApp.Workbooks.Add 'Creating new excel workbook

XLDoc.Sheets(1).Name = "Auditor WS" 'Adding the sheet name as Export
Set XLSheet = XLDoc.Worksheets(1) 'Setting the XLSheet
Set MyTable = ActiveDocument.GetSheetObject("CH47") 'Setting MyTable variable to current QlikView Object
MyTableCount = MyTable.GetRowCount

Set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted
Mytable.CopyTableToClipboard True 'Copy data to Clipboard
XLSheet.Paste XLSheet.Range("A1") 'Paste data starting at a1
Set objRange = objExcel.Range("Y1").EntireColumn
objRange.Insert(xlShiftToRight)
End Sub

This successfully opens Excel, and copies over the information from the table in QlikView.

However, it does not insert the column.

What am I missing here?

Thanks!

 

[texasalynn]

Welcome to MrExcel forum. . . .

try this

Range("Y:Y").EntireColumn.Insert

 

[KBertsch]

Welcome to MrExcel forum. . . .

try this

Range("Y:Y").EntireColumn.Insert

Thanks, I changed to that, but it makes no difference - the file gets exported, but no column gets inserted.

I'm going nuts over this!

 

[texasalynn]

might need to add the sheet to this

Worsheet(XLSheet).Range("Y:Y").EntireColumn.Insert

 

[KBertsch]

might need to add the sheet to this

Worsheet(XLSheet).Range("Y:Y").EntireColumn.Insert

I ended up doing this:

XLSheet.Columns("Y:Y").Insert xlToRight

and it worked.

Now, how do I Paste-Special-Values....

 

[texasalynn]

here is a sample

Range("B5:HT5").Copy
Range("B5:HT5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False