Dim ShtName As String
Dim NameOfFile As String
Dim fsoFSO
WQCDdir = "c:\New Inspection Stuff1\"
Private Sub Populate_Worksheet()
' This will be executed from a button on the "Ribbon" to create this workbook from the SDWIS-generated
' "Comprehensive Water System Report".
' Need to figure out how to add button to "Ribbon" UDF?
' What this sub does:
' if directory does not exist
' then
' create directory
' endif
' copy template worksheets to comprehensive_water_system_report workbook
' save file as CEI_PWSID_NAME_DATE.xlsm
' populate general info fields
' populate administrative contact fields (AC)
' populate designated operator contact fields (DO)
' populate operator contact fields (OP)
' populate system inventory
' loop
' facilities
' Facility flows
' endloop
On Error Resume Next
' is this needed? this worksheet is the only one in the generated report
ActiveWorkbook.Worksheets("Comprehensive Water System Repo").Activate
' turn off msgs to streamline creation process
Application.DisplayAlerts = False
'if target directory does not exist locally, then create it. Sort of housekeeping to
'create standard directory on each desktop
Set fsoFSO = CreateObject("Scripting.FileSystemObject")
If Dir(WQCDdiv, vbDirectory) = "" Then
fsoFSO.createfolder (WQCDdir)
End If
' required name of file is :"CEI_{pwsid}_{Name of system}_date"
'A4 will always contain {pwsid and name of system} as 1 group
NameOfFile = Sheets("Comprehensive Water System Repo").Range("A4").Value
'Save the workbook with defined name
ActiveWorkbook.SaveAs Filename:=(WQCDdir & "CEI_" & NameOfFile & "_" & Date$ & ".xlsm")
'Check if san survey worksheets are already in workbook.
' if so, get out, no need to re-add sheets
' if not, copy all worksheets from the blank workbook to this 'working' workbook
' For Each sh In Worksheets
' ShtName = "System Overview"
' If sh.Name Like ShtName Then
'Checks each sheet to make sure that the new sheet has not already been created
' nameTaken = True
' Exit For
' End If
' Next
' loop though and add workbooks from blank workbook
' If nameTaken = False Then
'Set variables
OrigWB = ActiveWorkbook.Name
SourceWB = "San Survey Blank.xlsm"
Workbooks.Open (WQCDdir & SourceWB)
SourceWS = "System Overview"
TargetWS = "System Overview"
ActiveSheet.Unprotect Password:="CDPHE"
'Copy WorkSheet to other WorkBook
Workbooks(SourceWB).Sheets(SourceWS).Cells.Copy _
Workbooks(OrigWB).Sheets(TargetWS).Range("A1")
' SourceWB.Close
' End If
' Workbooks.Open (OrigWB)
' Copy data from Comprehensive Water System Report to detail worksheets
ActiveWorkbook.Worksheets("System Overview").Activate
Sheets("System Overview").Select
ActiveSheet.Unprotect Password:="CDPHE"
Call Populate_General_Info
Call Populate_Administrative_Contact
Call Populate_Designated_Operator
Call Populate_Operator
Call Populate_Population_Info
ActiveSheet.Protect Password:="CDPHE"
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
' Save the workbook and worksheets
ActiveWorkbook.Save
' turn on messaging
Application.DisplayAlerts = True
On Error GoTo 0
End Sub
Private Sub Populate_General_Info()
Dim PWSID_Combo As String
Dim PWSID As String
Dim PWSName As String
Dim County_Name As String
Dim PWS_Class As String
Dim Activity_Status As String
'PWSID is always in A4
PWSID_Combo = Worksheets("Comprehensive Water System Repo").Range("A4")
PWSID = Left(PWSID_Combo, InStr(PWSID_Combo, "-") - 2)
PWSName = Right(PWSID_Combo, Len(PWSID_Combo) - InStr(PWSID_Combo, "-") - 1)
'PWSID
'This assignment gives me an error:
Worksheets("System Overview").Range("B6") = PWSID
'PWS Name
'This assignment gives me an error:
Worksheets("System Overview").Range("D6") = PWSName
'Search for "Principal County Served" and then add 1 row for county name
'County Name
'Currently hard-coded for City of Durango; need to be variable based on "Principal County Served"
Worksheets("Comprehensive Water System Repo").Range("BQ4").Copy _
Destination:=Worksheets("System Overview").Range("F6")
'Search for "Fed Type" and then add 3 rows for PWS Classification
'PWS Classification
'Currently hard-coded for City of Durango;
Worksheets("Comprehensive Water System Repo").Range("AO6").Copy _
Destination:=Worksheets("System Overview").Range("B7")
'Search for "Water System Status" and then add 1 row for Activity Status
'Activity Status
'Currently hard-coded for City of Durango;
Worksheets("Comprehensive Water System Repo").Range("AC4").Copy _
Destination:=Worksheets("System Overview").Range("D7")
End Sub
Private Sub Populate_Administrative_Contact()
' Search for "Contacts" in column B, save row number
' Search for "Facilities" in colum B, save row number
' search for "AC" (Administrative Contact) between "Contacts" row and "Factilites" row
' Get the line number of AC in column B
' As each field is merged cells, count # of merged cells and offset for next data
' must figure out "Address 2" problem
'Currently hard-coded for City of Durango
'Administrative Contact Name
Worksheets("Comprehensive Water System Repo").Range("K22").Copy _
Destination:=Worksheets("System Overview").Range("F10")
'Administrative Contact Address 1
Worksheets("Comprehensive Water System Repo").Range("AA22").Copy _
Destination:=Worksheets("System Overview").Range("F11")
'Administrative Contact Address 2
Worksheets("Comprehensive Water System Repo").Range("BH22").Copy _
Destination:=Worksheets("System Overview").Range("F12")
'Administrative Contact City
Worksheets("Comprehensive Water System Repo").Range("BW22").Copy _
Destination:=Worksheets("System Overview").Range("F13")
'Administrative Contact State
Worksheets("Comprehensive Water System Repo").Range("CM22").Copy _
Destination:=Worksheets("System Overview").Range("F14")
'Administrative Contact ZIP
Worksheets("Comprehensive Water System Repo").Range("CP22").Copy _
Destination:=Worksheets("System Overview").Range("F15")
'Administrative Contact Phone
Worksheets("Comprehensive Water System Repo").Range("CY22").Copy _
Destination:=Worksheets("System Overview").Range("F16")
End Sub
Private Sub Populate_Designated_Operator()
' Search for "Contacts" in column B, save row number
' Search for "Facilities" in colum B, save row number
' search for "DO" (Designated Operator) between "Contacts" row and "Factilites" row
' Get the line number of AC in column B
' As each field is merged cells, count # of merged cells and offset for next data
' must figure out "Address 2" problem
'Currently hard-coded for City of Durango
' Get the line number of DO in column B
'Designated Operator Contact Name
Worksheets("Comprehensive Water System Repo").Range("K24").Copy _
Destination:=Worksheets("System Overview").Range("B10")
'Designated Operator Contact Address 1
Worksheets("Comprehensive Water System Repo").Range("AA24").Copy _
Destination:=Worksheets("System Overview").Range("B11")
'Designated Operator Address 2
Worksheets("Comprehensive Water System Repo").Range("BG24").Copy _
Destination:=Worksheets("System Overview").Range("B12")
'Designated Operator City
Worksheets("Comprehensive Water System Repo").Range("BW24").Copy _
Destination:=Worksheets("System Overview").Range("B13")
'Designated Operator State
Worksheets("Comprehensive Water System Repo").Range("CM24").Copy _
Destination:=Worksheets("System Overview").Range("B14")
'Designated Operator ZIP
Worksheets("Comprehensive Water System Repo").Range("CP24").Copy _
Destination:=Worksheets("System Overview").Range("B15")
'Designated Operator Phone
Worksheets("Comprehensive Water System Repo").Range("CY24").Copy _
Destination:=Worksheets("System Overview").Range("B16")
End Sub
Private Sub Populate_Operator()
' Search for "Contacts" in column B, save row number
' Search for "Facilities" in colum B, save row number
' search for "OP" (Operator Contact) between "Contacts" row and "Factilites" row
' Get the line number of AC in column B
' As each field is merged cells, count # of merged cells and offset for next data
' must figure out "Address 2" problem
'Currently hard-coded for City of Durango
' Get the line number of OP in column B
'Operator Contact Name
Worksheets("Comprehensive Water System Repo").Range("K28").Copy _
Destination:=Worksheets("System Overview").Range("D10")
'Operator Contact Address 1
Worksheets("Comprehensive Water System Repo").Range("AA28").Copy _
Destination:=Worksheets("System Overview").Range("D11")
'Operator Address 2
Worksheets("Comprehensive Water System Repo").Range("BG28").Copy _
Destination:=Worksheets("System Overview").Range("D12")
'Operator City
Worksheets("Comprehensive Water System Repo").Range("BW28").Copy _
Destination:=Worksheets("System Overview").Range("D13")
'Operator State
Worksheets("Comprehensive Water System Repo").Range("CM28").Copy _
Destination:=Worksheets("System Overview").Range("D14")
'Operator ZIP
Worksheets("Comprehensive Water System Repo").Range("CP28").Copy _
Destination:=Worksheets("System Overview").Range("D15")
'Operator Phone
Worksheets("Comprehensive Water System Repo").Range("CY28").Copy _
Destination:=Worksheets("System Overview").Range("D16")
End Sub
Private Sub Populate_Population_Info()
'Search for "Population Type" and then add 1 row for Type of Population
' "R" - Residential
' "T" - Transient
' "N" - Non-transient
' figure # of cells merged
' offset # cells and add 1 row for population
' offset -13 to get Operating Period
'Currently hard-coded for City of Durango;
'Resident Population
Worksheets("Comprehensive Water System Repo").Range("DE7").Copy _
Destination:=Worksheets("System Overview").Range("B20")
'Resident Operating Period
Worksheets("Comprehensive Water System Repo").Range("CH6").Copy _
Destination:=Worksheets("System Overview").Range("D20")
'Transient Population
Worksheets("Comprehensive Water System Repo").Range("DE9").Copy _
Destination:=Worksheets("System Overview").Range("B21")
'Transient Operating Period
Worksheets("Comprehensive Water System Repo").Range("CH8").Copy _
Destination:=Worksheets("System Overview").Range("D21")
'Non-Transient Population
Worksheets("Comprehensive Water System Repo").Range("DE11").Copy _
Destination:=Worksheets("System Overview").Range("B22")
'Non-Transient Operating Period
Worksheets("Comprehensive Water System Repo").Range("CH10").Copy _
Destination:=Worksheets("System Overview").Range("D22")
End Sub
[code]
I don't know how to add copies of my source "Comprehensive Water System Report" or target worksheets.
Thank you for your help.
P.S. Is VBA for Dummies a good book to get to learn or continue? If not, which book would be best?