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?
Bookmarks