Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2003: Filling a form with data from spreadsheet

    Hi all,

    I had a thread about this earlier but I could not find it with a search. I have a workbook that has several worksheets. I found some VBA code on the web. I figured out how to adapt it to fill a form ( the "Cover Letter" tab) with data. The data is pulled from a worksheet called "Numbering". Now I need the macro to pull the data from a worksheet called "Update Notice List".

    I thought from the way the code looked that it would simply pull data from the row that had been selected on any worksheet. It doesn't. Can you help me to change the code so that it pulls data from the "Update Notice List" worksheet?


    TIA

    Bret


    Here is the code:
    Sub coverletter()
    Dim shInv As Worksheet

    Set shInv = ThisWorkbook.Sheets("Cover Letter")
    If Selection.Column <> 2 Or Selection.Value = "" Then
    MsgBox "There is nothing to export!" & Chr(10) & "Select a cell with data from column B," & Chr(10) & "and try again"
    Exit Sub
    End If
    rw = Selection.Row
    With shInv
    .Cells(2, 3).Value = Cells(rw, 8).Value
    .Cells(3, 3).Value = Cells(rw, 10).Value
    .Cells(3, 10).Value = Cells(rw, 9).Value
    .Cells(4, 3).Value = Cells(rw, 11).Value

    End With
    Cells(rw, "B").Select
    shInv.Select
    End Sub

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,073
    Thanks
    12
    Thanked 36 Times in 35 Posts
    Why not just create the cell references/links in the Cover Letter sheet to the material you have elsewhere in the workbook?

  4. #3
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The spreadsheet I am pulling the data from contains 245 rows (records). I need specific data from those rows to populate a form letter so that folks can be notified that documents they own need to be updated.

  5. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,073
    Thanks
    12
    Thanked 36 Times in 35 Posts
    My thinking is that by the time you make the cell references in the macro, you could make them directly in the form sheet. But, maybe I'm missing something here.

  6. #5
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I just have to make the cell references once in the macro. As it is now, if I place the cursor in the "B" column of the "Numbering" worksheet. The document name, review date, review frequency are all populated to the appropriate fields in the "Cover Letter" tab filling in the cells on the form when I run the macro. I need to add the document owner's name to the form. That data currently resides in another worksheet in the same workbook.

    In the code that I got from the WWW, I don't see any reference to the worksheet the data is being pulled from i.e. "Numbering". So, I don't know how to get it to pull the data I need from the worksheet named "Update Notice List".

    Would attaching the spreadsheet be useful?

    Thanks,

  7. #6
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Got it figured out! The macro does work for whichever row on whichever worksheet is selected. I was selecting a cell that didn't have any data, therefore the macro was giving me the error msg that was specified if there wasn't any data in the selected cell. Phew! Thanks for the help.

Posting Permissions

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