PDA

View Full Version : Copy a range of cells



netwerkz
07-15-2016, 03:51 PM
Hi All,

I have a UserForm that is ran on a sheet called "fieldDataCalc" where a user will enter data into specific fields and upon pressing the "Accept Data" CommandButton, the data will be dumped into "fieldDataCalc". After that, I want the data to be copied and pasted on the next available line on another sheet called "fieldData".

So far, the only VBA Code I have is for the "Accept Data" action...


Private Sub cmdAccept_Click()
Dim RowCount As Long
Dim ctl As Control

RowCount = Worksheets("fieldDataCalc").Range("A2").CurrentRegion.Rows.Count
With Worksheets("fieldDataCalc").Range("A2")
.Offset(RowCount, 0).Value = Me.txtBox_fieldDataCalc_dateDelivered.Value
.Offset(RowCount, 1).Value = Me.comboBox_fieldDataCalc_fieldName.Value
.Offset(RowCount, 2).Value = Me.txtBox_fieldDataCalc_acres.Value
.Offset(RowCount, 3).Value = Me.comboBox_fieldDataCalc_crop.Value
End With

RowCount = Worksheets("fieldDataCalc").Range("E2").CurrentRegion.Rows.Count
With Worksheets("fieldDataCalc").Range("E2")
.Offset(RowCount, 0).Value = Me.txtBox_fieldDataCalc_product1_gallonsLoaded.Value
.Offset(RowCount, 1).Value = Me.txtBox_fieldDataCalc_product2_gallonsLoaded.Value
.Offset(RowCount, 2).Value = Me.txtBox_fieldDataCalc_product3_gallonsLoaded.Value
.Offset(RowCount, 3).Value = Me.txtBox_fieldDataCalc_product4_gallonsLoaded.Value
.Offset(RowCount, 4).Value = Me.txtBox_fieldDataCalc_product5_gallonsLoaded.Value
.Offset(RowCount, 5).Value = Me.txtBox_fieldDataCalc_product6_gallonsLoaded.Value
End With


End Sub
...and...



Option Explicit
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
, which is on the fieldDataCalc sheet.

16626
16627

I appreciate any assistance offered!

Leith Ross
07-16-2016, 11:13 AM
Hello netwerkz,

What is your code not doing that you it want it to do?

snb
07-17-2016, 03:37 AM
Basically 1 line suffices:



sheets("fieldDataCalc").cells(rows.count,1).end(xlup).resize(,10)=array(txtBox_fieldDataCalc_date Delivered,.comboBox_fieldDataCalc_fieldName,txtBox_fieldDataCalc_acres,comb oBox_fieldDataCalc_crop,txtBox_fieldDataCalc_product1_gallonsLoaded,
txtBox_fieldDataCalc_product2_gallonsLoaded, ,,,,).Value


NB. If you reduce the length of the textboxnames & comboboxnames it's all much easier to write.

Paul_Hossler
07-17-2016, 06:40 AM
After that, I want the data to be copied and pasted on the next available line on another sheet called "fieldData".






Should this be called "fieldData" and not "fieldDataCalc"?




RowCount = Worksheets("fieldDataCalc").Range("E2").CurrentRegion.Rows.Count
With Worksheets("fieldDataCalc").Range("E2")

p45cal
07-17-2016, 08:22 AM
snb, does an array have a .Value property?

snb
07-17-2016, 08:50 AM
You can amend it if you like ...

p45cal
07-17-2016, 09:00 AM
the OP will have to…

netwerkz
07-18-2016, 06:52 AM
Hello netwerkz,

What is your code not doing that you it want it to do?

When the user opens the userform, they will enter the data and click the Accept button. Upon click, the data does drop into the fieldDataCalc sheet and the formulas are ran correctly. What I want to happen afterwards is to have the calculated data be copied over to another worksheet called fieldData. After the row is populated, the user will enter new data and that will follow the same process, which will be populated on the next available line on fieldData.

netwerkz
07-18-2016, 07:10 AM
Should this be called "fieldData" and not "fieldDataCalc"?




RowCount = Worksheets("fieldDataCalc").Range("E2").CurrentRegion.Rows.Count
With Worksheets("fieldDataCalc").Range("E2")


Hi Paul, I'm not sure if the question was directed at me, but I'll comment to help clarify. The two sheets are similarly named, but both have different uses. The fieldData sheet is where all of the calculated output data is compiled and is used for reporting to the client. The fieldDataCalc page is used to take input data, make a combination of specific calculations.

Paul_Hossler
07-26-2016, 07:08 AM
@netwerkz



I have a UserForm that is ran on a sheet called "fieldDataCalc" where a user will enter data into specific fields and upon pressing the "Accept Data" CommandButton, the data will be dumped into "fieldDataCalc". After that, I want the data to be copied and pasted on the next available line on another sheet called "fieldData".




Hi Paul, I'm not sure if the question was directed at me, but I'll comment to help clarify. The two sheets are similarly named, but both have different uses. The fieldData sheet is where all of the calculated output data is compiled and is used for reporting to the client. The fieldDataCalc page is used to take input data, make a combination of specific calculations.


I was asking because the code below does NOT appear to do what's in red. I wasn't sure if the below should have been just 'fieldData', or if the part in red above comes later




RowCount = Worksheets("fieldDataCalc").Range("E2").CurrentRegion.Rows.Count
With Worksheets("fieldDataCalc").Range("E2")
.Offset(RowCount, 0).Value = Me.txtBox_fieldDataCalc_product1_gallonsLoaded.Value
.Offset(RowCount, 1).Value = Me.txtBox_fieldDataCalc_product2_gallonsLoaded.Value
.Offset(RowCount, 2).Value = Me.txtBox_fieldDataCalc_product3_gallonsLoaded.Value
.Offset(RowCount, 3).Value = Me.txtBox_fieldDataCalc_product4_gallonsLoaded.Value
.Offset(RowCount, 4).Value = Me.txtBox_fieldDataCalc_product5_gallonsLoaded.Value
.Offset(RowCount, 5).Value = Me.txtBox_fieldDataCalc_product6_gallonsLoaded.Value
End With