VBA Command Button to send only certain data to new Workbook

Danin

New Member
Joined
Sep 16, 2016
Messages
6
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
To add to this question as below is what I am trying to move. sometimes it will only have one row of information other times it could have 5 lines. How do I tell it only to enter rows that have information in it otherwise skip to next row? I only want to send the Blue columns.
[TABLE="width: 893"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]CostCode[/TD]
[TD]Description[/TD]
[TD]On Hire Date[/TD]
[TD]Off Hire Date [/TD]
[TD] [/TD]
[TD]Unit[/TD]
[TD]Rate[/TD]
[TD]Amount (ex. GST)[/TD]
[/TR]
[TR]
[TD]testcode[/TD]
[TD]1 of 4 VMS Boards[/TD]
[TD="align: right"]1/06/2017[/TD]
[TD="align: right"]10/06/2017[/TD]
[TD]9[/TD]
[TD] day [/TD]
[TD]$30.00[/TD]
[TD] $ 270.00[/TD]
[/TR]
[TR]
[TD]testcode[/TD]
[TD]2 of 4 VMS Boards[/TD]
[TD="align: right"]2/06/2017[/TD]
[TD="align: right"]15/06/2017[/TD]
[TD]13[/TD]
[TD] day [/TD]
[TD]$30.00[/TD]
[TD] $ 390.00[/TD]
[/TR]
[TR]
[TD]testcode[/TD]
[TD]3 of 4 VMS Boards[/TD]
[TD="align: right"]4/06/2017[/TD]
[TD="align: right"]10/06/2017[/TD]
[TD]6[/TD]
[TD] day [/TD]
[TD]$30.00[/TD]
[TD] $ 180.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ - [/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top