Consulting

Results 1 to 4 of 4

Thread: How to paste from module to worksheet

  1. #1
    VBAX Newbie
    Joined
    Mar 2012
    Posts
    2
    Location

    Question How to paste from module to worksheet

    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!

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,875
    put something (anything, say, a header) in cell A35, then add this line before unload me:
    [vba]Sheets("Dry").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 4) = Array(Fines.Value, Over.Value, Dust.Value, Down.Value)
    [/vba]
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Newbie
    Joined
    Mar 2012
    Posts
    2
    Location
    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.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,875
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •