CODEWITHSUNDEEP
excelvbscriptvba
arunpandiyarajhen
arunpandiyarajhen

Reputation: 653

How to Copy specific column values from one excel file to another?

I'm trying to copy specific column values from one excel file to the other. This is what I've tried.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\A.xls")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\C.xls")

Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

Set objRange = objWorksheet.Range("A:Q").EntireColumn
objRange.Copy

Set objWorksheet2 = objWorkbook2.Worksheets(1)
objWorksheet.Activate

objWorksheet.Range("A:B").EntireColumn.Copy objWorksheet2.Paste
objWorksheet2.Range ("A1")

objWorksheet.Columns("Q").EntireColumn.Copy objWorksheet2.Paste
objWorksheet2.Range ("C1")

objWorkbook2.Save 
objWorkbook2.Close

My doubt is, the column Q contains formula. So, when column is copied, only formula is copied but not the values. Please help to copy the values in the cell but not the formula.

Thanks in advance.

Upvotes: 0

Views: 371

Answers (1)

SJR
SJR

Reputation: 23081

Try this. You should get in the habit of declaring your variables (as well as formatting your code).

Sub x()

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\A.xls")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\C.xls")

Set objWorksheet = objWorkbook.Worksheets(1)

Set objRange = objWorksheet.Range("A:Q").EntireColumn
objRange.Copy 'not sure what this does

Set objWorksheet2 = objWorkbook2.Worksheets(1)

objWorksheet.Range("A:B").Copy objWorksheet2.Range("A1")

objWorksheet.Columns("Q").EntireColumn.Copy
objWorksheet2.Range("C1").PasteSpecial xlValues

objWorkbook2.Save
objWorkbook2.Close

End Sub

Upvotes: 2

Related Questions