Copy one cell from a workbook sheet to another workbook sheet

I've been testing. Instead of:
Public wkb As Workbook '<< added line.
in the Thisworkbook module, delete it from there and put it into any (single) standard module (names are like Module1, Module2 etc.) in the same file.

Again, after these changes, to test, you will have to save and close the workbooks, then re-open the one with the code in so that the Workbook_Open() event runs.
 
I'm confused! Here is the code that I have so far. The password is ka3pmw all lower case.

The files are included here and the path is C:\Skywarn\

BTW the event spaning midnight is not a problem as it would be the date it was started.
 

Attachments

  • Emergency Log.xls
    216 KB · Views: 10
  • Log Summary.xls
    64 KB · Views: 7
VBA Neofite said:
I'm confused!
Click to expand...
Let me tell you, however confused you are, I'm even more so.
You have files, all of which have their own various event-triggered code, among them, renaming themselves. They will rename and save themselves regardless of when, what or who opens them.
If someone opens one of these files that was made a few days ago, it will immediately save itself under a new name with today's date; but what if such a named file already exists? - there's danger of it being overwritten. Maybe you want that.



VBA Neofite said:
Here is the code that I have so far.
Click to expand...
I note that in Emergency log.xls you have two versions of the Copycell macro; one in the FORMS sheet code-module, and one in Module4. It would help to know how you intend the Copycell code to be started; manually,? automatically?, on the click of a button?
I suspect that you want to lose Module4 altogether and keep the version in the FORM sheet's code-module. You haven't moved the line Public wkb As Workbook '<< added line from the top of the ThisWorkbook code-module to the top of one of the standard code-modules as suggested in message #22
I will look more closely at your files today/tomorrow, but not right now.



VBA Neofite said:
The password is ka3pmw all lower case.
Click to expand...
It would help if you removed such password protection - just an unneccessary hoop for people trying to help you to have to jump through.



VBA Neofite said:
BTW the event spaning midnight is not a problem as it would be the date it was started.
Click to expand...
Oh yes it is - your current cell-copying code looks for a file with today's date (the date while the code is run ). If the file was opened by a user before midnight, and the cell-copying took place after midnight, the file wouldn't be found.
Of course, you might say, that would never happen, but it is easy to envisage one or two situations where it could easily happen; users (those very users, some of whom you say 'are lucky to fill it out let alone do a copy and paste') can leave files open at the end of the day and come back to them the next morning (they may have had to, say in the event of a fire alarm and subsequent emergency building evacuation).

Coding is, in large part, a battle to keep things foolproof; the fools nearly always win.
 
p45cal said:
Let me tell you, however confused you are, I'm even more so.
You have files, all of which have their own various event-triggered code, among them, renaming themselves. They will rename and save themselves regardless of when, what or who opens them.
If someone opens one of these files that was made a few days ago, it will immediately save itself under a new name with today's date; but what if such a named file already exists? - there's danger of it being overwritten. Maybe you want that.
Click to expand...
Good point. Since the Log Summary file is going to have the date automatically copied over (Cell C2) maybe I can use that cell as the source for the date part of the file. One thing that I do have a problem with is the case where we have multiple events in the same day like two events of Storm Warning that are a few hours apart after the first one is canceled, or a storm warning and then after the warning has expired a tornado warning is issued several hours later.


p45cal said:
I note that in Emergency log.xls you have two versions of the Copycell macro; one in the FORMS sheet code-module, and one in Module4. It would help to know how you intend the Copycell code to be started; manually,? automatically?, on the click of a button?
I suspect that you want to lose Module4 altogether and keep the version in the FORM sheet's code-module. You haven't moved the line Public wkb As Workbook '<< added line from the top of the ThisWorkbook code-module to the top of one of the standard code-modules as suggested in message #22
I will look more closely at your files today/tomorrow, but not right now.
Click to expand...
I will remove the password for now but it needs to stay there as the shaded cells are not to be altered by the users. The idea is that every time any entry is made in cells F2, I2, I3,J3, and B4:B153 the summary is to be updated. I don't really care if the Summary file can be changed by a user or not. It would probably be best if it was totally dependent of the Log. That way there would be some semblance of accuracy to it.


p45cal said:
It would help if you removed such password protection - just an unneccessary hoop for people trying to help you to have to jump through.
Click to expand...



p45cal said:
Oh yes it is - your current cell-copying code looks for a file with today's date (the date while the code is run ). If the file was opened by a user before midnight, and the cell-copying took place after midnight, the file wouldn't be found.
Of course, you might say, that would never happen, but it is easy to envisage one or two situations where it could easily happen; users (those very users, some of whom you say 'are lucky to fill it out let alone do a copy and paste') can leave files open at the end of the day and come back to them the next morning (they may have had to, say in the event of a fire alarm and subsequent emergency building evacuation).
Click to expand...

This program is used for weather emergencies and public service events such as races, marathons, parades etc. If we have an event that goes past midnight, we will have to create a new log as I can't get the time to go past 24 hours. If I started a log at 11:00 and the event ends at 9:00 the next day the total hours will show ##### instead of 22 hours.

p45cal said:
Coding is, in large part, a battle to keep things foolproof; the fools nearly always win.
Click to expand...

I will attach a revised code. Please tell me how to delete the other copies.
 

Attachments

  • Emergency Log.xls
    210 KB · Views: 6
  • Log Summary.xls
    64 KB · Views: 10
Last edited by a moderator:
Attached Emergency Log.xls only.
Very few amendments made, moved the Public wkb As Workbook '<< added line to the first line of Module 1.
The Copy_cell macro in the FORM sheet code-module seeems to work.
 

Attachments

  • Emergency Log.xls
    187.5 KB · Views: 14
VBA Neofite said:
I just tried it. While there were no errors, nothing was copied either.
Click to expand...
I've re-checked and after running the macro Copycell (it's at the bottom of the FORM sheet code-module), cell C2 of Sheet1 of the file 06-11-2018 Log Summary.xls contains 43262, which is today's date (format the cell as date).
 
Last edited:
I changed the date format a.d still nothing is transferred. ai even ran the sub manually. What am I doing wrong? Should I be defining the source as ThisWorkbook.Sheets ???
 
Confirm:

  • You are using the file I attached in msg#26 ?
  • You are starting with any other versions of the relevant files closed.
  • You're not playing about with macros (stopping one from running for example) before you try the Copycell macro.
I'm going to test on a machine which has Excel 2003 on it…

…Yep, works there too.
 
Last edited:
To be certain, this is the version of Copycell in its entirety at the bottom of the code-module belonging to the sheet called FORM:
Code: 
Sub Copycell()
wkb.Sheets("Sheet1").Range("C2") = Range("F2")
End Sub
 
I copied the version that you sent to my C:\Skywarn directory. I then executed it and it launched the Log Summary and started the new files as it should. I reset the warnings for the privacy and the macros. I replaced the original emergency log with the one you sent. I left the log summary as it was. I am not stopping any macros. I did try to run the Copycell macro by itself after I checked the Log Summary and found that it was blank.
 
Ensure you're checking:
cell C2 (2nd row, 3rd column) of Sheet1 of the already open file: 06-11-2018 Log Summary.xls
and that you answer Yes, when it asks: do you want to replace the file of the same name.

And for the avoidance of doubt, temporarily add the line:
Application.Goto wkb.Sheets("Sheet1").Range("C2")
just before the End Sub, which will go and select that cell for you, just to make sure you're looking in the right place.

Unless you've stopped a macro running, it shouldn't need a save and reopening of the files.
 
Last edited:
Ok, I found the problem. We were sending it to Sheet1 instead of FORM.

I added the following:
Code: 
wkb.Sheets("FORM").Range("C2") = Range("F2")
wkb.Sheets("FORM").Range("G3") = Range("I1")
wkb.Sheets("FORM").Range("D3") = Range("I3")
wkb.Sheets("FORM").Range("G3") = Range("J3")
wkb.Sheets("FORM").Range("B4:B153") = Range("B4:B153")
Application.Goto wkb.Sheets("FORM").Range("C2")

The first 4 lines work great! The 5th does not. Also, will this run every time I make an entry?
 
VBA Neofite said:
Ok, I found the problem. We were sending it to Sheet1 instead of FORM.
Click to expand...
I was very clear on that… it was your code anyway.



VBA Neofite said:
The 5th does not.
Code: 
wkb.Sheets("FORM").Range("B4:B153") = Range("B4:B153")
Application.Goto wkb.Sheets("FORM").Range("C2")
Click to expand...
In what way does it not work? Try:
Code: 
wkb.Sheets("FORM").Range("B4:B153").value = Range("B4:B153").value
'Application.Goto wkb.Sheets("FORM").Range("C2") 'you probably don't want this line any more



VBA Neofite said:
Also, will this run every time I make an entry?
Click to expand...
Nope. How will we know when an entry is complete? At the moment you have to run it manually. A button perhaps?
 
Last edited:
It works now. Here is the correct routine:
Code: 
Sub Copycell()
wkb.Sheets("FORM").Range("C2") = Range("F2")
wkb.Sheets("FORM").Range("G3") = Range("I1")
wkb.Sheets("FORM").Range("D3") = Range("I3")
wkb.Sheets("FORM").Range("F2") = Range("J3")
wkb.Sheets("FORM").Range("B4:B153").Value = Range("B4:B153").ValueApplication.Goto wkb.Sheets("FORM").Range("C2")
End Sub

All I need now is for it to update the cells when an entry is made. It would be best if they were updated when an entry was madde in one of the cells in the list of F2, I1, I3, J3, or B4:B153

Right now I have to run the macro manually.
 
RE:
Code: 
As an aside, you're using a shaky method of referring to workbooks; Thisworkbook is robust when referring to the workbook that the code resides in. Using Now means that if a file is being worked on over midnight you're guaranteed to get the wrong file name.
I just talked with one of the people that is up the food chain from me. They total all the hours for the month on their report that goes where ever (FEMA, PEMA, ARRL, Home Land Security). They told me not to worry about past midnight, just start another log.
 
Problem Solved. I added a button on the menu at the right to run the sub. Works fine and doesn't interfere with the form.

Thanks for all the help.
 
You must log in or register to reply here.
Back
Top