View Full Version : How to paste from module to worksheet

03-14-2012, 12:02 PM
Hello! I'm new here, so don't be too critical. I'm a chemical engineer that only touched on VBA (2007) back in school, and I'm trying to learn how to use it again. So anyway, time for business:

I've made a module that allows user input, and pastes those values into the worksheet in selected cells. What I WANT to do is make a log of those cells. So, have it paste Product to "C3", Size to "C5", etc...AND below a set row (say, 35) look for the next empty row to paste all the data in line. I'm guessing it's something after:

ThisWorkbook.Sheets("Dry").Range("H11") = Fines.Value
ThisWorkbook.Sheets("Dry").Range("H14") = Overs.Value
ThisWorkbook.Sheets("Dry").Range("H17") = Dust.Value
ThisWorkbook.Sheets("Dry").Range("H21") = Down.Value
'these paste the values in the F/O/D/D ^ input boxes to the ranges.
Unload Me
End Sub

I've looked all over the internet for something, but I can't find anything that refers from a module (not that there should be much difference). Any help is much appreciated!

03-14-2012, 12:18 PM
put something (anything, say, a header) in cell A35, then add this line before unload me:
Sheets("Dry").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 4) = Array(Fines.Value, Over.Value, Dust.Value, Down.Value)

03-14-2012, 12:30 PM
Well, p45cal, I guess I should specify another part - the data I need pasted consists of 7 items: three as text, four as numeric values. The code you posted worked for one of the four (the Dust.Value). I tried to change it to [...Fines.Text...], but alas, no luck.

03-14-2012, 12:58 PM
I misspelt overs.value, but what are overs, fines etc. on the userform?
I'll try then to reproduce your situation.
With regard to 7 items rather than 4, change the 4 to a seven and make sure there are 7 items in the array. I haven't tested what I suggested.