Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2016
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy data from open workbook to closed workbook

    I am trying to copy data from a daily use workbook to closed master workbook. I found the code on this site. Runs w/o errors, but no data is added to the master wb.
    Sub ReceiptRegister()
    Dim wbTarget As Workbook 'destination
    Dim wbThis As Workbook 'source
    'DEFINE COPY-FROM SOURCE WORKBOOK
    Set wbThis = ActiveWorkbook 'set to the current active workbook
    'DEFINE RECEIPTS REGISTER FILE HERE
    DestinationFile = "MergeData11-11-2016.xlsm" '<=====change as needed
    DestinationWKsheetname = "Test" '<< change as needed
    DestinationFolder = "E:\My Documents\2Dextera\timer log\" '<< change as needed
    zFetch = DestinationFolder & DestinationFile 'full path and filename
    'GET REQUIRED DATA FOR THE UPDATE..
    wbThis.Activate 'switch to Invoice file
    RMN = [c2]
    DOC = [D2]
    Images = [E2]
    IndexErrors = [F2]
    ImageErrors = [g2]
    'OPEN RECEIPTS REGISTER FILE FOR UPDATING
    Set wbTarget = Workbooks.Open(zFetch) 'open file, and assign shortcut to it

    'UPDATE RECEIPTS REGISTER FILE
    wbTarget.Activate 'switch to Register file
    Sheets("Test").Select '<< specify sheet tab name for update
    r = Cells(Rows.Count, 1).End(xlUp).Row + 1 'next available row for update
    Cells(r, "B") = testRMN
    Cells(r, "C") = testDOC
    Cells(r, "D") = testImages
    Cells(r, "E") = testIndexErrors
    Cells(r, "F") = testImageErrors
    'SAVE AND CLOSE UPDATED RECEIPTS FILE
    wbTarget.Save
    wbTarget.Close
    'UPDATE STATUS TO SHOW DATA HAS BEEN POSTED
    wbThis.Activate
    saywhat = "Receipt Register has been updated"
    btns = vbOKOnly + vbExclamation 'message box buttons
    answer = MsgBox(saywhat, btns, boxtitle) 'display message box
    End Sub


    What is wrong?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Shaner,

    You are writing empty variables to your destination workbook. For example, you are writing to RMN variable but RMN is not writing to your destination file.....testRMN is. Use the same variable to write to the destination folder

    Code:
    '......
    RMN = [c2]
    DOC = [D2]
    Images = [E2]
    IndexErrors = [F2]
    ImageErrors = [g2]
    'OPEN RECEIPTS REGISTER FILE FOR UPDATING
    Set wbTarget = Workbooks.Open(zFetch) 'open file, and assign shortcut to it
    
    'UPDATE RECEIPTS REGISTER FILE
    wbTarget.Activate 'switch to Register file
    Sheets("Test").Select '<< specify sheet tab name for update
    r = Cells(Rows.Count, 1).End(xlUp).Row + 1 'next available row for update
    Cells(r, "B") = testRMN
    Cells(r, "C") = testDOC
    Cells(r, "D") = testImages
    Cells(r, "E") = testIndexErrors
    Cells(r, "F") = testImageErrors
    '.....

  3. #3
    New Lounger
    Join Date
    Nov 2016
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for getting back to me so quickly, I very much appreciate your help.
    I made some changes, but same results.
    RMN = [C2]
    DOC = [D2]
    Images = [E2]
    IndexErrors = [F2]
    ImageErrors = [G2]

    'OPEN RECEIPTS REGISTER FILE FOR UPDATING
    Set wbTarget = Workbooks.Open(zFetch) 'open file, and assign shortcut to it


    'UPDATE RECEIPTS REGISTER FILE
    wbTarget.Activate 'switch to Register file
    Sheets("Nov2016").Select '<< specify sheet tab name for update
    r = Cells(Rows.Count, 1).End(xlUp).Row + 1 'next available row for update
    Cells(r, "B") = RMN
    Cells(r, "C") = DOC
    Cells(r, "D") = Images
    Cells(r, "E") = IndexErrors
    Cells(r, "F") = ImageErrors
    I guess I do not understand what you meant about "Variables"

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Shaner,

    The changes you made were correct. The variables that were assigned values in your source book are now the same variables containing values that are used to place the values in the destination book. Please make sure that cells C2 to G2 in the source book have a value to write. Also make sure that that the correct source book is the active workbook when the code runs.

    The rest of your code is sound providing that zFetch evaluates to a valid destination file which should include the path (ex. "C:\Users\Maudibe\Desktop\test.xls") and contains a sheet called Nov2016. If either is not correct, a error will be thrown however the error may be suppressed if there is an Error Resume Next statement. The code you posted does not have such a statement but your actual code you are running may be different.

    If there is an On Error Resume Next statement, do not disable it as other parts of the routine may need it to run. Better to troubleshoot your coded by adding the following lines to your code noted in blue.

    Code:
    '....PREVIOUS CODE
    RMN = [C2]
    DOC = [D2]
    Images = [E2]
    IndexErrors = [F2]
    ImageErrors = [G2]
    'OPEN RECEIPTS REGISTER FILE FOR UPDATING
    Debug.Print zFetch
    Set wbTarget = Workbooks.Open(zFetch) 'open file, and assign shortcut to it
    
    
    'UPDATE RECEIPTS REGISTER FILE
    wbTarget.Activate 'switch to Register file
    Sheets("Nov201").Select '<< specify sheet tab name for update
    Debug.Print ActiveSheet.Name
    r = Cells(Rows.Count, 1).End(xlUp).Row + 1 'next available row for update
    Cells(r, "B") = RMN
    Cells(r, "C") = DOC
    Cells(r, "D") = Images
    Cells(r, "E") = IndexErrors
    Cells(r, "F") = ImageErrors
    End Sub
    'REST OF CODE...
    Run the code. Note in the intermediate window that the file name (path and filename) and sheet name written to it is correct. If the file name and sheet have not been written to the immediate window, then this part of the code may be circumvented by some type of conditional response or by an On Error Goto statement. In that case, your code needs to be re-evaluated at that point by cycling through the code using F-8 while noting the flow direction of the code and evaluating the values of variables (ex. zFetch) or using the watch window.

    HTH,
    Maud
    Last edited by Maudibe; 2016-11-21 at 23:53.

  5. #5
    New Lounger
    Join Date
    Nov 2016
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No data copied

    Quote Originally Posted by Maudibe View Post
    Shaner,

    The changes you made were correct. The variables that were assigned values in your source book are now the same variables containing values that are used to place the values in the destination book. Please make sure that cells C2 to G2 in the source book have a value to write. Also make sure that that the correct source book is the active workbook when the code runs.

    The rest of your code is sound providing that zFetch evaluates to a valid destination file which should include the path (ex. "C:\Users\Maudibe\Desktop\test.xls") and contains a sheet called Nov2016. If either is not correct, a error will be thrown however the error may be suppressed if there is an Error Resume Next statement. The code you posted does not have such a statement but your actual code you are running may be different.

    If there is an On Error Resume Next statement, do not disable it as other parts of the routine may need it to run. Better to troubleshoot your coded by adding the following lines to your code noted in blue.

    Code:
    '....PREVIOUS CODE
    RMN = [C2]
    DOC = [D2]
    Images = [E2]
    IndexErrors = [F2]
    ImageErrors = [G2]
    'OPEN RECEIPTS REGISTER FILE FOR UPDATING
    Debug.Print zFetch
    Set wbTarget = Workbooks.Open(zFetch) 'open file, and assign shortcut to it
    
    
    'UPDATE RECEIPTS REGISTER FILE
    wbTarget.Activate 'switch to Register file
    Sheets("Nov201").Select '<< specify sheet tab name for update
    Debug.Print ActiveSheet.Name
    r = Cells(Rows.Count, 1).End(xlUp).Row + 1 'next available row for update
    Cells(r, "B") = RMN
    Cells(r, "C") = DOC
    Cells(r, "D") = Images
    Cells(r, "E") = IndexErrors
    Cells(r, "F") = ImageErrors
    End Sub
    'REST OF CODE...
    Run the code. Note in the intermediate window that the file name (path and filename) and sheet name written to it is correct. If the file name and sheet have not been written to the immediate window, then this part of the code may be circumvented by some type of conditional response or by an On Error Goto statement. In that case, your code needs to be re-evaluated at that point by cycling through the code using F-8 while noting the flow direction of the code and evaluating the values of variables (ex. zFetch) or using the watch window.

    HTH,
    Maud
    There is still no data copied. I included the debug lines in my code. I am including the all of the code just in case I wrote something wrong in the beginning.

    Sub ReceiptRegister()
    Dim wbTarget As Workbook 'destination
    Dim wbThis As Workbook 'source
    'DEFINE COPY-FROM SOURCE WORKBOOK
    Set wbThis = ActiveWorkbook 'set to the current active workbook
    'DEFINE Source FILE HERE
    TargetFile = "MergeData11-11-2016.xlsm" '<=====change as needed Target
    SourceWKsheetname = "RandomList'<< change as needed"
    zFolder = "E:\My Documents\2Dextera\timer log\" '<< change as needed
    zFetch = zFolder & TargetFile 'full path and filename
    'GET REQUIRED DATA FOR THE UPDATE..
    wbThis.Activate 'switch to Invoice file
    RMN = [C2]
    DOC = [D2]
    Images = [E2]
    IndexErrors = [F2]
    ImageErrors = [G2]
    'OPEN RECEIPTS REGISTER FILE FOR UPDATING
    Debug.Print zFetch
    Set wbTarget = Workbooks.Open(zFetch) 'open file, and assign shortcut to it

    'UPDATE RECEIPTS REGISTER FILE
    wbTarget.Activate 'switch to Register file
    Sheets("Nov2016").Select '<< specify sheet tab name for update
    Debug.Print ActiveSheet.Name
    r = Cells(Rows.Count, 1).End(xlUp).Row + 1 'next available row for update
    Cells(r, "B") = RMN
    Cells(r, "C") = DOC
    Cells(r, "D") = Images
    Cells(r, "E") = IndexErrors
    Cells(r, "F") = ImageErrors
    'SAVE AND CLOSE UPDATED RECEIPTS FILE
    wbTarget.Save
    wbTarget.Close
    'UPDATE STATUS TO SHOW DATA HAS BEEN POSTED
    wbThis.Activate
    saywhat = "MergeData has been updated"
    btns = vbOKOnly + vbExclamation 'message box buttons
    answer = MsgBox(saywhat, btns, boxtitle) 'display message box
    End Sub

    Thanks again for your help & explaining everything.

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    Use Debug.Print to show the values for RMN etc.
    You can do the same for your variable "r" to be sure it's valid.

    cheers, Paul

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Shaner,

    When you ran the code, What did debug.print show for

    Debug.Print zFetch

    Debug.Print ActiveSheet.Name

  8. #8
    New Lounger
    Join Date
    Nov 2016
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It showed the path & file name. I moved both files up 1 folder to make a smaller character count. Then I created a new sheet inside the MergeData wb. It worked! sort of, it would only paste to row 2.

  9. #9
    New Lounger
    Join Date
    Nov 2016
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I removed the totals row & it works perfectly! Thank you Maudibe for your help. Greatly appreciated.

Posting Permissions

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