Help modifying code to transfer data to continuation sheet please.
Having problems with VBA code below transferring rows & columns from sheet2 (armele) to sheet1 (Purchase Order) starting at "Materials" & "Price rows & columns (F14:I33) down to (F33:I33) then continuing with next column on page1 "Materials" & "Price" rows & columns (O14:T14) down to (O33:T33).
Would also like it to continue to page2 "Materials" & "Price" rows & columns (F39:I39) down to (F:68:I68) then to "Materials" & "Price" rows & columns (O39:T39) down to and finishing at (O68:T68)
Please see code below and file if needed - Thanks!.
Option Explicit
Option Base 1
Const IsChecked As String = "a"
Sub TransferData()
Dim ARMELE As Worksheet, REQFORM As Worksheet
Dim CheckList As Range, CheckBox As Range
Dim InvCount As Long, ReqRow As Long, UnitDivisor As Long, d As Long
Dim UnitIssue As String, DestM As Variant, DestP As Variant
Const strPassword As String = "Password"
ActiveSheet.Unprotect Password:=strPassword
On Error Resume Next
DestM = Array(6, 15) 'material columns
DestP = Array(9, 20) 'price columns
Set ARMELE = Worksheets("armele") 'source worksheet
Set REQFORM = Worksheets("Purchase Order") 'destination worksheet
Set CheckList = ARMELE.Range("G:G").SpecialCells(xlConstants) 'cells with checkmarks
If CheckList Is Nothing Then
MsgBox "No items were checked to copy!"
Exit Sub
End If
'next order-form row to fill, based on column F (Description)
ReqRow = REQFORM.Cells(Rows.Count, "F").End(xlUp).Row + 1
If ReqRow > 33 Then
ReqRow = REQFORM.Cells(35, "O").End(xlUp).Row + 1
If ReqRow > 33 Then
MsgBox "Purchase Order Form is Full! ( Press OK to delete remaining check marks? )"
' DeleteColumn Macro
'
'
Columns("G:G").Select
Selection.ClearContents
Sheets("Purchase Order").Select
Range("X10").Select
Exit Sub
End If
d = 2 'destination array item
Else
d = 1 'destination array item
End If
For Each CheckBox In CheckList
If CheckBox = IsChecked Then
'material
REQFORM.Cells(ReqRow, DestM(d)).Value = ARMELE.Cells(CheckBox.Row, "C").Value
'price
Select Case UCase(ARMELE.Cells(CheckBox.Row, "D").Value)
Case Is = "C", "H", "J", "HU": UnitDivisor = 100
Case Is = "M", "T": UnitDivisor = 1000
Case Is = "E", "F", "R", "B", "P", "RL", "BX", "PK", "CD", "FT", "KG", "PC", "JR": UnitDivisor = 1
End Select
REQFORM.Cells(ReqRow, DestP(d)).Value = ARMELE.Cells(CheckBox.Row, "F").Value / UnitDivisor
CheckBox = "" 'clear the check mark
If ReqRow = 33 Then 'increment to next req form row/column
If d = 2 Then
MsgBox "Purchase Order Form is Full! ( Press OK to delete remaining check marks? )"
' DeleteColumn Macro
'
'
Columns("G:G").Select
Selection.ClearContents
Sheets("Purchase Order").Select
Range("X10").Select
Exit Sub
Else
ReqRow = 14
d = 2
End If
Else
ReqRow = ReqRow + 1
End If
End If
Next CheckBox
ActiveSheet.Protect Password:=strPassword
End Sub
Attachment | Size |
---|---|
Test PURCHASE ORDER FORM.xlsm | 439.35 KB |
This code will do Sub
This code will do
Sub Leo()
With Sheets("Purchase Order")
.Range("F14", "I33").ClearContents
.Range("F39", "I68").ClearContents
.Range("O14", "U33").ClearContents
.Range("O39", "U68").ClearContents
End With
Const strPassword As String = "Password"
ActiveSheet.Unprotect Password:=strPassword
lr = Cells(Rows.Count, 6).End(xlUp).Row
x = 14
col = 6
For Each cl In Range("G2", "G" & lr)
If cl.Value = "a" Then
If col = 6 Then
Sheets("Purchase Order").Cells(x, col).Resize(1, 4) = Array(cl.Offset(, -4), " ", " ", cl.Offset(, -1))
x = x + 1
End If
If col = 15 Then
Sheets("Purchase Order").Cells(x, col).Resize(1, 6) = Array(cl.Offset(, -4), " ", " ", "", "", cl.Offset(, -1))
x = x + 1
End If
End If
If x = 34 And col = 6 Then
x = 14
col = 15
End If
If x = 34 And col = 15 Then
x = 39
col = 6
End If
If x = 69 And col = 6 Then
x = 39
col = 15
End If
Next
Range("G:G").ClearContents
ActiveSheet.Protect Password:=strPassword
Application.Goto reference:=Sheets("Purchase Order").Range("X10")
End Sub
Kind regards
Leo
It needs a little more adjustment please.
This works excellent filling up the rows and columns properly but the pricing breakdown is for (per each item) is not defined anymore.
'price
Select Case UCase(ARMELE.Cells(CheckBox.Row, "D").Value)
Case Is = "C", "H", "J", "HU": UnitDivisor = 100
Case Is = "M", "T": UnitDivisor = 1000
Case Is = "E", "F", "R", "B", "P", "RL", "BX", "PK", "CD", "FT", "KG", "PC", "JR": UnitDivisor = 1
Also, each time you select an item from the "armele" sheet and hit the transfer button it clears the "Purchase Order" form and starts from the beginning each time instead of allowing you to keep adding to it till the end of both pages of the "Purchase Order" form. There also needs to be a pop up warning to notify the user that both pages are full "Do you want to delete the remaining check boxes?"
Thank you so much!
Hi, the problem is not the
Hi,
the problem is not the code, it are joint cells,
move material in row 38 from column F to column G
and from column O to column P.
Kind regards
Leo
I'm not understanding
Leo,
Your code works perfectly but it's not isolating the per price to be (ea.) Some of the prices on the "armele" sheet in column "D" & "E" our per (100) or per (1,000) etc. etc. All prices need to be transferred over to the "Purchase Form" as per (ea.)
Also, the user needs to be able to add more items either manually or by the "armele" sheet without it wiping out the "Purchase Order" sheet each time you use the "armele" sheet transfer method.
Is there anyway you can adjust my original file to just fill in the rows and columns properly starting with the first page then continuing on to the second page?
I would appreciate it very much.
Respectfully,
ElmerFud
Elmer,i tink your own code
Elmer,
i tink your own code is fine, just undo the merged cells
in row 38.
That causes the problem for finding the right rownumber
Cell F38 en O38 have both "Material"
so thats not an emty row
Kind Regards
Leo