I am fairly new to the world of VBA coding and feel that I am coming a long way, however, I am hoping that someone a bit more experienced than I, could look at my coding and tell me if I am on the right track. I know this is a bit long but I want to be sure that I am providing as much info as I can and am making things as clear as I can. Please bear with me.
I have a spreadsheet that data from a userform is moved to. This spreadsheet is primarily to 'house' what is presented by the user in a format that they are familiar with, and of course there are calculations, etc to provide further information. I have created a macro to 'unhide' an additional spreadsheet that I want to use to copy certain fields of each row of data in order to summarize in a useful format. This macro needs to determine if there is a value in the cell of a range of cells (I named the range on my worksheet as "mileage") and if so, copy that field and one other field in the row to the new spreadsheet, then I also need this macro to determine an additional code based on a couple of fields on the first spreadsheet and put that additional code on the new worksheet. Once this is done, it needs to go to the next row until all rows which contain the 'Mileage' range has been processed. Does all of that make sense? I will explain in practice and then I will post my code that I have thus far.
If row one has a value in the 'mileage' range cell, I need the 'mileage' cell and the 'projectcode' cell to copy to the new worksheet in the first empty row. Then, I need it to view the 'travelstate' range cell of the first worksheet and also a static field (D5), which together are used to determine which of 3 different codes to use for the additional field on my new worksheet using If and IfElse statements. Once this is completed, I need it to go to the second row and do the same steps, until all rows have been processed.
My code below shows first that, using my macro keys, I am 'unhiding' the worksheet where the data will transfer to. Next, it is determining if there is a value in the first cell of the 'Mileage' range and if there is, it copies that field and the 'projectcode' range cell of the same row to my new worksheet. These are copied to the first empty cell (I have done this by creating a 'cheater field' on a hidden spreadsheet which calculates how many fields are used to ensure my values move to the first empty. I think I did it correctly). Finally, my code is looking at the 'TravelState' and field D5 of the same row on the worksheet to determine the expense code and putting it in the field next to the mileage and projectcode values.
Just as a side, I want to note that I have two other 'sets' of similar steps that I need to have this macro accomplish and my goal is that it will complete all of the rows for this first set of steps prior to going to the next set of steps and do all of that 'set' for all rows prior to going to the next 'set' of similar steps. I understand that this macro might take a few seconds to run but I'm okay with that. The end results will be worth the wait.
Also, I know that I could probably provide a couple of additional 'dim's to shorten my coding and I appreciate the thoughts, but I prefer to keep it the way it is as it makes more sense to me being a novice, and in the event I have to go in at a later date and make tweaks, I want to be sure I'm not totally lost. I don't want to have to post again on here every time I have to make any changes or adjustments to my worksheet because I don't understand what the code says. I hope to someday be as capable as each of you but I'm just not there yet.
Thank you, in advance. I appreciate any help you can give me!
I have a spreadsheet that data from a userform is moved to. This spreadsheet is primarily to 'house' what is presented by the user in a format that they are familiar with, and of course there are calculations, etc to provide further information. I have created a macro to 'unhide' an additional spreadsheet that I want to use to copy certain fields of each row of data in order to summarize in a useful format. This macro needs to determine if there is a value in the cell of a range of cells (I named the range on my worksheet as "mileage") and if so, copy that field and one other field in the row to the new spreadsheet, then I also need this macro to determine an additional code based on a couple of fields on the first spreadsheet and put that additional code on the new worksheet. Once this is done, it needs to go to the next row until all rows which contain the 'Mileage' range has been processed. Does all of that make sense? I will explain in practice and then I will post my code that I have thus far.
If row one has a value in the 'mileage' range cell, I need the 'mileage' cell and the 'projectcode' cell to copy to the new worksheet in the first empty row. Then, I need it to view the 'travelstate' range cell of the first worksheet and also a static field (D5), which together are used to determine which of 3 different codes to use for the additional field on my new worksheet using If and IfElse statements. Once this is completed, I need it to go to the second row and do the same steps, until all rows have been processed.
My code below shows first that, using my macro keys, I am 'unhiding' the worksheet where the data will transfer to. Next, it is determining if there is a value in the first cell of the 'Mileage' range and if there is, it copies that field and the 'projectcode' range cell of the same row to my new worksheet. These are copied to the first empty cell (I have done this by creating a 'cheater field' on a hidden spreadsheet which calculates how many fields are used to ensure my values move to the first empty. I think I did it correctly). Finally, my code is looking at the 'TravelState' and field D5 of the same row on the worksheet to determine the expense code and putting it in the field next to the mileage and projectcode values.
Code:
Sub ExpenseCodeProcessing()'DataEntrySummary creation Keyboard Shortcut: Ctrl + Shift + Q'''
Worksheets("DataEntrySummary").Visible = True
Dim rngSource As Range
Dim col As Integer
Dim TargetRow As Integer
Dim CodesTargetRow As Integer
TargetRow = Sheets("Codes").Range("D43").Value + 1
CodesTargetRow = Sheets("Codes").Range("D45").Value + 1
'''DETERMINE EXPENSE CODE AND MOVE MILEAGE, PROJECT CODE AND EXPENSE CODE TO EXPENSE CODE PROCESSING WORKSHEET'''
For Each cell In Sheets("Travel Expense Voucher").Range("Mileage")
If ActiveCell <> "" Then
Sheets("Expense Code Processing").Range("Data_Start").Offset(CodesTargetRow, 0).Value = mileage
Sheets("Expense Code Processing").Range("Data_Start").Offset(CodesTargetRow, 1).Value = projectcode
If Sheets("Travel Expense Voucher").Range("$D$5") = "2" Then
Sheets("Expense Code Processing").Range("Data_Start").Offset(CodesTargetRow, 2).Value = "62494"
ElseIf Sheets("Travel Expense Voucher").Range("$D$5") = "1" And TravelState = ("In-State") Then
Sheets("Expense Code Processing").Range("Data_Start").Offset(CodesTargetRow, 2).Value = "62401"
ElseIf Sheets("Travel Expense Voucher").Range("$D$5") = "1" And TravelState = ("Out-of-State") Then
Sheets("Expense Code Processing").Range("Data_Start").Offset(CodesTargetRow, 2).Value = "62411"
End If
'''END MOVE OF MILEAGE VALUES WITH EXPENSE CODES'''
Just as a side, I want to note that I have two other 'sets' of similar steps that I need to have this macro accomplish and my goal is that it will complete all of the rows for this first set of steps prior to going to the next set of steps and do all of that 'set' for all rows prior to going to the next 'set' of similar steps. I understand that this macro might take a few seconds to run but I'm okay with that. The end results will be worth the wait.
Also, I know that I could probably provide a couple of additional 'dim's to shorten my coding and I appreciate the thoughts, but I prefer to keep it the way it is as it makes more sense to me being a novice, and in the event I have to go in at a later date and make tweaks, I want to be sure I'm not totally lost. I don't want to have to post again on here every time I have to make any changes or adjustments to my worksheet because I don't understand what the code says. I hope to someday be as capable as each of you but I'm just not there yet.
Thank you, in advance. I appreciate any help you can give me!