I have an excel worksheet set up as a form in a workbook. from that form i upload to a workbook on our main server parts of it that I need to log.
I want to edit this form and change what it uploads.
[TABLE="width: 500"]
<tbody>[TR]
[TD]CosCode[/TD]
[TD]Description[/TD]
[TD]Startdate[/TD]
[TD]EndDate[/TD]
[TD]Rate[/TD]
[TD]UoM[/TD]
[TD]Total [/TD]
[/TR]
[TR]
[TD]TestCode[/TD]
[TD]Generator 1 of 2[/TD]
[TD]01/06/207[/TD]
[TD]04/06/2017[/TD]
[TD]$1.00[/TD]
[TD]day[/TD]
[TD]$4.00[/TD]
[/TR]
[TR]
[TD]TestCode1[/TD]
[TD]Generator 2 of 2[/TD]
[TD]03/06/2017[/TD]
[TD]004/06/2017[/TD]
[TD]$1.00[/TD]
[TD]day[/TD]
[TD]$2.00[/TD]
[/TR]
</tbody>[/TABLE]
I want to enter above underlined to cells into new workbook. But if the rows are empty i want it not enter empty rows. there are 7 rows to enter details into on the form.
I have vba set up to enter cells for the purchase number date supplier etc. it is getting the vba to stop if the cells are empty. how can I write that part and where should I put it?
Private Sub CommandButton1_Click()
Dim Area As String
Dim Supplier As String, PurchaseNumber As Long
Dim Description As String, Commercial As String
Dim Approved As String, Costcode As String
Dim PODate As Long
Dim Total_GSTexc As Single
Dim PurchaseOrder As Workbook
Worksheets("PurchaseOrder").Select
Area = Range("G6")
Worksheets("PurchaseOrder").Select
PurchaseNumber = Range("H6")
Worksheets("PurchaseOrder").Select
PODate = Range("H7")
Worksheets("PurchaseOrder").Select
Supplier = Range("B6")
Worksheets("PurchaseOrder").Select
Description = Range("A35")
Worksheets("PurchaseOrder").Select
Costcode = Range("D35")
Worksheets("PurchaseOrder").Select
Commercial = Range("A32")
Worksheets("PurchaseOrder").Select
Approved = Range("A29")
Worksheets("PurchaseOrder").Select
Total_GSTexc = Range("H26")
Set PurchaseOrder = Workbooks.Open("P:\put address here example.xlsb")
Worksheets("NewPurchaseOrders").Select
Worksheets("NewPurchaseOrders").Range("A3").Select
RowCount = Worksheets("NewPurchaseOrders").Range("A1").CurrentRegion.Rows.Count
With Worksheets("NewPurchaseOrders").Range("A1")
.Offset(RowCount, 0) = Area
.Offset(RowCount, 1) = PurchaseNumber
.Offset(RowCount, 2) = PODate
.Offset(RowCount, 3) = Supplier
.Offset(RowCount, 4) = Description
.Offset(RowCount, 5) = Costcode
.Offset(RowCount, 6) = Approved
.Offset(RowCount, 7) = Commercial
.Offset(RowCount, 8) = Total_GSTexc
End With
PurchaseOrder.Save
End Sub
I want to edit this form and change what it uploads.
[TABLE="width: 500"]
<tbody>[TR]
[TD]CosCode[/TD]
[TD]Description[/TD]
[TD]Startdate[/TD]
[TD]EndDate[/TD]
[TD]Rate[/TD]
[TD]UoM[/TD]
[TD]Total [/TD]
[/TR]
[TR]
[TD]TestCode[/TD]
[TD]Generator 1 of 2[/TD]
[TD]01/06/207[/TD]
[TD]04/06/2017[/TD]
[TD]$1.00[/TD]
[TD]day[/TD]
[TD]$4.00[/TD]
[/TR]
[TR]
[TD]TestCode1[/TD]
[TD]Generator 2 of 2[/TD]
[TD]03/06/2017[/TD]
[TD]004/06/2017[/TD]
[TD]$1.00[/TD]
[TD]day[/TD]
[TD]$2.00[/TD]
[/TR]
</tbody>[/TABLE]
I want to enter above underlined to cells into new workbook. But if the rows are empty i want it not enter empty rows. there are 7 rows to enter details into on the form.
I have vba set up to enter cells for the purchase number date supplier etc. it is getting the vba to stop if the cells are empty. how can I write that part and where should I put it?
Private Sub CommandButton1_Click()
Dim Area As String
Dim Supplier As String, PurchaseNumber As Long
Dim Description As String, Commercial As String
Dim Approved As String, Costcode As String
Dim PODate As Long
Dim Total_GSTexc As Single
Dim PurchaseOrder As Workbook
Worksheets("PurchaseOrder").Select
Area = Range("G6")
Worksheets("PurchaseOrder").Select
PurchaseNumber = Range("H6")
Worksheets("PurchaseOrder").Select
PODate = Range("H7")
Worksheets("PurchaseOrder").Select
Supplier = Range("B6")
Worksheets("PurchaseOrder").Select
Description = Range("A35")
Worksheets("PurchaseOrder").Select
Costcode = Range("D35")
Worksheets("PurchaseOrder").Select
Commercial = Range("A32")
Worksheets("PurchaseOrder").Select
Approved = Range("A29")
Worksheets("PurchaseOrder").Select
Total_GSTexc = Range("H26")
Set PurchaseOrder = Workbooks.Open("P:\put address here example.xlsb")
Worksheets("NewPurchaseOrders").Select
Worksheets("NewPurchaseOrders").Range("A3").Select
RowCount = Worksheets("NewPurchaseOrders").Range("A1").CurrentRegion.Rows.Count
With Worksheets("NewPurchaseOrders").Range("A1")
.Offset(RowCount, 0) = Area
.Offset(RowCount, 1) = PurchaseNumber
.Offset(RowCount, 2) = PODate
.Offset(RowCount, 3) = Supplier
.Offset(RowCount, 4) = Description
.Offset(RowCount, 5) = Costcode
.Offset(RowCount, 6) = Approved
.Offset(RowCount, 7) = Commercial
.Offset(RowCount, 8) = Total_GSTexc
End With
PurchaseOrder.Save
End Sub