Macro help needed

[Joy]

I'm learning macros at the moment and thought I'd venture to write one which
I needed myself but I could do with some help, since I spent hours today
trying everything that occured to me and searching the internet when I
should be studying since I've an exam on macros tomorrow.

I import an list of people from the internet by using a web query in excel
(labelled provisions) I selected a group using the first letter of the last
name and used record macro to copy the selection and paste the values into
another worksheet labelled with that letter, eg labelled A. At the end of
the macro, I'd like to be able to move the cursor to the next blank line in
worksheet A, Col 1 so that the next time I run the macro it adds to the
list. The names are in Col 2, todays date is in Col 1. I'm using MS office
2003.

Selection.Copy
Sheets("A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks
Joy

 

[Harald Staff]

Hi Joy

In the risk of confusing you right before your exam; You don't have to go
places or select cells to move information or get things done. Try the
following code:

Function NextCell(ShName As String) As Range
Set NextCell = Sheets(ShName).Cells(65000, 1).End(xlUp).Offset(1, 0)
End Function

Sub Test()
Dim PasteCell As Range
Set PasteCell = NextCell("A")
Selection.Copy
PasteCell.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

HTH. Best wishes Harald

 

[Joy]

Thanks for that, I'll have to study it later.

Hi Joy

In the risk of confusing you right before your exam; You don't have to go
places or select cells to move information or get things done. Try the
following code:

Function NextCell(ShName As String) As Range
Set NextCell = Sheets(ShName).Cells(65000, 1).End(xlUp).Offset(1, 0)
End Function

Sub Test()
Dim PasteCell As Range
Set PasteCell = NextCell("A")
Selection.Copy
PasteCell.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

HTH. Best wishes Harald