Forum Discussion
Macro to copy items from one sheet to another by vendor and copy to other sheet with running total?
Hello, I would like to make a macro that will copy from 3_Master PO Request, based on Vendor, to 4A_PO Template but only 21 items at a time. I want the catalog #, Name/Description, UOM, and Qty copied over to the form. I have a macro on it that will then export it to the proper file type to allow it to be imported into a premade pdf PO form we have. I would also like anything transferred to 4A_PO TEMPLATE to also be transfered to 4B_2024 Totals but adding it to the next blank line as this sheet will keep a running tally of all purchases in 2024. How would I do this efficiently? I've attached a copy of the worksheet.
- To create a macro that copies items from "3_Master PO Request" to "4A_PO Template" and updates "4B_2024 Totals," follow these steps:
1. **Filter and Copy Data**: Filter "3_Master PO Request" by Vendor, then copy the relevant data (Catalog #, Name/Description, UOM, Qty) in batches of 21 items to "4A_PO Template."
2. **Update Running Total**: After copying each batch, append the data to "4B_2024 Totals" while maintaining a running total.
Here's a basic VBA macro that accomplishes this:
```vba
Sub CopyItemsByVendor()
Dim wsSource As Worksheet, wsTarget As Worksheet, wsTotals As Worksheet
Dim rngSource As Range, rngTarget As Range
Dim lastRowSource As Long, lastRowTarget As Long, lastRowTotals As Long
Dim vendor As String
Dim i As Long, batchSize As Integer
' Initialize sheets
Set wsSource = ThisWorkbook.Sheets("3_Master PO Request")
Set wsTarget = ThisWorkbook.Sheets("4A_PO Template")
Set wsTotals = ThisWorkbook.Sheets("4B_2024 Totals")
' Define batch size
batchSize = 21
' Get vendor from user or input box
vendor = InputBox("Enter the Vendor to filter by:")
' Filter by Vendor
wsSource.Range("A1").AutoFilter Field:=1, Criteria1:=vendor
' Find last row in source data
lastRowSource = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
' Find last row in target and totals sheets
lastRowTarget = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row + 1
lastRowTotals = wsTotals.Cells(wsTotals.Rows.Count, 1).End(xlUp).Row + 1
' Copy data in batches
For i = 2 To lastRowSource Step batchSize
Set rngSource = wsSource.Range("A" & i & ":D" & Application.WorksheetFunction.Min(i + batchSize - 1, lastRowSource))
Set rngTarget = wsTarget.Range("A" & lastRowTarget)
' Copy to target sheet
rngSource.Copy Destination:=rngTarget
' Copy to totals sheet
rngSource.Copy Destination:=wsTotals.Range("A" & lastRowTotals)
' Update last row counters
lastRowTarget = lastRowTarget + rngSource.Rows.Count
lastRowTotals = lastRowTotals + rngSource.Rows.Count
' Optional: Add any additional processing here
Next i
' Remove filter
wsSource.AutoFilterMode = False
MsgBox "Items copied successfully!"
End Sub
```
**Instructions**:
1. Open Excel and press `ALT + F11` to open the VBA editor.
2. Insert a new module (`Insert` > `Module`).
3. Copy and paste the above code into the module.
4. Close the VBA editor and run the macro from the Excel interface (`ALT + F8`).
Adjust the column references in the `rngSource` and `rngTarget` ranges if your data is in different columns. This macro assumes Vendor is in column A and your data starts from row 2.
