Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

LandoMarossi

Programmer
Jun 10, 2022
4
0
0
IT
Hi,

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
attachment_km8rxd.jpg


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


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.

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

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?
 
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:p) you must define the extent in rows. For instance, if your data in columns M:p is from row 1 to 99, then
Code:
objWorkSheet.Range("M1:P99").Copy

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


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Hi, thanks for reply.

I have edit my code, but now I have error

Code:
Object Doesn't Support this Property or Method

on this line

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

My new code below

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

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

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

objWorkbook2.Save 
objWorkbook2.Close
objWorkbook.Close

objExcel.Quit
Set objExcel = Nothing

 
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...
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

[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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Thanks.

I've tried your code.

I've the same error

Code:
Object Doesn't Support this Property or Method

on the same line

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

Your complete code

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
 
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").[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...
Code:
objWorkbook2.Worksheets(1).Range("M4").[b]PasteSpecial xlPasteAll[/b]

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
See my previous post edit

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top