Solved

Value pasting to wrong workbook

Posted on 2011-03-23
6
275 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 41

Expert Comment

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

ThisWorkbook.Range("C15")

Dvae
0
 
LVL 41

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 85

Accepted Solution

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

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

803 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