Transport data

surfacesys · #1 (**permalink**) 08-18-04, 11:24

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

shades · #2 (**permalink**) 08-18-04, 17:43

Can you post the code, and then show where you want the improvement?

surfacesys · #3 (**permalink**) 08-19-04, 10:25

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

DavidCoutts · #4 (**permalink**) 08-19-04, 10:55

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

surfacesys · #5 (**permalink**) 08-19-04, 11:16

Thank You that worked great!! Is there a way to add +1 so I would get the next number?

Thank You again!
Michael

DavidCoutts · #6 (**permalink**) 08-19-04, 11:22

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

surfacesys · #7 (**permalink**) 08-19-04, 12:03

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!!

DavidCoutts · #8 (**permalink**) 08-20-04, 01:47

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

Internet Services

Web Development

Digital Marketing

Consumer Tech