How to Copy specific range column values from one excel 365 file to another using VBScript?

Question

start edit 20220610

I'm trying to copy specific column values (range "M:P") from one excel 365 file to the other on the range "M:P", using VBScript.

Master.xlsx file structure

Copy_2022.xlsx file structure (after the copy columns from Master.xlsx)

The copy of specific column values working correctly from Master.xlsx file to Copy_2022.xlsx file.

But the problem is that the copied columns values start from cell M1 and not from cell M4 on Copy_2022.xlsx file.

This is what I've tried.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Master.xlsx")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\Copy_2022.xlsx")

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

Set objRange = objWorkSheet.Range("M:P").EntireColumn
objRange.Copy

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

Set objRange = objWorkSheet2.Range("M:P")
objWorksheet.Paste(objRange)

objWorkbook2.Save 
objWorkbook2.Close
objWorkbook.Close

objExcel.Quit
Set objExcel = Nothing

Any suggestion?

end edit 20220610

I'm trying to copy specific column values (range "M:P") from one excel 365 file to the other on the range "M:P", using VBScript.

This is what I've tried.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Master.xlsx")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\Copy_2022.xlsx")

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

Set objRange = objWorksheet.Range("M:P").EntireColumn
objRange.Copy

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

objWorksheet.Range("M:P").EntireColumn.Copy objWorksheet2.Paste
objWorksheet2.Range("M:P")

objWorkbook2.Save 
objWorkbook2.Close

When the code reaches the below line I am getting the below error.

objWorksheet.Range("M:P").EntireColumn.Copy objWorksheet2.Paste

Run Time Error '1004': Paste Method Of worksheet Class Failed error

Any suggestion?

Thanks in advance for any help.

Really appreciated

edit

New version same error

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Master.xlsx")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\Copy_2022.xlsx")

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

Set objRange = objWorksheet.Range("M:P").EntireColumn
objRange.Copy

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

objWorksheet.unprotect
objWorksheet2.unprotect

objWorksheet.Range("M:P").EntireColumn.Copy objWorksheet2.Paste
objWorksheet2.Range("M:P")

objWorksheet.protect
objWorksheet2.protect

objWorkbook2.Save 
objWorkbook2.Close

Why do you have `objWorksheet.Range("M:P").EntireColumn.Copy` and `objWorksheet2.Paste` on a single line? That seems wrong. — Geert Bellekens, Jun 09 '22 at 16:26
@GeertBellekens I've tried using two separate lines. Not working. — Lando Marossi, Jun 09 '22 at 16:36
You are not making your question better with all these edits. Make sure you focus your question on one problem. If you have another problem you better create a new question. — Geert Bellekens, Jun 10 '22 at 12:21
@GeertBellekens That's what I did but it was closed. Really disappointing [link](https://stackoverflow.com/questions/72565672/vbscript-to-copy-the-specific-columns-only-to-specific-cell-in-excel-365) — Lando Marossi, Jun 10 '22 at 13:04

How to Copy specific range column values from one excel 365 file to another using VBScript?

0 Answers0