×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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


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.

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?

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

Hi,

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

(OP)
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 


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

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

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

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

objWorkbook2.Save 
objWorkbook2.Close
objWorkbook.Close

objExcel.Quit
Set objExcel = Nothing 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

(OP)
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 

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

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").PasteSpecial -4104

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").PasteSpecial xlPasteAll 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

(OP)
Thanks a lot!

Now working correctly.

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

See my previous post edit

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close