Hi,
I apologize ahead of time if my post is confusing. I am trying my best to explain what I am attempting to accomplish.
I have a large set of data that’s listed on a specific worksheet named “Trans-Data”. On this worksheet there are columns labeled with a specific project number that contain the data, for example B1 to B14 and I have it hard-coded to copy that correct data to the correct project. I am sure there is a much better way in accomplishing this process than what I’ve written, but my VB knowledge is limited.
1st Question – Is there a way to detect all of the worksheets name in the workbook and copy the appropriate project number’s data to the associated worksheet?
So if there are “Project” worksheets named:
2nd Question - Again, my VB knowledge is limited but is there a way to loop through each worksheet and project and run through all of the variables rather than having to hardcode everything?
Thank you in advance!
I apologize ahead of time if my post is confusing. I am trying my best to explain what I am attempting to accomplish.
I have a large set of data that’s listed on a specific worksheet named “Trans-Data”. On this worksheet there are columns labeled with a specific project number that contain the data, for example B1 to B14 and I have it hard-coded to copy that correct data to the correct project. I am sure there is a much better way in accomplishing this process than what I’ve written, but my VB knowledge is limited.
1st Question – Is there a way to detect all of the worksheets name in the workbook and copy the appropriate project number’s data to the associated worksheet?
So if there are “Project” worksheets named:
- AAA-ProjectNo.###, and its associated data is on worksheet Trans-Data, cells B1 to B14 and has a column header named AAA-ProjectNo.###
- BBB-ProjectNo.###.### and its associated data is on worksheet Trans-Data, cells C1 to C14 and has a column header named BBB-ProjectNo.###
- CCC-ProjectNo.###.###.### and its associated data is on worksheet Trans-Data, cells D1 to D14 and has a column header named CCC-ProjectNo.###
- Etc.
2nd Question - Again, my VB knowledge is limited but is there a way to loop through each worksheet and project and run through all of the variables rather than having to hardcode everything?
Thank you in advance!
VBA Code:
'Set Constants
Const SourceData = "Trans-Data"
Const ACTPreFix = "ACT-"
Const WIPPreFix = "WIP-"
Const LogPath = "\\some-locations\Logs\" UNC Path
Const LogFile = "DC_log.txt"
'Set SubProject Constants
Const Sub001 = ".010.001"
Const Sub002 = ".010.002"
Const Sub003 = ".010.003"
'Set Variables
Dim FYPeriod As Variant
Dim ProjNum As Variant
Dim fileNum As Integer
Dim UserPrompt As Integer
Sub Extract_DataCopy_Proc()
fileNum = FreeFile()
Open LogPath & LogFile For Append As #fileNum
UserPrompt = MsgBox("Are you sure you want to proceed?", vbQuestion + vbYesNo + vbDefaultButton2, "Initiate Data Copy?")
If UserPrompt = vbYes Then
FYPeriod = InputBox("Enter Fiscal Year", "User Prompt - Fiscal Year Input") 'Method of receiving user input for: FISCAL YR
ProjNum = InputBox("Enter FULL Project No.", "User Prompt - Project Number Input") 'Method of receiving user input for: PROJECT NUMBER
'--------------------------------------------------------------------
'BEGINNING
'Sub-Project .010.001
'--------------------------------------------------------------------
'---------------
'ACTUALS DATA
Debug.Print "Process Initiated on: " & Now 'Append to Imediate
Write #fileNum, "Process Initiated on: " & Now 'Append to log file
Debug.Print "Project Year: " & FYPeriod
Write #fileNum, "Project Year: " & FYPeriod
Debug.Print "Actuals Data Copy Beginning..."
Write #fileNum, "Actuals Data Copy Beginning..."
Debug.Print "For worksheet: " & ACTPreFix & ProjNum & Sub001
Write #fileNum, "For worksheet: " & ACTPreFix & ProjNum & Sub001
Debug.Print ""
Write #fileNum, ""
Print #fileNum, ""
Write #fileNum, ""
Debug.Print "---------------------------------------------"
Write #fileNum, "---------------------------------------------"
Print #fileNum, "---------------------------------------------"
Write #fileNum, "---------------------------------------------"
'TransYTDMaterial
Debug.Print "Copying: TransYTDMaterial..."
Write #fileNum, "Copying: TransYTDMaterial..."
sheets(SourceData).Range("D2:D6").Copy
sheets(ACTPreFix & ProjNum & Sub001).Select
Range("Actual" & "FY" & FYPeriod & "Mat").Select
ActiveSheet.Paste
'TransYTDHrsEngr
Debug.Print "Copying: TransYTDHrsEngr..."
Write #fileNum, "Copying: TransYTDHrsEngr..."
sheets(SourceData).Range("D7:D10").Copy
sheets(ACTPreFix & ProjNum & Sub001).Select
Range("Actual" & "FY" & FYPeriod & "HrsEngr").Select
ActiveSheet.Paste
'TransYTDEngrDollars
Debug.Print "Copying: TransYTDEngrDollars..."
Write #fileNum, "Copying: TransYTDEngrDollars..."
sheets(SourceData).Range("D11").Copy
sheets(ACTPreFix & ProjNum & Sub001).Select
Range("FY" & FYPeriod & "Engr").Select
ActiveSheet.Paste
Debug.Print "---------------------------------------------"
Write #fileNum, "---------------------------------------------"
Debug.Print "Work-In-Progess Data Copy Complete"
Write #fileNum, "Work-In-Progess Data Copy Complete"
Debug.Print "For worksheet: " & ACTPreFix & ProjNum & Sub001
Write #fileNum, "For worksheet: " & ACTPreFix & ProjNum & Sub001
Debug.Print "---------------------------------------------"
Write #fileNum, "---------------------------------------------"
Debug.Print ""
Write #fileNum, ""
Debug.Print ""
Write #fileNum, ""
Debug.Print ""
Write #fileNum, ""
Debug.Print "---------------------------------------------"
Write #fileNum, "---------------------------------------------"
Debug.Print "Data Copy Complete for & ProjNum & Sub001"
Write #fileNum, "---------------------------------------------"
Debug.Print "Process Completed on: " & Now
Write #fileNum, "Process Completed on: " & Now
Debug.Print "---------------------------------------------"
Write #fileNum, "---------------------------------------------"
'--------------------------------------------------------------------
'Sub-Project .010.001
'END
'--------------------------------------------------------------------
Close #fileNum
Else
MsgBox "Process Canceled"
Close #fileNum
End If
Application.DisplayAlerts = True
End Sub