insert column in another workbook on a sheet1 from current workbook

excel vba
I have data i'm copying to another workbook, from my current workbook.

Dim curWks As Worksheet
Dim templWks As Worksheet
Dim rngToCopy As Range

  
Set curWks = ActiveSheet
With curWks

Set rngToCopy = .Range("A1:AX65453", .Range("a1").End(xlToRight).End(xlDown))
End With

Workbooks.Open _
fileName:="C:\Program Files\enterprise\Customer Copy\Customer_Template.xlsx"


If ActiveSheet.Name = "Project Data" Then
Else
Sheets("project Data").Activate
End If

Set templWks = ActiveSheet
templWks.Cells.Select
templWks.Cells.Clear

' APPENDS DATA
rngToCopy.Copy _
Destination:=templWks.Range("A65453").End(xlUp)


' after i copy data i need to insert a column at  Column P


Workbooks("Customer_Template.xlsx").Close SaveChanges:=True
Application.CutCopyMode = False

Open in new window

What I need:
after i copy data i need to insert a column at Column P in the other workbook.
And give the Column Header a name "Customer Price"

Thanks
fordraiders

LVL 3

FordraidersAsked: 2017-11-20

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 31

ShumsDistinguished Expert - 2017Commented: 2017-11-20

Hi,

Try below:

Sub CopyData()
Dim TrgtWB As Workbook
Dim curWks As Worksheet
Dim templWks As Worksheet
Dim rngToCopy As Range

Set curWks = ActiveSheet
With curWks

Set rngToCopy = .Range("A1:AX65453", .Range("a1").End(xlToRight).End(xlDown))
End With

Set TrgtWB = Workbooks.Open(Filename:="C:\Program Files\enterprise\Customer Copy\Customer_Template.xlsx")

If ActiveSheet.Name = "Project Data" Then
Else
TrgtWS.Activate
End If

Set templWks = ActiveSheet
templWks.Cells.Select
templWks.Cells.Clear

' APPENDS DATA
rngToCopy.Copy _
Destination:=templWks.Range("A65453").End(xlUp)


' after i copy data i need to insert a column at  Column P
templWks.Columns("P:P").Insert
templWks.Range("P1").Value = "Customer  Price"
TrgtWB.Close SaveChanges:=True
Application.CutCopyMode = False
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 3

FordraidersAuthor Commented: 2017-11-20

thanks !

LVL 31

ShumsDistinguished Expert - 2017Commented: 2017-11-20

Pls try below, I have edited above code:

Sub CopyData()
Dim TrgtWB As Workbook
Dim curWks As Worksheet
Dim templWks As Worksheet
Dim rngToCopy As Range

Set curWks = ActiveSheet
With curWks

Set rngToCopy = .Range("A1:AX65453", .Range("a1").End(xlToRight).End(xlDown))
End With

Set TrgtWB = Workbooks.Open(Filename:="C:\Program Files\enterprise\Customer Copy\Customer_Template.xlsx")

If ActiveSheet.Name = "Project Data" Then
Else
Sheets("project Data").Activate
End If

Set templWks = ActiveSheet
templWks.Cells.Select
templWks.Cells.Clear

' APPENDS DATA
rngToCopy.Copy _
Destination:=templWks.Range("A65453").End(xlUp)


' after i copy data i need to insert a column at  Column P
templWks.Columns("P:P").Insert
templWks.Range("P1").Value = "Customer  Price"
TrgtWB.Close SaveChanges:=True
Application.CutCopyMode = False
End Sub