I'm currently trying to utilize the below VBA code I found here to take a spreadsheet export from my software and break it up into a usable form for my projects. As is, the data itself is all in a single row. I need this data to be broken up into a "Material" and "Labor" row separate from each other. That being said, I have toiled with VBA once before, and quite a while ago.
Currently, the only data I need is from Columns B, C, and D. I then need the current row in column A to be renamed, and a new row inserted below it with a generated name based on its predecessor (Rename row1 column A Material, Insert new row(2) column A and name Labor)
Here is a sample of the data in export format.
I would need to grab data from columns...
A - for row name/floor reference
M - Material Amount
R - Labor Amount
Here is a sample of the data in my desired format.
Floor row is created
Single row is split into two, renamed and material/labor specific data copied into cells
Material - Column D
Labor - Column B
I appreciate any support/direction you guys have to offer. While this is a small example that doesn't warrant a VBA macro, I typically have around 100-500 rows of information on a typical job to reorganize 3 to 4 times a week.
Currently, the only data I need is from Columns B, C, and D. I then need the current row in column A to be renamed, and a new row inserted below it with a generated name based on its predecessor (Rename row1 column A Material, Insert new row(2) column A and name Labor)
Code:
Public Sub ShiftData()
'Declarations
Dim wsData As Worksheet
Dim rngCopy As Range
Dim i As Long 'Loop variable
Dim j As Long 'Loop variable
Dim lngFirstDataRow As Long
Dim lngLastDataRow As Long
Dim boolDataFound As Boolean 'True or false value
'Initialization
Set wsData = ActiveSheet
lngFirstDataRow = 21 'Replace with your own value - row where the data starts
lngLastDataRow = 32 'Replace with your own value - row where the data ends
'Loop from the bottom to top. Why? Because when we insert
'rows, it will shift stuff down.
For i = lngLastDataRow To lngFirstDataRow Step -1
'Check columns AL - AP for data
boolDataFound = False
For j = 4 To 6 'AL is the 38th column, AP the 42nd
'If it's not blank...
If Not wsData.Cells(i, j).Text = "" Then
'...then there is data. Change the boolean to TRUE and exit the for loop
boolDataFound = True
Exit For
End If
Next j
'If we found data there
If boolDataFound Then
'Insert a new row below the current row
wsData.Rows(i + 1).Insert shift:=xlShiftDown
'Copy the data
Set rngCopy = wsData.Range(wsData.Cells(i, 4), wsData.Cells(i, 6))
rngCopy.Copy
'Copy that data into cells AB-AF
wsData.Cells(i + 1, 4).PasteSpecial xlPasteAll
'Copy the value in column B
wsData.Cells(i + 1, 6).Value = wdata.Cells(i, 6).Value
End If
Next i
'Remove the copy mode
Application.CutCopyMode = False
End Sub
Here is a sample of the data in export format.
I would need to grab data from columns...
A - for row name/floor reference
M - Material Amount
R - Labor Amount
Here is a sample of the data in my desired format.
Floor row is created
Single row is split into two, renamed and material/labor specific data copied into cells
Material - Column D
Labor - Column B
I appreciate any support/direction you guys have to offer. While this is a small example that doesn't warrant a VBA macro, I typically have around 100-500 rows of information on a typical job to reorganize 3 to 4 times a week.