Hi all,
I received a file that I need to reformat to upload to a database. The current file is setup where one row represents one client, with twelve values for financial projections for each month of next year. I need this formatted with the same attributes, except i need one row for each monthly projection. I set up this procedure to copy Columns A through J, then to to copy the month/yr combo (eg -- 202301 for January) into column K, and then the value for that month in column L. So I have 1248 rows, and I would expect this program to easily create 14,976.
But my program only successfully completes about 50 inner loops before getting hung up. I tried to leave my computer running, but it just froze. I am not sure if there is a memory leak somewhere, or if this is just an inefficient procedure. Are there some simple changes I can make to get this to finish?
Any input is appreciated!
Below is an example of part of the table ... I want to repeat the strings in State, County and Market, then copy the 2023XY label, then copy the value that was under the 2023XY Column to the cell beside the new paste.
Here is what I want it to look like:
I received a file that I need to reformat to upload to a database. The current file is setup where one row represents one client, with twelve values for financial projections for each month of next year. I need this formatted with the same attributes, except i need one row for each monthly projection. I set up this procedure to copy Columns A through J, then to to copy the month/yr combo (eg -- 202301 for January) into column K, and then the value for that month in column L. So I have 1248 rows, and I would expect this program to easily create 14,976.
But my program only successfully completes about 50 inner loops before getting hung up. I tried to leave my computer running, but it just froze. I am not sure if there is a memory leak somewhere, or if this is just an inefficient procedure. Are there some simple changes I can make to get this to finish?
Any input is appreciated!
Below is an example of part of the table ... I want to repeat the strings in State, County and Market, then copy the 2023XY label, then copy the value that was under the 2023XY Column to the cell beside the new paste.
| State | County | Market | 202301 | 202302 |
| NC | Buncombe | Asheville | 500.00 | 250.00 |
Here is what I want it to look like:
| State | County | Market | Month_YR_Num | Budget |
| NC | Buncombe | Asheville | 202301 | 500.00 |
| NC | Buncombe | Asheville | 202302 | 250.00 |
VBA Code:
Public Sub Auto_Budget_Prep_1()
'Macro to assist in preparing 2023 Budget for upload
'Error Handling
On Error GoTo 0
'Declare Variables
Dim macroWB As Workbook
Dim srcWS1 As Worksheet
Dim srcWS2 As Worksheet
'Turn off notifications
With Application
.ScreenUpdating = False
' .ScreenUpdating = True --using comments to test this portion and see if the code was running
.DisplayAlerts = False
End With
'Bind Objects
Set macroWB = ThisWorkbook
Set srcWS1 = macroWB.Worksheets("stage")
Set srcWS2 = macroWB.Worksheets("budget")
'COPY EACH ROW B:F 12 TIMES --- 1X FOR EACH Month
'Find last row of each destination / summary file worksheet
Dim NumRows As Long: NumRows = srcWS1.Range("A1", srcWS1.Range("A1").End(xlDown)).Rows.Count
'need to create a month_yr_num stamp
'Dim month7 As String: month7 = "202301"
'Dim month8 As String: month8 = "202302"
'Dim month9 As String: month9 = "202303"
'Dim month10 As String: month10 = "202304"
'Dim month11 As String: month11 = "202305"
'Dim month12 As String: month12 = "202306"
'Dim month13 As String: month13 = "202307"
'Dim month14 As String: month14 = "202308"
'Dim month15 As String: month15 = "202309"
'Dim month16 As String: month16 = "202310"
'Dim month17 As String: month17 = "202311"
'Dim month18 As String: month18 = "202312"
Dim i, j As Integer
'declare k for row counter on srcsheet2
'this variable is used to count rows for budget sheet ws2, it is not to be destroyed by loop
j = 2
'loop through each row stage sheet
For i = 2 To 20
'For i = 2 To NumRows
'this variable to be used for 12 columns / months
Dim k As Integer
'inner loop will run once for each month of year
For k = 11 To 22
'first copy B:F to budget sheet
srcWS1.Range("A" & i & ":J" & i).Copy
srcWS2.Range("A" & j & ":J" & j).PasteSpecial xlPasteValues
'now copy month_yr_num stamp col 11
srcWS1.Cells(1, k).Copy
srcWS2.Cells(j, 11).PasteSpecial xlPasteValues
'now copy political budget value col 12
srcWS1.Cells(i, k).Copy
srcWS2.Cells(j, 12).PasteSpecial xlPasteValues
'increment j
j = j + 1
'increment k
Next k
'increment i
Next i
'Close out variables
'Set j = Nothing
'Set i = Nothing
'Set NumRows = Nothing
Set srcWS1 = Nothing
Set srcWS2 = Nothing
Set macroWB = Nothing
'Turn on notifications
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
'Message
MsgBox "Done!"
End Sub
