VBScript To copy the specific columns only to specific cell in Excel 365 1

[LandoMarossi]

Hi,

I'm trying to copy specific column values (range "M") from one excel 365 file to the other on the range "M", 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?

 

[SkipVought]

Hi,

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

Well, what do you expect? That's s exactly what your code says!

Your picture shows that your table has data in column L but nothing in column Q.

So without asking a lot of questions which should be asked in order to know what your data structure is, rather than defining the COPY ranges as COLUMNS (M) you must define the extent in rows. For instance, if your data in columns M is from row 1 to 99, then

objWorkSheet.Range("M1:P99").Copy

objWorkbook2.Worksheets(1).Range("M4").Paste

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[LandoMarossi]

Hi, thanks for reply.

I have edit my code, but now I have error

Object Doesn't Support this Property or Method

on this line

objWorkbook2.Worksheets(1).Range("M4").Paste

My new code below

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

objWorkSheet.Range("M1:P99").Copy

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

objWorkbook2.Worksheets(1).Range("M4").Paste 

objWorkbook2.Save 
objWorkbook2.Close
objWorkbook.Close

objExcel.Quit
Set objExcel = Nothing

 

[SkipVought]

objWorkbook2 has to be already set to the second workbook.

I ASSUMED that your would not simply run my code but insert it into your COPY N PASTE code as...

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

[b]objWorkSheet.Range("M1:P99").Copy

objWorkbook2.Worksheets(1).Range("M4").Paste[/b] 

objWorkbook2.Save 
objWorkbook2.Close
objWorkbook.Close

objExcel.Quit
Set objExcel = Nothing

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[LandoMarossi]

Thanks.

I've tried your code.

I've the same error

Object Doesn't Support this Property or Method

on the same line

objWorkbook2.Worksheets(1).Range("M4").Paste

Your complete code

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

objWorkSheet.Range("M1:P99").Copy

objWorkbook2.Worksheets(1).Range("M4").Paste 

objWorkbook2.Save 
objWorkbook2.Close
objWorkbook.Close

objExcel.Quit
Set objExcel = Nothing

 

[SkipVought]

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

objWorkSheet.Range("M1:P99").Copy

objWorkbook2.Worksheets(1).Range("M4").[b]PasteSpecial -4104[/b]

objWorkbook2.Save 
objWorkbook2.Close
objWorkbook.Close

objExcel.Quit
Set objExcel = Nothing

PS: If you were coding in Excel VBA rather than VB Script, the syntax would be...

objWorkbook2.Worksheets(1).Range("M4").[b]PasteSpecial xlPasteAll[/b]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[LandoMarossi]

Thanks a lot!

Now working correctly.

 

[SkipVought]

See my previous post edit

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!