Results 1 to 8 of 8

Thread: Transport data

  1. #1
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Unanswered: Transport data

    We use a proposal that has address, phone, company name, proposal number, etc. that I would like to transfer to another excel spreadsheet.

    I used the "record macro as you go" method to make a quick macro. It works fine but it enters the data over the same line. Not where I have the cursor!

    How can I get the data to enter into the current line of the cursor? And all the cells to the right respectfully?

    Any code that you could offer would be of great assistance.

    Michael
    Gotta to do some code

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Can you post the code, and then show where you want the improvement?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Actually I got it!!


    BUT!!!
    Is there a way to do this:
    I have a form called "List of Proposals Written" that is listed by the Proposal number.

    Is there a way to display on another excel spreadsheet, the most current Proposal number?

    I know Access you use something like Dmax(ProposalID)+1
    What should I do in Excel?

    Any help would be greatly appreciated!
    Michael
    Gotta to do some code

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    is this what you mean (find the largest value in the list)

    Code:
    Dim r As Range
        Set r = Range("A1:a10")
        
        r.Find(Application.WorksheetFunction.Max(r)).Select
    Dave

  5. #5
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Thank You that worked great!! Is there a way to add +1 so I would get the next number?

    Thank You again!
    Michael
    Gotta to do some code

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    I think i know what you want now

    Code:
    Sub test()
    Dim r As Range
    Dim Lastrow As Long
    
        Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        Set r = Range("A1:a" & Lastrow)
        
        Range("A" & Lastrow + 1).Formula = Application.WorksheetFunction.Max(r) + 1
    End Sub
    Dave

  7. #7
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Actually the first code works perfect for what I need. I added some things

    Dim r As Range
    Set r = Range("A1:a65300")

    r.Find(Application.WorksheetFunction.Max(r)).Selec t

    Then I copy the data and paste it on the front of the Proposal page in cell F2.

    I guess I can change the font to white. Then in cell G2 enter a formula (F2+1)
    That should work. Then the person doing a Proposal will know the next Proposal number to Enter!

    Thank YOU
    Michael!!
    Gotta to do some code

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    quick question
    why have you used 65300 in your range select as it usually better to use the last filled row of data, Just a thought
    secondly

    what you could do is to do something like

    Code:
    Sub test()
    Dim r As Range
    Dim Lastrow As Long
    
        Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        Set r = Range("A1:a" & Lastrow)
        
        worksheets("Proposal").Range("G2").formula = worksheets("Data").Range("A" & Lastrow + 1).Formula = Application.WorksheetFunction.Max(r) + 1
    End Sub
    assuming your proposal sheet is called "proposal" and your data sheet is called data

    or instead of turning the text white you could hide the column F

    Dave

Posting Permissions

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