Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 38

Thread: Copy one cell from a workbook sheet to another workbook sheet

  1. #1
    Acolyte VBA Neofite's Avatar
    Join Date
    Apr 2018
    Location
    Mather, PA USA
    Posts
    23
    Articles
    0
    Excel Version
    2007

    Copy one cell from a workbook sheet to another workbook sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Acolyte VBA Neofite's Avatar
    Join Date
    Apr 2018
    Location
    Mather, PA USA
    Posts
    23
    Articles
    0
    Excel Version
    2007
    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

  3. #3
    Acolyte VBA Neofite's Avatar
    Join Date
    Apr 2018
    Location
    Mather, PA USA
    Posts
    23
    Articles
    0
    Excel Version
    2007
    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 p45cal; 2018-06-06 at 03:36 PM. Reason: added code tags

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,398
    Articles
    0
    Excel Version
    365
    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....

  5. #5
    Acolyte VBA Neofite's Avatar
    Join Date
    Apr 2018
    Location
    Mather, PA USA
    Posts
    23
    Articles
    0
    Excel Version
    2007
    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


  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,398
    Articles
    0
    Excel Version
    365
    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 by p45cal; 2018-06-07 at 01:59 PM.

  7. #7
    Acolyte VBA Neofite's Avatar
    Join Date
    Apr 2018
    Location
    Mather, PA USA
    Posts
    23
    Articles
    0
    Excel Version
    2007
    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.

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,398
    Articles
    0
    Excel Version
    365
    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.

  9. #9
    Acolyte VBA Neofite's Avatar
    Join Date
    Apr 2018
    Location
    Mather, PA USA
    Posts
    23
    Articles
    0
    Excel Version
    2007
    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 p45cal; 2018-06-07 at 04:46 PM. Reason: added code tags

  10. #10
    Acolyte VBA Neofite's Avatar
    Join Date
    Apr 2018
    Location
    Mather, PA USA
    Posts
    23
    Articles
    0
    Excel Version
    2007
    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 p45cal; 2018-06-07 at 04:45 PM. Reason: added code tags

Page 1 of 4 1 2 3 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •