Page 4 of 4 FirstFirst ... 2 3 4
Results 31 to 38 of 38

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

  1. #31
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,465
    Articles
    0
    Excel Version
    365


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

    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

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

  3. #33
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,465
    Articles
    0
    Excel Version
    365
    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 by p45cal; 2018-06-11 at 08:17 PM.

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

  5. #35
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,465
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by VBA Neofite View Post
    Ok, I found the problem. We were sending it to Sheet1 instead of FORM.
    I was very clear on that… it was your code anyway.



    Quote Originally Posted by VBA Neofite View Post
    The 5th does not.
    Code:
    wkb.Sheets("FORM").Range("B4:B153") = Range("B4:B153")
    Application.Goto wkb.Sheets("FORM").Range("C2")
    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


    Quote Originally Posted by VBA Neofite View Post
    Also, will this run every time I make an entry?
    Nope. How will we know when an entry is complete? At the moment you have to run it manually. A button perhaps?
    Last edited by p45cal; 2018-06-11 at 10:47 PM.

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

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

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

Page 4 of 4 FirstFirst ... 2 3 4

Posting Permissions

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