Hi
I have an excel based Purchase requistion (PR) form which I am trying to transfer data to a master invoice log sheet
The form is in 2 sections
Top half containg date, Supplier Name , Currency, Cost Centre, Account Number
The bottom half contains line number, description cost , cost centre , account number ( if different from top half ) and value
If i have multiple lines on the PR form how can I transfer this data so that on the master log all details are transferred
I attach below the code for transferring 1 line of code but unsure how to dio for multiple as would need to copy both the top half & second half details accrosss
Thanks
Ken
Sub transfer()
'
' transfer Macro
'
Transferred = Range("X5")
'PRNo = "JP2014-" & Range("Q7")
PRDate = Range("P9")
SupplierJapanese = Range("E14")
SupplierEnglish = Range("E16")
NewSupplier = Range("R14")
CostCentre = Range("F19")
AccountCode = Range("F20")
ProductCode = Range("F21")
ProjectCode = Range("F22")
TaskCode = Range("I22")
PCurrency = Range("F24")
'line items
LineNmber = Range("C28")
Quantity = Range("D28")
UnitPrice = Range("F28")
DeliveryDate = Range("H28")
Description = Range("J28")
LineCostCentre = Range("O28")
LineAccountCode = Range("P28")
LineTotal = Range("R28")
Subtotal = Range("R46")
ConsumptionTax = Range("R47")
Total = Range("R48")
Requester = Range("E50")
Approver = Range("E53")
ApprovalDate = Range("R53")
Windows("Japan Invoice & PR Log.xlsm").Activate
Range("G2").Select
If Range("G3") <> "" Then
Selection.End(xlDown).Select
End If
NextRow = Selection.Row + 1
If Transferred = "" Then
Worksheets("PR").Unprotect Password:="5880"
'Range("A" & NextRow) = PRNo
Range("G" & NextRow) = PRDate
Range("H" & NextRow) = PCurrency
Range("I" & NextRow) = Total
'Range("F" & NextRow) = DeliveryDate
Range("N" & NextRow) = SupplierJapanese
Range("O" & NextRow) = SupplierEnglish
Range("P" & NextRow) = CostCentre
Range("R" & NextRow) = AccountCode
Range("T" & NextRow) = Description
Range("U" & NextRow) = Requester
Range("V" & NextRow) = Approver
'Range("P" & NextRow) = ApprovalDate
Worksheets("PR").Protect Password:="5880", UserInterfaceOnly:=True
ActiveWorkbook.Save
' Windows(" Japan PR Form.xlsm").Activate
Windows(2).Activate
Worksheets("PR").Unprotect Password:="5880"
Range("X5") = "Transferred!"
Worksheets("PR").Protect Password:="5880", UserInterfaceOnly:=True
Else
MsgBox PRNo & " has already been transferred!"
End If
End Sub
I have an excel based Purchase requistion (PR) form which I am trying to transfer data to a master invoice log sheet
The form is in 2 sections
Top half containg date, Supplier Name , Currency, Cost Centre, Account Number
The bottom half contains line number, description cost , cost centre , account number ( if different from top half ) and value
If i have multiple lines on the PR form how can I transfer this data so that on the master log all details are transferred
I attach below the code for transferring 1 line of code but unsure how to dio for multiple as would need to copy both the top half & second half details accrosss
Thanks
Ken
Sub transfer()
'
' transfer Macro
'
Transferred = Range("X5")
'PRNo = "JP2014-" & Range("Q7")
PRDate = Range("P9")
SupplierJapanese = Range("E14")
SupplierEnglish = Range("E16")
NewSupplier = Range("R14")
CostCentre = Range("F19")
AccountCode = Range("F20")
ProductCode = Range("F21")
ProjectCode = Range("F22")
TaskCode = Range("I22")
PCurrency = Range("F24")
'line items
LineNmber = Range("C28")
Quantity = Range("D28")
UnitPrice = Range("F28")
DeliveryDate = Range("H28")
Description = Range("J28")
LineCostCentre = Range("O28")
LineAccountCode = Range("P28")
LineTotal = Range("R28")
Subtotal = Range("R46")
ConsumptionTax = Range("R47")
Total = Range("R48")
Requester = Range("E50")
Approver = Range("E53")
ApprovalDate = Range("R53")
Windows("Japan Invoice & PR Log.xlsm").Activate
Range("G2").Select
If Range("G3") <> "" Then
Selection.End(xlDown).Select
End If
NextRow = Selection.Row + 1
If Transferred = "" Then
Worksheets("PR").Unprotect Password:="5880"
'Range("A" & NextRow) = PRNo
Range("G" & NextRow) = PRDate
Range("H" & NextRow) = PCurrency
Range("I" & NextRow) = Total
'Range("F" & NextRow) = DeliveryDate
Range("N" & NextRow) = SupplierJapanese
Range("O" & NextRow) = SupplierEnglish
Range("P" & NextRow) = CostCentre
Range("R" & NextRow) = AccountCode
Range("T" & NextRow) = Description
Range("U" & NextRow) = Requester
Range("V" & NextRow) = Approver
'Range("P" & NextRow) = ApprovalDate
Worksheets("PR").Protect Password:="5880", UserInterfaceOnly:=True
ActiveWorkbook.Save
' Windows(" Japan PR Form.xlsm").Activate
Windows(2).Activate
Worksheets("PR").Unprotect Password:="5880"
Range("X5") = "Transferred!"
Worksheets("PR").Protect Password:="5880", UserInterfaceOnly:=True
Else
MsgBox PRNo & " has already been transferred!"
End If
End Sub