Solved

Value pasting to wrong workbook

Posted on 2011-03-23
6
276 Views
Last Modified: 2012-05-11
I have a workbook that inserts the time to cell C15. However when I have another workbook open the time is inserted to cell C15 of the active workbook. How to I prevent this from occurring?
Snippet from code that puts the data and time in the C15 cell 
of the Active workbook  when I need it in
ThisWorkbookSheets(1).Range C15

Set TimeC = range("C15")
   TimeC = TimeC.Value
   range("B15").Value = Left(Trim(range("B15").Value), 12)
   MyData.SetText ""
   MyData.PutInClipboard
   TimeC.Value = Date & ":" & Time

Open in new window

0
Comment
Question by:llawrenceg
6 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35200124
Try referring to the range like ths:

ThisWorkbook.Range("C15")

Dvae
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35200142
Set TimeC = ThisWorkbook.Range("C15") 'where the code is running
TimeC = TimeC.Value

Now that TimeC is pointed to the right workbook, the rest of your code should be fine.

Dave
0
 
LVL 20

Assisted Solution

by:pari123
pari123 earned 166 total points
ID: 35200162
whats the name of the target workbook?


you can use somthing like this...replace target with the actual name of the excel file

Workbooks("Target").Activate
Set TimeC = range("C15")
   TimeC = TimeC.Value
   range("B15").Value = Left(Trim(range("B15").Value), 12)
   MyData.SetText ""
   MyData.PutInClipboard
   TimeC.Value = Date & ":" & Time

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 167 total points
ID: 35200285
Set TimeC = ThisWorkbook.Sheets(1).range("C15")  
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 167 total points
ID: 35200813
I forgot the sheets part

Lol
0
 

Author Closing Comment

by:llawrenceg
ID: 35200988
Great Guys , I needed to know where to put it and you  told me.
Thanks Works perfectly
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question