Macro required

P

PCOR

Hi All
I would sure like to have a macro that will do the following:
1. Select B1
2. Run down the B col until it finds a blank
3. Then paste in that blank line (starting a col B )what has been placed in
the clipboard.
Would appreciate any help
Thanks
 
S

Sandy Mann

Assuming that the copy and paste are both in the same sheet and the data to
be copied when the Macro is invoked is highlighted then try:

Sub CopyIt()
Dim Here As Long

'Set Here to last Row +1 for no blank cells
Here = Cells(Rows.Count, 2).End(xlUp).Row + 1

On Error Resume Next '(If no blank cells)
Here = Columns("B").SpecialCells(xlCellTypeBlanks).Row

Selection.Copy Destination:=Cells(Here, 2)

Application.CutCopyMode = False

End Sub

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
P

PCOR

Thanks forthe help but.....
What I really need is:
I want a macro that will run down Col B ,find the first blank row and paste
what ever is in the clipboard.
Any help appreciated.\
 
S

Sandy Mann

PCOR said:
Thanks forthe help but.....
What I really need is:
I want a macro that will run down Col B ,find the first blank row and
paste what ever is in the clipboard.
Click to expand...

Is that not what my Macro does with the exception of the fact that you have
to high-light the data so that it will be loaded into the clipboard?

How are you loading the data into the clipboard in the first place?

If you want to copy data from other sheets and have it pasted into one
particular sheet, say sheet1 save it to the clipboard and then run this
Macro:

Sub CopyItClipboard()
Dim Here As Long

Application.ScreenUpdating = False

With Sheets("Sheet1")
'Set Here to last Row +1
Here = .Cells(.Rows.Count, 2).End(xlUp).Row + 1

On Error Resume Next '(If no blank cells)
Here = .Columns("B").SpecialCells(xlCellTypeBlanks).Row

.Paste Destination:=.Cells(Here, 2)
End With

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

Replace "Sheet1" with the name of the sheet you want.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
Top