VBA form data to spreadsheet

[Christine81]

hi all, I'm new to vba but managing to bumble my way through
I've created a quote template in VBA forms and I've managed to get as far as creating a submit button which transfers the data to an excel spreadsheet. is there any way of getting the data split to multiple rows?

I've added a picture as i don't think I'm explaining very well. the fields I've highlighted in yellow when transferred to the spreadsheet behind i want those fields to populate underneath each other rather than next to each other. can this be done ??

 

[PeteWright]

Sure. Something like this:

Cells(ROWINDEX, COLINDEX)
Cells(ROWINDEX + 1, COLINDEX)
Cells(ROWINDEX + 2, COLINDEX)
Cells(ROWINDEX + 3, COLINDEX)
Cells(ROWINDEX + 4, COLINDEX)

But would be much easier to tell if you could share your VBA code (at least the part that copies data to the sheet) and some sample data.

 

[Christine81]

sorry for the late response, here is the code i used

Private Sub CommandButton1_Click()
Dim A As Long
A = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(A, 1).Value = quoteref.Value
Cells(A, 2).Value = customer.Value
Cells(A, 3).Value = siteadd.Value
Cells(A, 4).Value = deldate.Value
Cells(A, 5).Value = esthire.Value
Cells(A, 6).Value = createdby.Value
Cells(A, 7).Value = createddate.Value
Cells(A, 8).Value = reqby.Value
Cells(A, 9).Value = telno.Value
Cells(A, 10).Value = email.Value
Cells(A, 11).Value = code1.Value
Cells(A, 12).Value = code2.Value
Cells(A, 13).Value = code3.Value
Cells(A, 14).Value = code4.Value
Cells(A, 15).Value = code5.Value
Cells(A, 16).Value = code6.Value
Cells(A, 17).Value = qty1.Value
Cells(A, 18).Value = qty2.Value
Cells(A, 19).Value = qty3.Value
Cells(A, 20).Value = qty4.Value
Cells(A, 21).Value = qty5.Value
Cells(A, 22).Value = qty6.Value
Cells(A, 23).Value = description1.Value
Cells(A, 24).Value = description2.Value
Cells(A, 25).Value = description3.Value
Cells(A, 26).Value = description4.Value
Cells(A, 27).Value = description5.Value
Cells(A, 28).Value = description6.Value
Cells(A, 29).Value = hp1.Value
Cells(A, 30).Value = hp2.Value
Cells(A, 31).Value = hp3.Value
Cells(A, 32).Value = hp4.Value
Cells(A, 33).Value = hp5.Value
Cells(A, 34).Value = hp6.Value
Cells(A, 35).Value = up1.Value
Cells(A, 36).Value = up2.Value
Cells(A, 37).Value = up3.Value
Cells(A, 38).Value = up4.Value
Cells(A, 39).Value = up5.Value
Cells(A, 40).Value = up6.Value
Cells(A, 41).Value = note.Value
quoteref.Value = ""
customer.Value = ""
siteadd.Value = ""
deldate.Value = ""
esthire.Value = ""
createdby.Value = ""
createddate.Value = ""
reqby.Value = ""
telno.Value = ""
email.Value = ""
code1.Value = ""
code2.Value = ""
code3.Value = ""
code4.Value = ""
code5.Value = ""
code6.Value = ""
qty1.Value = ""
qty2.Value = ""
qty3.Value = ""
qty4.Value = ""
qty5.Value = ""
qty6.Value = ""
description1.Value = ""
description2.Value = ""
description3.Value = ""
description4.Value = ""
description5.Value = ""
description6.Value = ""
hp1.Value = ""
hp2.Value = ""
hp3.Value = ""
hp4.Value = ""
hp5.Value = ""
note.Value = ""

here is my sample data. the top bit is how this code puts the info into my spreadsheet, the bit i have circled in red is how id like it to come out

 

[dmt32]

Hi,
are you able to place copy of your workbook with sample data in a file sharing site like dropbox & provide a link to it?

Dave

 

[Christine81]

thanks dave, ive never done file sharing before but ill give it a go

 

[dmt32]

thanks dave, ive never done file sharing before but ill give it a go

If can more helpful to forum - just ensure remove any personal data before uploading to sharing site.

Dave