+ Reply to Thread
Results 1 to 19 of 19

Copy contents from "Child" file to a "Master"

Hybrid View

  1. #1
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Copy contents from "Child" file to a "Master"

    Dear All,

    I hope you are all well.

    I want to ask your advise, if possible, on a code I have.

    Basically, the code copy contents from 4 "Child" files to a "Mater" file.

    Everything works smoothly but no data is being copied from the "Child" files.

    Is there something wrong on the code:

    Sub Update_Info_From_Childs()
    
    Application.ScreenUpdating = False 'prevents screen flashing through sheets during code
    
    'WorkBooks
    Dim iRowNom As Long
    Dim iRowMon As Long
    Dim iRowSug As Long
    Dim Response As VbMsgBoxResult
    Dim LR As Long
    Dim wbMaster As Workbook
    Dim wbHana As Workbook
    Dim wbMob As Workbook
    Dim wbCcm As Workbook
    Dim wbTma As Workbook
    
    'Setting Worksheets
    Dim wsMasterNom As Worksheet 'Master
    Dim wsMasterMon As Worksheet 'Master
    Dim wsMasterSug As Worksheet 'Master
    Dim wsHanaNom As Worksheet 'HANA
    Dim wsHanaMon As Worksheet 'HANA
    Dim wsHanaSug As Worksheet 'HANA
    Dim wsMobNom As Worksheet 'Mobility
    Dim wsMobMon As Worksheet 'Mobility
    Dim wsMobSug As Worksheet 'Mobility
    Dim wsCcmNom As Worksheet 'CCM
    Dim wsCcmMon As Worksheet 'CCM
    Dim wsCcmSug As Worksheet 'CCM
    Dim wsTmaNom As Worksheet 'TMA
    Dim wsTmaMon As Worksheet 'TMA
    Dim wsTmaSug As Worksheet 'TMA
    
    'Setting Worksheets and Workbooks locations
    Set wbMaster = Workbooks("Master Dashboard.xlsm") 'Master
    Set wsMasterNom = wbMaster.Sheets("Customer Nomination") 'Master
    Set wsMasterMon = wbMaster.Sheets("Customer Monitoring") 'Master
    Set wsMasterSug = wbMaster.Sheets("Improvement Suggestions") 'Master
    Set wbHana = Workbooks.Open(Filename:="C:\HANA VM DashBoard.xlsm") 'HANA
    Set wsHanaNom = wbHana.Sheets("Customer Nomination") 'HANA
    Set wsHanaMon = wbHana.Sheets("Customer Monitoring") 'HANA
    Set wsHanaSug = wbHana.Sheets("Improvement Suggestions") 'HANA
    Set wbMob = Workbooks.Open(Filename:="C:\Mobility VM DashBoard.xlsm") 'Mobility
    Set wsMobNom = wbMob.Sheets("Customer Nomination") 'Mobility
    Set wsMobMon = wbMob.Sheets("Customer Monitoring") 'Mobility
    Set wsMobSug = wbMob.Sheets("Improvement Suggestions") 'Mobility
    Set wbCcm = Workbooks.Open(Filename:="C:\CCM VM DashBoard.xlsm") 'CCM
    Set wsCcmNom = wbCcm.Sheets("Customer Nomination") 'CCM
    Set wsCcmMon = wbCcm.Sheets("Customer Monitoring") 'CCM
    Set wsCcmSug = wbCcm.Sheets("Improvement Suggestions") 'CCM
    Set wbTma = Workbooks.Open(Filename:="C:\TMA VM DashBoard.xlsm") 'TMA
    Set wsTmaNom = wbTma.Sheets("Customer Nomination") 'TMA
    Set wsTmaMon = wbTma.Sheets("Customer Monitoring") 'TMA
    Set wsTmaSug = wbTma.Sheets("Improvement Suggestions") 'TMA
    
    
    
    wsMasterNom.Unprotect Password:=""
    wsMasterMon.Unprotect Password:=""
    wsMasterSug.Unprotect Password:=""
    wsHanaNom.Unprotect Password:=""
    wsHanaMon.Unprotect Password:=""
    wsHanaSug.Unprotect Password:=""
    wsMobNom.Unprotect Password:=""
    wsMobMon.Unprotect Password:=""
    wsMobSug.Unprotect Password:=""
    wsCcmNom.Unprotect Password:=""
    wsCcmMon.Unprotect Password:=""
    wsCcmSug.Unprotect Password:=""
    wsTmaNom.Unprotect Password:=""
    wsTmaMon.Unprotect Password:=""
    wsTmaSug.Unprotect Password:=""
    
    'Delete info from Master "Customer Nomination" sheet
    'wsMasterNom.Select
    'Range("J12:O511").Select
    'Selection.ClearContents
    'Delete info from Master "Customer Monitoring" sheet
    'wsMasterNom.Select
    'Range("J12:R511").Select
    'Selection.ClearContents
    
    'Finds first empty row in Master's "Customer Nomination" table
    iRowNom = wsMasterNom.Range("J" & Rows.count).End(xlUp).Row + 1
    
    'Copy "Customer Nomination" from Child (HANA) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsHanaNom.Range("AH12:AH512") 'VM
    wsMasterNom.Cells(iRowMon, 11).Value = wsHanaNom.Range("J12:J511") 'Customer Name
    wsMasterNom.Cells(iRowMon, 12).Value = wsHanaNom.Range("K12:K511") 'Customer Number
    wsMasterNom.Cells(iRowMon, 13).Value = wsHanaNom.Range("M12:M511") 'Country
    wsMasterNom.Cells(iRowMon, 14).Value = wsHanaNom.Range("N12:N511") 'Region
    wsMasterNom.Cells(iRowMon, 15).Value = wsHanaNom.Range("Y12:Z511") 'Dedicated ESA
    'Copy "Customer Nomination" from Child (Mobility) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsMobNom.Range("AH12:AH511") 'VM
    wsMasterNom.Cells(iRowMon, 11).Value = wsMobNom.Range("J12:K511") 'Customer Name
    wsMasterNom.Cells(iRowMon, 12).Value = wsMobNom.Range("K12:L511") 'Customer Number
    wsMasterNom.Cells(iRowMon, 13).Value = wsMobNom.Range("M12:N511") 'Country
    wsMasterNom.Cells(iRowMon, 14).Value = wsMobNom.Range("N12:O511") 'Region
    wsMasterNom.Cells(iRowMon, 15).Value = wsMobNom.Range("Y12:Z511") 'Dedicated ESA
    'Copy "Customer Nomination" from Child (CCM) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsCcmNom.Range("AH12:AH511") 'VM
    wsMasterNom.Cells(iRowMon, 11).Value = wsCcmNom.Range("J12:K511") 'Customer Name
    wsMasterNom.Cells(iRowMon, 12).Value = wsCcmNom.Range("K12:L511") 'Customer Number
    wsMasterNom.Cells(iRowMon, 13).Value = wsCcmNom.Range("M12:N511") 'Country
    wsMasterNom.Cells(iRowMon, 14).Value = wsCcmNom.Range("N12:O511") 'Region
    wsMasterNom.Cells(iRowMon, 15).Value = wsCcmNom.Range("Y12:Z511") 'Dedicated ESA
    'Copy "Customer Nomination" from Child (TMA) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsTmaNom.Range("AH12:AH511") 'VM
    wsMasterNom.Cells(iRowMon, 11).Value = wsTmaNom.Range("J12:K511") 'Customer Name
    wsMasterNom.Cells(iRowMon, 12).Value = wsTmaNom.Range("K12:L511") 'Customer Number
    wsMasterNom.Cells(iRowMon, 13).Value = wsTmaNom.Range("M12:N511") 'Country
    wsMasterNom.Cells(iRowMon, 14).Value = wsTmaNom.Range("N12:O511") 'Region
    wsMasterNom.Cells(iRowMon, 15).Value = wsTmaNom.Range("Y12:Z511") 'Dedicated ESA
    
    'Finds first empty row in Master's "Customer Monitoring" table
    iRowMon = wsMasterMon.Range("J" & Rows.count).End(xlUp).Row + 1
    
    'Copy "Customer Monitoring" from Child (HANA) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsHanaMon.Range("CH12:CH511") 'VM
    wsMasterNom.Cells(iRowMon, 11).Value = wsHanaMon.Range("J12:J511") 'Customer Name
    wsMasterNom.Cells(iRowMon, 12).Value = wsHanaMon.Range("L12:L511") 'Dedicated ESA
    wsMasterNom.Cells(iRowMon, 13).Value = wsHanaMon.Range("M12:M511") 'Customer Number
    wsMasterNom.Cells(iRowMon, 14).Value = wsHanaMon.Range("O12:O511") 'Country
    wsMasterNom.Cells(iRowMon, 15).Value = wsHanaMon.Range("P12:P511") 'Region
    wsMasterNom.Cells(iRowMon, 16).Value = wsHanaMon.Range("AF12:AF511") 'EGIs
    wsMasterNom.Cells(iRowMon, 17).Value = wsHanaMon.Range("BA12:BA511") 'MTEs
    wsMasterNom.Cells(iRowMon, 18).Value = wsHanaMon.Range("CG12:CG511") 'Pilot Objective Met
    'Copy "Customer Monitoring" from Child (Mobility) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsMobMon.Range("CH12:CH511") 'VM
    wsMasterNom.Cells(iRowMon, 11).Value = wsMobMon.Range("J12:J511") 'Customer Name
    wsMasterNom.Cells(iRowMon, 12).Value = wsMobMon.Range("L12:L511") 'Dedicated ESA
    wsMasterNom.Cells(iRowMon, 13).Value = wsMobMon.Range("M12:M511") 'Customer Number
    wsMasterNom.Cells(iRowMon, 14).Value = wsMobMon.Range("O12:O511") 'Country
    wsMasterNom.Cells(iRowMon, 15).Value = wsMobMon.Range("P12:P511") 'Region
    wsMasterNom.Cells(iRowMon, 16).Value = wsMobMon.Range("AF12:AF511") 'EGIs
    wsMasterNom.Cells(iRowMon, 17).Value = wsMobMon.Range("BA12:BA511") 'MTEs
    wsMasterNom.Cells(iRowMon, 18).Value = wsMobMon.Range("CG12:CG511") 'Pilot Objective Met
    'Copy "Customer Monitoring" from Child (CCM) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsCcmMon.Range("CH12:CH511") 'VM
    wsMasterNom.Cells(iRowMon, 11).Value = wsCcmMon.Range("J12:J511") 'Customer Name
    wsMasterNom.Cells(iRowMon, 12).Value = wsCcmMon.Range("L12:L511") 'Dedicated ESA
    wsMasterNom.Cells(iRowMon, 13).Value = wsCcmMon.Range("M12:M511") 'Customer Number
    wsMasterNom.Cells(iRowMon, 14).Value = wsCcmMon.Range("O12:O511") 'Country
    wsMasterNom.Cells(iRowMon, 15).Value = wsCcmMon.Range("P12:P511") 'Region
    wsMasterNom.Cells(iRowMon, 16).Value = wsCcmMon.Range("AF12:AF511") 'EGIs
    wsMasterNom.Cells(iRowMon, 17).Value = wsCcmMon.Range("BA12:BA511") 'MTEs
    wsMasterNom.Cells(iRowMon, 18).Value = wsCcmMon.Range("CG12:CG511") 'Pilot Objective Met
    'Copy "Customer Monitoring" from Child (TMA) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsTmaMon.Range("CH12:CH511") 'VM
    wsMasterNom.Cells(iRowMon, 11).Value = wsTmaMon.Range("J12:J511") 'Customer Name
    wsMasterNom.Cells(iRowMon, 12).Value = wsTmaMon.Range("L12:L511") 'Dedicated ESA
    wsMasterNom.Cells(iRowMon, 13).Value = wsTmaMon.Range("M12:M511") 'Customer Number
    wsMasterNom.Cells(iRowMon, 14).Value = wsTmaMon.Range("O12:O511") 'Country
    wsMasterNom.Cells(iRowMon, 15).Value = wsTmaMon.Range("P12:P511") 'Region
    wsMasterNom.Cells(iRowMon, 16).Value = wsTmaMon.Range("AF12:AF511") 'EGIs
    wsMasterNom.Cells(iRowMon, 17).Value = wsTmaMon.Range("BA12:BA511") 'MTEs
    wsMasterNom.Cells(iRowMon, 18).Value = wsTmaMon.Range("CG12:CG511") 'Pilot Objective Met
    
    'Finds first empty row in Master's "Improvement Suggestions" table
    iRowSug = wsMasterSug.Range("J" & Rows.count).End(xlUp).Row + 1
    
    'Copy "Improvement Suggestions" from Child (HANA) to Master
    wsMasterSug.Cells(iRowSug, 10).Value = wsHanaSug.Range("J12:J511") 'Classification
    wsMasterSug.Cells(iRowSug, 11).Value = wsHanaSug.Range("K12:K511") 'Improvement Suggestions
    wsMasterSug.Cells(iRowSug, 10).Value = wsHanaSug.Range("L12:L511") 'Feedback Received from:
    'Copy "Improvement Suggestions" from Child (Mobility) to Master
    wsMasterSug.Cells(iRowSug, 10).Value = wsMobSug.Range("J12:J511") 'Classification
    wsMasterSug.Cells(iRowSug, 11).Value = wsMobSug.Range("K12:K511") 'Improvement Suggestions
    wsMasterSug.Cells(iRowSug, 10).Value = wsMobSug.Range("L12:L511") 'Feedback Received from:
    'Copy "Improvement Suggestions" from Child (CCM) to Master
    wsMasterSug.Cells(iRowSug, 10).Value = wsCcmSug.Range("J12:J511") 'Classification
    wsMasterSug.Cells(iRowSug, 11).Value = wsCcmSug.Range("K12:K511") 'Improvement Suggestions
    wsMasterSug.Cells(iRowSug, 10).Value = wsCcmSug.Range("L12:L511") 'Feedback Received from:
    'Copy "Improvement Suggestions" from Child (TMA) to Master
    wsMasterSug.Cells(iRowSug, 10).Value = wsTmaSug.Range("J12:J511") 'Classification
    wsMasterSug.Cells(iRowSug, 11).Value = wsTmaSug.Range("K12:K511") 'Improvement Suggestions
    wsMasterSug.Cells(iRowSug, 10).Value = wsTmaSug.Range("L12:L511") 'Feedback Received from:
    
    Response = MsgBox("Data copied successfully!", vbInformation, "Data from Child")
    
    wsMasterNom.Protect Password:=""
    wsMasterMon.Protect Password:=""
    wsMasterSug.Protect Password:=""
    wsHanaNom.Protect Password:=""
    wsHanaMon.Protect Password:=""
    wsHanaSug.Protect Password:=""
    wsMobNom.Protect Password:=""
    wsMobMon.Protect Password:=""
    wsMobSug.Protect Password:=""
    wsCcmNom.Protect Password:=""
    wsCcmMon.Protect Password:=""
    wsCcmSug.Protect Password:=""
    wsTmaNom.Protect Password:=""
    wsTmaMon.Protect Password:=""
    wsTmaSug.Protect Password:=""
    
    wbHana.Close True
    wbMob.Close True
    wbCcm.Close True
    wbTma.Close True
    Set wbHana = Nothing
    Set wbMob = Nothing
    Set wbCcm = Nothing
    Set wbTma = Nothing
    Set wsMasterNom = Nothing
    Set wsMasterMon = Nothing
    Set wsMasterSug = Nothing
    Set wsHanaNom = Nothing
    Set wsHanaMon = Nothing
    Set wsHanaSug = Nothing
    Set wsMobNom = Nothing
    Set wsMobMon = Nothing
    Set wsMobSug = Nothing
    Set wsCcmNom = Nothing
    Set wsCcmMon = Nothing
    Set wsCcmSug = Nothing
    Set wsTmaNom = Nothing
    Set wsTmaMon = Nothing
    Set wsTmaSug = Nothing
    
    Application.ScreenUpdating = True
    
    End Sub
    The other thing I've noticed is that if a "Child" file is open, I cannot run the code. Is there a way that even a "Child" file is open, the code can copy it's contents on the background?

    Your assistance on this matter will be highly appreciated.

    Best regards,
    Filipe
    Last edited by pipoliveira; 02-12-2014 at 09:11 AM.

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Copy contents from "Child" file to a "Master"

    It look that you have problem in these rows:

    wsMasterNom.Cells(iRowMon, 10).Value = wsHanaNom.Range("AH12:AH512") 'VM
    and all of that type after this one

    you try to put the value of some 501 cells in a single cell

    it should be

    wsMasterNom.Cells(iRowMon, 10).Resize(501,1).Value = wsHanaNom.Range("AH12:AH512").Value 'VM
    Note that you copy different number of rows 500 or 501.

    The second problem - you will get an error if the file is already open, so you should check if file is open before trying to open it
    Last edited by buran; 02-12-2014 at 09:26 AM.
    If you are pleased with a member's answer then use the Star icon to rate it.

  3. #3
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Re: Copy contents from "Child" file to a "Master"

    Hi buran,

    Thank you for your prompt reply.

    I've changed the code as you've mentioned above but I still cannot have any data copied to the "Master" file and there's no errors on the code either. There must be something that is not right.

    As for the second problem, is there a way to copy the contents of a "Child" file even it's open?

    Many thanks in advance and kind regards,
    Filipe

  4. #4
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Copy contents from "Child" file to a "Master"

    Hm, it's hard without the files, but one more thing - note that you copy data from Monitoring to Master Nomination. Is that right?
    Regarding the second problem - my understanding is that you got error when you try to run the code and it try to open already open file. Is that correct?

  5. #5
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Re: Copy contents from "Child" file to a "Master"

    Hi buran,

    There is a Master file with extension "Master Dashboard" and the Child files. The master and child files have the same worksheet names ("Customer Nomination"; "Customer Monitoring" & "Improvement Suggestions").

    The only thing that is not the same between Master and Child files is the contents because the Master file only have the required information and not all of it.

    Regarding the second problem:

    The "Child" files will be stored on a shared drive that can be accessed by employees from different locations, so we can not tell who's using the file. If there was a peace of code that could say to copy contents even the file is open, that could be great.

    Please let me know if you need further information.

    Best regards,
    Filipe.

  6. #6
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Copy contents from "Child" file to a "Master"

    'Finds first empty row in Master's "Customer Monitoring" table
    iRowMon = wsMasterMon.Range("J" & Rows.count).End(xlUp).Row + 1
    
    'Copy "Customer Monitoring" from Child (HANA) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsHanaMon.Range("CH12:CH511") 'VM
    You check for the last row in wsMasterMon and then you copy data to wsMasterNom

    I think that's not right.

    Regarding the second question - my impression was that the file is already open on the same pc. if it's on a shared drive and some one has already open the file, it should open in read only mode. if it doesn't try to add ReadOnly:=True to the Opne method. Also maybe there is some policy in place that prevents the file from opening if its already open?

  7. #7
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Copy contents from "Child" file to a "Master"

    'Finds first empty row in Master's "Customer Monitoring" table
    iRowMon = wsMasterMon.Range("J" & Rows.count).End(xlUp).Row + 1
    
    'Copy "Customer Monitoring" from Child (HANA) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsHanaMon.Range("CH12:CH511") 'VM
    You check for the last row in wsMasterMon and then you copy data to wsMasterNom

    I think that's not right.

    Regarding the second question - my impression was that the file is already open on the same pc. if it's on a shared drive and some one has already open the file, it should open in read only mode. if it doesn't try to add ReadOnly:=True to the Opne method. Also maybe there is some policy in place that prevents the file from opening if its already open?

  8. #8
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Copy contents from "Child" file to a "Master"

    sorry for duplicate post, there was some problem while posting

  9. #9
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Re: Copy contents from "Child" file to a "Master"

    Let me try to explain.

    As the Master file will have the contents of all 4 Child files, I assumed that I need to say in VBA to compile all Child information in the Master one by one or else it might replace values instead of maintaining all Child files information, one after the other.

    I did something similar that worked real fine, but that one was only a Master and 1 Child. It copies the contents from one to the other. The difference is that the code copies all table from Child to Master.

    Here's the code I used for that file and works perfectly:
    Sub Update_File()
    
    Application.ScreenUpdating = False 'prevents screen flashing through sheets during code
    
        If MsgBox("Are you sure you want to update the file for Management now?", vbYesNo + vbQuestion, "Update file to Management") = vbNo Then End
    
        Dim wbNew As Workbook
        Dim wsNew As Worksheet
        Dim wsNew2 As Worksheet
        Dim wsNew3 As Worksheet
        Dim wbOld As Workbook
        Dim wsOld As Worksheet
        Dim wsOld2 As Worksheet
        Dim WsOld3 As Worksheet
        
        Set wbOld = Workbooks("Remote Services Administration 2013.xlsm")
        Set wsOld = wbOld.Sheets("Engagements")
        Set wsOld2 = wbOld.Sheets("Engagements II")
        Set WsOld3 = wbOld.Sheets("Booking Overview")
        Set wbNew = Workbooks.Open(Filename:="C:\Employee Workload Tool 2013.xlsx")
        Set wsNew = wbNew.Sheets("Engagements")
        Set wsNew2 = wbNew.Sheets("Engagements II")
        Set wsNew3 = wbNew.Sheets("Booking Overview")
        
        'Unprotect Sheets
        Windows("Employee Workload Tool 2013.xlsx").Activate
        Sheets("Engagements").Select
        ActiveSheet.Unprotect Password:="155249"
        Sheets("Engagements II").Select
        ActiveSheet.Unprotect Password:="155249"
        Sheets("Booking Overview").Select
        ActiveSheet.Unprotect Password:="155249"
    
        'Copy/Paste Values
        wsNew.Range("H20:CO51").Value = wsOld.Range("H20:CO51").Value 'Engagements Sheet (Dedicated)
        wsNew.Range("H63:CO94").Value = wsOld.Range("H63:CO94").Value 'Engagements Sheet (Net-New):
        wsNew2.Range("H20:CO51").Value = wsOld2.Range("H20:CO51").Value 'Engagements II (Dedicated Light / Temporary):
        wsNew2.Range("H63:CQ163").Value = wsOld2.Range("H63:CQ163").Value 'Engagements II (Reactive / On-Demand):
        wsNew3.Range("H19:AR128").Value = WsOld3.Range("H19:AR128").Value 'Booking Overview:
        
        'Protect Sheets Back
        Windows("Employee Workload Tool 2013.xlsx").Activate
        Sheets("Engagements").Select
        ActiveSheet.Protect Password:="155249"
        Sheets("Engagements II").Select
        ActiveSheet.Protect Password:="155249"
        Sheets("Booking Overview").Select
        ActiveSheet.Protect Password:="155249"
        
        'Set Sheet to "Home"
        Sheets("Home").Select
    
        'Close or Open file
        If MsgBox("Data Uploaded successfully!" & vbCrLf & vbCrLf & _
        "Do you want to open the Management Workbook file?", vbYesNo + vbQuestion, "Update file to Management") = vbYes Then End
    
        'Closing "Employee Workload Tool 2013.xlsx"
        wbNew.Close True
        Set wsNew = Nothing
        Set wsNew2 = Nothing
        Set wsNew3 = Nothing
        Set wbNew = Nothing
        Set wsOld = Nothing
        Set wsOld2 = Nothing
        Set WsOld3 = Nothing
        Set wbOld = Nothing
    
    End Sub
    On this new file I am creating, I need to have all childs info from each sheet to the Master. The problem might be that the "Child" table on each sheet contains about 25 columns and on the Master, I only need about 5 or 9 depending on the sheet and the headers are not in the same order as the "Child" files.

    Please let me know if you need further information.

    I can try to make a Demo file if you think that will help.

    Many thanks in advance for checking this up.

    Best regards,
    Filipe
    Last edited by pipoliveira; 02-12-2014 at 10:45 AM.

  10. #10
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Re: Copy contents from "Child" file to a "Master"

    Sorry buran,

    You are right,

    On that line I should have:
    wsMasterMon.Cells(iRowMon, 10).Resize(501, 1).Value = wsHanaMon.Range("CH12:CH511").Value 'VM
    But still it does not copy anything.

    Any ideas will be highly appreciated.

    Best regards,
    Filipe

  11. #11
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Copy contents from "Child" file to a "Master"

    I'm looking at your code and I noticed something else

    Look at this code

    'Finds first empty row in Master's "Customer Nomination" table
    iRowNom = wsMasterNom.Range("J" & Rows.count).End(xlUp).Row + 1
    
    'Copy "Customer Nomination" from Child (HANA) to Master
    wsMasterNom.Cells(iRowMon, 10).Value = wsHanaNom.Range("AH12:AH512") 'VM
    iRowNom is the last row in wsMasterNom. after that you copy values to wsMasterNom.Cells(iRowMon, 10). I don't see where in the code up to that point variable iRowMon has been initialized. That code should rise an error, so it's not possible that *Everything works smoothly but no data is being copied from the "Child" files.*

  12. #12
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Re: Copy contents from "Child" file to a "Master"

    That was already changed, now it's:
    'Finds first empty row in Master's "Customer Nomination" table
    iRowNom = wsMasterNom.Range("J" & Rows.count).End(xlUp).Row + 1
    
    'Copy "Customer Nomination" from Child (HANA) to Master
    wsMasterNom.Cells(iRowNom, 10).Resize(501, 1).Value = wsHanaNom.Range("AH12:AH511").Value 'VM
    Honestly, when the code was as I posted, I did not received any errors and the code ran till the end as all "Child" files closed and I did received the MsgBox text set at the end of it.

    I might try the ".Copy" instead of ".Value" for the child range and ".PasteSpecial" for the "Master" to see what happens.

    Best regards,
    Filipe

  13. #13
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Copy contents from "Child" file to a "Master"

    well. I give up

  14. #14
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Re: Copy contents from "Child" file to a "Master"

    No worries,

    Thanks a million for trying.

    Best regards,
    Filipe.

  15. #15
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Copy contents from "Child" file to a "Master"

    One more thing:

    'Copy "Improvement Suggestions" from Child (HANA) to Master
    wsMasterSug.Cells(iRowSug, 10).Value = wsHanaSug.Range("J12:J511") 'Classification
    wsMasterSug.Cells(iRowSug, 11).Value = wsHanaSug.Range("K12:K511") 'Improvement Suggestions
    wsMasterSug.Cells(iRowSug, 10).Value = wsHanaSug.Range("L12:L511") 'Feedback Received from:
    It looks like that you overwrite data from J12:J511 with data from L12:L511 both are copied to column 10, starting iRowSug

  16. #16
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Re: Copy contents from "Child" file to a "Master"

    Thanks buran,

    I did noticed that error previously and I have changed it

    Thanks a million.

  17. #17
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Re: Copy contents from "Child" file to a "Master"

    Hi buran,

    I thought you might be interested to know.

    I have solved the problem.

    Apparently, I copied/paste the following statement to each different "Child" file:
     iRowNom = wsMasterNom.Range("J" & Rows.count).End(xlUp).Row + 1
    I don't know why but it worked. Now it updates all "Child" files to the "Master".

    Now I have a doubt. This code was created on a module to test it but I would like to have it on the "Workbook_Open" event.

    The thing I need is to avoid duplication. Let's say that I need a code that focus on Column "J" for duplication.

    "J" stands for "Customer Number" header and there shouldn't be more than 1 data for each customer.

    What will be the best peace of code to use?

    Your assistance on this matter will be highly appreciated.

    Best regards,
    Filipe

  18. #18
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Copy contents from "Child" file to a "Master"

    sorry, not able to help with this

  19. #19
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Re: Copy contents from "Child" file to a "Master"

    No worries,

    Thanks a million.

    The problem of this thread was finished so I will maintain this as Solved.

    Thanks a million,
    Filipe

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. Replies: 1
    Last Post: 06-06-2013, 06:28 PM
  4. MSoffice Outlook 2003 message to be saved in C dir with the file name "From" "Sent" "Sub"
    By shailendra0509 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-09-2012, 01:32 AM
  5. How can i copy value from "HTMLText"(EMBED("Forms.HTML:Text","")),using Macro
    By andrewyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2010, 12:47 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1