Copy one cell from a workbook sheet to another workbook sheet

V

VBA Neofite

New member
Joined
Apr 12, 2018
Messages
23
Reaction score
0
Points
0
Location
Mather, PA USA
Website
www.ka3pmw.com
Excel Version(s)
2007
Hi All,
I have a problem that I hope you can help with. I have 2 workbooks and I need to copy some data froe one to the other.
Both workbooks reside in a folder called C:\Skywarn\ and are named as follows: mm-dd-yyyy Emergency Log.xls and mm-dd-yyyy Log Summary.xls.
I am trying to copy from the log to the summary. When I open a file called Emergency Log.xls, It automatically opens a file called Log Summary.xls and both create the mm-dd-yyyy files then use those files. What I need to do is copy a single cell from the Log to the Summary. For example I want to copy F2 from the log to C2 in the summary. I need to do this every time an entry is made in the Log as I may have changed the data in the cells.
I have included both workbooks here for you to look at. The password for the VBA routines is ka3pmw. I sure hope you can help me. I have been fighting this for several days and tried many solutions with no success.
Thank You
 

Attachments

  • Emergency Log.xls
    227 KB · Views: 14
  • Log Summary.xls
    64 KB · Views: 10
I have tried this and get an invalid qualifier error
Sub Copycell()
Dim FilePath1, FilePath2 As String
FilePath1 = ThisWorkbook.FullName
FilePath2 = ThisWorkbook.Path & "" & Format(Now, "dd-mm-yyyy") & " Log Summary"
FilePath2.Sheets("Sheet1").Range("F2") = FilePath1.Sheets("Sheet1").Range("C2")
End Sub
Click to expand...
 
I goofed. The correct sub should read:
Code: 
Sub Copycell()
 Dim FilePath1, FilePath2 As String
 FilePath1 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Emergency Log"
 FilePath2 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Log Summary"
 FilePath2.Sheets("FORM").Range("C2") = FilePath1.Sheets("FORM").Range("F2")
 End Sub

I still get an invalid qualifier error on the line FilePath2.Sheets("FORM").Range
Both files are open.
 
Last edited by a moderator:
if the workbook is open try:
Workbooks(FilePath2).Sheets("FORM").Range("C2") = Workbooks(FilePath1).Sheets("FORM").Range("F2")
and you may need to change:
FilePath2 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Log Summary"
to:
FilePath2 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Log Summary.xls"

If the workbook is closed, you may have to open it first with Workbooks.Open....
 
I get a Subscript out of Range on the line
Workbooks(FilePath2).Sheets("FORM").Range("C2") = Workbooks(FilePath1).Sheets("FORM").Range("F2")

The changes look like this:
Sub Copycell()
Dim FilePath1, FilePath2 As String
FilePath1 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Emergency Log"
FilePath2 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Log Summary"
Workbooks(FilePath2).Sheets("FORM").Range("C2") = Workbooks(FilePath1).Sheets("FORM").Range("F2")

End Sub

 
Subscript out of range means that vba can't match something in side the brackets (the subscript) in this case one or more of:

  1. the contents of FilePath2 can't be matched with an existing file name
    • When you debug (the yellow highlighted line), open the Locals pane and examine what's in the variable FilePath2; is it what you expect. If so is it open? Have you tried including the file extension in the name of the file in FilePath2/1?
  2. "FORM" is not exactly the correct sheet name. Check for an exact match.
  3. "C2" and "F2" are likely to be OK
  4. the contents of FilePath1 can't be matched with an existing file name
    • Do the same as for FilePath2.
 
Last edited:
Ok, I made the changes and still have a problem. It now looks like this:
Sub Copycell()
Dim FilePath1, FilePath2 As String
FilePath1 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Emergency Log.xls"
FilePath2 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Log Summary.xls"
FilePath2.Sheets("Sheet1").Range("F2") = FilePath1.Sheets("Sheet1").Range("C2")

End Sub

I have a compile error invalid qualifier with FilePath2 highlighted in blue. Sub Copycell() is in yellow.
the locals window says + : Me : : Sheet1/Sheet1
Both workbooks show Sheeti(FORM)
I am also getting this in several places:
: CurrentArray : <You cannot use this command on a protected sheet. To use this command, you must first unprotect the sheet (Review tab, Changes group, Unprotect Sheet button).
I need to protect the log. The summary can be unprotected.
 
The line:
FilePath2.Sheets("Sheet1").Range("F2") = FilePath1.Sheets("Sheet1").Range("C2")
won't work and has to be (as said before):
Workbooks(FilePath2).Sheets("Sheet1").Range("F2") = Workbooks(FilePath1).Sheets("Sheet1").Range("C2")
but even this might not work if the workbook is not already open.
To debug step by step add these lines directly after the FilePath2= line (comment-out or temporarily delete any line at the top of the module which reads Option Explicit):
Code: 
Debug.Print "]" & FilePath1 & "[" 'this should cause the filenames to appear in the Immediate Pane of the VBE - the square brackets are to make it obvious if there are any leading/trailing spaces.
Debug.Print "]" & FilePath2 & "["
Set yyy = Workbooks(FilePath2) 'if this fails the workbook name in FilePath2 is not open; check the name is correct and it's open.
Set yyyy=yyy.Sheets("Sheet1") 'if this fails the sheet name Sheet1 is wrong or it doesn't exist.
Set yyyyy = yyyy.range("F2") 'this is unlikely to fail if the code gets this far.

Set zzz = Workbooks(FilePath1) 'if this fails the workbook name in FilePath1 is not open; check the name is correct and it's open.
Set zzzz=zzz.Sheets("Sheet1") 'if this fails the sheet name is wrong or it doesn't exist.
Set zzzzz = zzzz.range("C2") 'this is unlikely to fail if the code gets this far.
These lines are temporary lines and can be deleted later.

If the code fails at the Set yyy or Set zzz line, then ensure the workbook concerned is open (do it manually if necessary) before trying again.
Report back with your results.
 
I set it up as:
Code: 
Sub Copycell()
 Debug.Print "]" & FilePath1 & "[" 'this should cause the filenames to appear in the Immediate Pane of the VBE - the square brackets are to make it obvious if there are any leading/trailing spaces.
Debug.Print "]" & FilePath2 & "["
Set yyy = Workbooks(FilePath2) 'if this fails the workbook name in FilePath2 is not open; check the name is correct and it's open.
Set yyyy = yyy.Sheets("Sheet1") 'if this fails the sheet name Sheet1 is wrong or it doesn't exist.
Set yyyyy = yyyy.Range("F2") 'this is unlikely to fail if the code gets this far.
Set zzz = Workbooks(FilePath1) 'if this fails the workbook name in FilePath1 is not open; check the name is correct and it's open.
Set zzzz = zzz.Sheets("Sheet1") 'if this fails the sheet name is wrong or it doesn't exist.
Set zzzzz = zzzz.Range("C2") 'this is unlikely to fail if the code gets this far.
 Dim FilePath1, FilePath2 As String
 FilePath1 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Emergency Log"
 FilePath2 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Log Summary"
 WorkbooksFilePath2.Sheets("Sheet1").Range("F2") = WorkbooksFilePath1.Sheets("Sheet1").Range("C2")
 
 End Sub
Now I get Filepath1 highlighted on the Dim line. Compile error duplicate declaration in current scope
 
Last edited by a moderator:
OK, I fixed some things in the code and tried again, I get Runtime error 9 Subscript out of range. Here is the revised code:
Code: 
Sub Copycell()
 Dim FilePath1, FilePath2 As String
 FilePath1 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Emergency Log.xls"
 FilePath2 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Log Summary.xls"
Debug.Print "]" & FilePath1 & "[" 'this should cause the filenames to appear in the Immediate Pane of the VBE - the square brackets are to make it obvious if there are any leading/trailing spaces.
Debug.Print "]" & FilePath2 & "["
Set yyy = Workbooks(FilePath2) 'if this fails the workbook name in FilePath2 is not open; check the name is correct and it's open.
Set yyyy = yyy.Sheets("Sheet1") 'if this fails the sheet name Sheet1 is wrong or it doesn't exist.
Set yyyyy = yyyy.Range("F2") 'this is unlikely to fail if the code gets this far.
Set zzz = Workbooks(FilePath1) 'if this fails the workbook name in FilePath1 is not open; check the name is correct and it's open.
Set zzzz = zzz.Sheets("Sheet1") 'if this fails the sheet name is wrong or it doesn't exist.
Set zzzzz = zzzz.Range("C2") 'this is unlikely to fail if the code gets this far.
 Workbooks(FilePath2).Sheets("Sheet1").Range("C2") = Workbooks(FilePath1).Sheets("Sheet1").Range("F2")
 End Sub
 
Last edited by a moderator:
VBA Neofite said:
OK, I fixed some things in the code and tried again, I get Runtime error 9 Subscript out of range.
Click to expand...
On which line?!
Did the file names/paths look right in the Immediate Pane?

edit post posting:
you say 'Both workbooks reside in a folder called C:\Skywarn\ and' so I think this:
FilePath1 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Emergency Log.xls"
should be this:
FilePath1 = "C:\Skywarn" & "\" & Format(Now, "dd-mm-yyyy") & " Emergency Log.xls"
or:
Code: 
FilePath1 = "C:\Skywarn\" & Format(Now, "dd-mm-yyyy") & " Emergency Log.xls"
or:
FilePath1 = "C:\Skywarn" & Application.PathSeparator & Format(Now, "dd-mm-yyyy") & " Emergency Log.xls"
similarly for FilePath2
 
Last edited:
At last my first attempt at helping:

If you simply want the same text to appear in another sheet try:

1. open the main sheet
2. select the cell you want, right click and copy
3. open the second sheet
4. select the cell you want this text to appear
5. right click and select "paste link"


that should always show the same text as your main sheet

I really hope this helps....​
 
I have no idea how the code got messed up. There were errors in it originally when I copied it over. Here is the way it is in the Sub:
Sub Copycell()
Dim FilePath1, FilePath2 As String
FilePath1 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Emergency Log.xls"
FilePath2 = "C:\Skywarn" & "" & Format(Now, "dd-mm-yyyy") & " Log Summary.xls"
Debug.Print "]" & FilePath1 & "[" 'this should cause the filenames to appear in the Immediate Pane of the VBE - the square brackets are to make it obvious if there are any leading/trailing spaces.
Debug.Print "]" & FilePath2 & "["
Set yyy = Workbooks(FilePath2) 'if this fails the workbook name in FilePath2 is not open; check the name is correct and it's open.
Set yyyy = yyy.Sheets("Sheet1") 'if this fails the sheet name Sheet1 is wrong or it doesn't exist.
Set yyyyy = yyyy.Range("F2") 'this is unlikely to fail if the code gets this far.
Set zzz = Workbooks(FilePath1) 'if this fails the workbook name in FilePath1 is not open; check the name is correct and it's open.
Set zzzz = zzz.Sheets("Sheet1") 'if this fails the sheet name is wrong or it doesn't exist.
Set zzzzz = zzzz.Range("C2") 'this is unlikely to fail if the code gets this far.
Workbooks(FilePath2).Sheets("Sheet1").Range("C2") = Workbooks(FilePath1).Sheets("Sheet1").Range("F2")
End Sub
I am getting Runtime error 9 subscript out of range. There is no debugging information given.
 
Chris, Thanks for the reply but this has to be done automatically. Some of the people using this form are lucky to fill it out let alone do a copy and paste.
If you look at the two forms in my first post you will notice that an exact copy is not possible there fore a cell copy is needed.
 
VBA Neofite said:
I have no idea how the code got messed up. There were errors in it originally when I copied it over. Here is the way it is in the Sub:<snip>
I am getting Runtime error 9 subscript out of range. There is no debugging information given.
Click to expand...
Please put your code in code tags [go Advanced, and use the # button] (it's one reason the code is messing up, the slash character is going awol):
2018-06-09_151932.jpg
Instead of just running the macro, go through it, running one line at a time by using the F8 key. Then you will know which lines have been successfully executed.
 
Separately, I note that there is a Workbook_Open event which saves the file as ThisWorkbook.Path & "" & Format(Now, "mm-dd-yyyy") & " Emergency Log"
Is this the same file that has the code you're writing now?
If so then you can forget Filepath1 and just replace it with ThisWorkbook.:
Code: 
Sub Copycell()
Dim FilePath2 As String
FilePath2 = "C:\Skywarn" & "\" & Format(Now, "dd-mm-yyyy") & " Log Summary.xls"
Debug.Print "]" & FilePath2 & "[" 'this should cause the filenames to appear in the Immediate Pane of the VBE - the square brackets are to make it obvious if there are any leading/trailing spaces.
Set yyy = Workbooks(FilePath2) 'if this fails the workbook name in FilePath2 is not open; check the name is correct and it's open.
Set yyyy = yyy.Sheets("Sheet1") 'if this fails the sheet name Sheet1 is wrong or it doesn't exist.
Set yyyyy = yyyy.Range("F2") 'this is unlikely to fail if the code gets this far.
Set zzzz = ThisWorkbook.Sheets("Sheet1") 'if this fails the sheet name is wrong or it doesn't exist.
Set zzzzz = zzzz.Range("C2") 'this is unlikely to fail if the code gets this far.
Workbooks(FilePath2).Sheets("Sheet1").Range("C2") = ThisWorkbook.Sheets("Sheet1").Range("F2")
End Sub
 
Last edited:
I am still getting Runtime error 9 subscript out of range.
When I step through the code, I get it on the first set line (set yyy). when I comment all the debugging code out I get it on the End Sub line.
Also, I get no highlighted line on the Dim line
Code: 
Sub Copycell()
Dim FilePath2 As String
FilePath2 = "C:\Skywarn" & "\" & Format(Now, "dd-mm-yyyy") & " Log Summary.xls"
'Debug.Print "]" & FilePath2 & "[" 'this should cause the filenames to appear in the Immediate Pane of the VBE - the square brackets are to make it obvious if there are any leading/trailing spaces.
'Set yyy = Workbooks(FilePath2) 'if this fails the workbook name in FilePath2 is not open; check the name is correct and it's open.'Set yyyy = yyy.Sheets("Sheet1") 'if this fails the sheet name Sheet1 is wrong or it doesn't exist.
'Set yyyyy = yyyy.Range("F2") 'this is unlikely to fail if the code gets this far.
'Set zzzz = ThisWorkbook.Sheets("Sheet1") 'if this fails the sheet name is wrong or it doesn't exist.
'Set zzzzz = zzzz.Range("C2") 'this is unlikely to fail if the code gets this far.
Workbooks(FilePath2).Sheets("Sheet1").Range("C2") = ThisWorkbook.Sheets("Sheet1").Range("F2")
End Sub
 
VBA Neofite said:
I am still getting Runtime error 9 subscript out of range.
When I step through the code, I get it on the first set line (set yyy).
Click to expand...
If that file is open then try:
FilePath2 = Format(Now, "dd-mm-yyyy") & " Log Summary.xls"


VBA Neofite said:
Also, I get no highlighted line on the Dim line
Click to expand...
That's normal.

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.

In the WorkBook_Open event you have:
Code: 
Private wbOpenEventRun As Boolean
Private Sub Workbook_Open()
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Format(Now, "mm-dd-yyyy") & " Emergency Log"
Set wkb = Workbooks.Open(Filename:="C:\Skywarn\Log Summary.xls")
ActiveWindow.WindowState = xlMinimized
End Sub
If you add one line to make it:
Code: 
Private wbOpenEventRun As Boolean
Public wkb As Workbook '<< added line.
Private Sub Workbook_Open()
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Format(Now, "mm-dd-yyyy") & " Emergency Log"
Set wkb = Workbooks.Open(Filename:="C:\Skywarn\Log Summary.xls")
ActiveWindow.WindowState = xlMinimized
End Sub
the wkb object variable should be available to code elsewheree - as long as the project hasn't been ReSet (Run dropdown menu in the VBE) (quite likely when you're debugging).
So your code might be as simple as a one-liner:
Code: 
wkb.Sheets("Sheet1").Range("C2") = Thisworkbook.Sheets("Sheet1").Range("F2")
or some such,
which should be fine as long as there's a Sheet1 in both files.
 
Last edited:
Ok, I made the following changes and no luck:
1. I changed the code in Thisworkbook to:
Code: 
Private wbOpenEventRun As Boolean
Public wkb As Workbook '<< added line.
Private Sub Workbook_Open()
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Format(Now, "mm-dd-yyyy") & " Emergency Log"
Set wkb = Workbooks.Open(Filename:="C:\Skywarn\Log Summary.xls")
ActiveWindow.WindowState = xlMinimized
End Sub

2. I then changed the code in Sheet1 to:

Code: 
Sub Copycell()
Dim FilePath2 As String
FilePath2 = Format(Now, "dd-mm-yyyy") & " Log Summary.xls"
Debug.Print "]" & FilePath2 & "[" 'this should cause the filenames to appear in the Immediate Pane of the VBE - the square brackets are to make it obvious if there are any leading/trailing spaces.
'Set yyy = Workbooks(FilePath2) 'if this fails the workbook name in FilePath2 is not open; check the name is correct and it's open.'Set yyyy = yyy.Sheets("Sheet1") 'if this fails the sheet name Sheet1 is wrong or it doesn't exist.
'Set yyyyy = yyyy.Range("F2") 'this is unlikely to fail if the code gets this far.
'Set zzzz = ThisWorkbook.Sheets("Sheet1") 'if this fails the sheet name is wrong or it doesn't exist.
'Set zzzzz = zzzz.Range("C2") 'this is unlikely to fail if the code gets this far.
Workbooks(FilePath2).Sheets("Sheet1").Range("C2") = ThisWorkbook.Sheets("Sheet1").Range("F2")
End Sub
 
Your code is in Sheet1's code module?! Then it's even simpler:
Keep your changes in (1) in your last message.
Change Copycell macro to:
Code: 
Sub Copycell()
wkb.Sheets("Sheet1").Range("C2") = Range("F2")
End Sub
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.
 
Last edited:
You must log in or register to reply here.
Back
Top