I am in the process of setting up a pricing template for my company and need some help the VBA.
My workbook currently has two worksheets, the first is called "Phases" and the second is called "Estimate"
The "Phases" Sheet contains all of the available product codes that can be added to an estimate template. Each product code has its own row and there is a checkbox in column "M" that can be selected if they would like to add the product code to the estimate template. Once all of the desired product codes are selected, I have a macro that will copy the desired range (Column A & Column B) from each row that contains a selected checkbox and paste it in the next available row on the "Estimates" sheet in columns D and E. This part of the process corresponds to the code I've listed below:
The next step in the process is where I'm having an issue. I'd like to modify the code above to include a MID Function. The Phase Codes (Located in Column A on the "Phases" sheet have an identifier in the 2nd character that qualifies them as a Labor or Material Phase Code. For example, a material phase code is something like "0M-123" and a labor phase code is something like "OL-123".
When the ranges are copied from the "Phases" sheet, I need the ranges to be pasted as follows:
If row contains a checkbox AND If Range contains a material phase code, copy Cells from Column A & B from "Phases" Sheet, Paste Values 3 times on "Estimate" sheet beginning in next available row in Column D. (If Next available row was row 25, then the values would be pasted in range D25:E27). Then Add Value of "5" in Cell F25, add Value of "6" in Cell F26 and Add Value of "7" in Cell F27.
If row contains a checkbox AND If Range contains a labor phase code, copy Cells from Column A & B from "Phases" Sheet, Paste Values 4 times on "Estimate" sheet beginning in next available row in Column D. (If Next available row was row 28, then the values would be pasted in range D28:E31). Then Add Value of "1" in Cell F28, add Value of "2" in Cell F29 and Add Value of "3" in Cell F30 and a value of "4" in F31.
I've created a pdf that walks through the steps of the copy/paste procedure I'd like to execute with VBA. I received an error that my file was too large when I tried to upload my workbook. If anyone can help and would like to view my workbook, please send me a direct message and I'll email you the files.
Any help is GREATLY appreciated.
POST MOVED BY MODERATOR.
.
My workbook currently has two worksheets, the first is called "Phases" and the second is called "Estimate"
The "Phases" Sheet contains all of the available product codes that can be added to an estimate template. Each product code has its own row and there is a checkbox in column "M" that can be selected if they would like to add the product code to the estimate template. Once all of the desired product codes are selected, I have a macro that will copy the desired range (Column A & Column B) from each row that contains a selected checkbox and paste it in the next available row on the "Estimates" sheet in columns D and E. This part of the process corresponds to the code I've listed below:
Code:
Sub AddtoEstimate()
For Each chkbx In ActiveSheet.CheckBoxes
If chkbx.Value = 1 Then
For r = 1 To Rows.Count
If Cells(r, 1).Top = chkbx.Top Then
With Worksheets("Estimate")
LRow = .Range("D" & Rows.Count).End(xlUp).row + 1
.Range("D" & LRow & ":E" & LRow) = _
Worksheets("Phases").Range("A" & r & ":B" & r).Value
End With
Exit For
End If
Next r
End If
Next
End SubThe next step in the process is where I'm having an issue. I'd like to modify the code above to include a MID Function. The Phase Codes (Located in Column A on the "Phases" sheet have an identifier in the 2nd character that qualifies them as a Labor or Material Phase Code. For example, a material phase code is something like "0M-123" and a labor phase code is something like "OL-123".
When the ranges are copied from the "Phases" sheet, I need the ranges to be pasted as follows:
If row contains a checkbox AND If Range contains a material phase code, copy Cells from Column A & B from "Phases" Sheet, Paste Values 3 times on "Estimate" sheet beginning in next available row in Column D. (If Next available row was row 25, then the values would be pasted in range D25:E27). Then Add Value of "5" in Cell F25, add Value of "6" in Cell F26 and Add Value of "7" in Cell F27.
If row contains a checkbox AND If Range contains a labor phase code, copy Cells from Column A & B from "Phases" Sheet, Paste Values 4 times on "Estimate" sheet beginning in next available row in Column D. (If Next available row was row 28, then the values would be pasted in range D28:E31). Then Add Value of "1" in Cell F28, add Value of "2" in Cell F29 and Add Value of "3" in Cell F30 and a value of "4" in F31.
I've created a pdf that walks through the steps of the copy/paste procedure I'd like to execute with VBA. I received an error that my file was too large when I tried to upload my workbook. If anyone can help and would like to view my workbook, please send me a direct message and I'll email you the files.
Any help is GREATLY appreciated.
POST MOVED BY MODERATOR.
.
Last edited by a moderator:
