MACRO/VBA help!!! :S new line... etc

[KaZper009]

Im trying to create a price list, been doing some study on how to do this whole VBA/Macro thing... which is pretty cool and does everything i want, so i have been told, still in the learning process... however im stuck on a small part that i would love some help with if possible...

I want to be able to click on an item in a list on one worksheet and then possibly hit a button and that will move it to the sheet that compiles a quote of all the items i wish to include in this.
I have managed to get the first code to work to get the info copied to insert onto the sheet as below...

Sub Cpy()
'
' Cpy Macro
'
'
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Final Pricing Sheet").Select
Range("B20").Select
ActiveSheet.Paste
End Sub

now my next thing i want to be able to insert a new line under where the above goes... and be able to repeat this above process (like a loop sort or thing) so that the data will be sent to the quoting sheet but on the new line thats inserted...

If this makes any sense...

Please helpon the above if ya can... as soon as... been over this for to long now... (Access seems to be able to do this easy... so i found out, but dont have this at work... so gutted... oh and im using Excel 2003)

also wondering if i can have other formulas working in behind all this so it will do a proper quoting tyoe sheet according to the data input in...

Any immediate help would be most appreciated!!!
Thanks Heaps...

Mike

[Charlize]

?[VBA]Sub Cpy()
'
' Cpy Macro
'
'
Dim myrange As Range
Dim mydest As Worksheet
Set mydest = Worksheets("Final Pricing Sheet")
Set myrange = Application.InputBox("Give area to copy", _
"Copy area ..", Type:=8)
myrange.Copy
mydest.Range("B" & mydest.Range("B" & _
Rows.Count).End(xlUp).Offset(1, 0).Row).PasteSpecial xlPasteAll
End Sub[/VBA]Charlize

[KaZper009]

Hey Charlize... thanks for your help...
have put your code in... but it seems to just recreate exactly what i already got... not inserting the new line below the item...
making it work to put in the new data... hope this makes sense...

Mike

[KaZper009]

I lie... it does work... some how i recreated a a link to a different sheet... but i fixed that and got your code working... better then i was expecting... Very cool! thanks so much for your help.... didnt realise it was easily done like that
i can sleep properl at night now

[greymalkin]

to insert a newline in vba you use the vbCrLf command, but since you're in excel you are probably actually just wanting to jump down an extra row.

This may be a dirty solution but if you capture the length of the list to be pasted as an integer (we'll call in listLength) you could after pasting the information do something like:

ActiveCell.Offset(listLength + 2, 0).Select

Then have another string variable (we'll call it lastCellAddress) that remembers that location for the next paste:

lastCellAddress = ActiveCell.Address(False, False) <-- This makes the address look like "A1" instead of "$A$1"

So after the first copy paste you could loop something like this:

<get length of source list as listLength>
<copy the source>
<select the destination sheet>
Range(lastCellAddress).Select
ActiveCell.Paste
ActiveCell.Offset(listLength + 2, 0).Select
lastCellAddress = ActiveCell.Address(False, False)

[KaZper009]

Hey Greymalkin

Wicked... that sounds cool as... will give it a shot!

thanks for all your help

Mike