PDA

View Full Version : Copy to a specific cell



timecarter
08-09-2010, 02:26 PM
Hello folks. This is my first time posting here as Im in a bind at work and need to get a quick macro done.

So I need to copy a line from one spreedsheet to another spreadsheet in the same workbook. I used the record feature and got this:

Sub Glycine()
'
' Glycine Macro
'
'
ActiveWindow.SmallScroll Down:=-6
ActiveCell.Range("A1:G1").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-3
Sheets("Purchase Order").Select
ActiveSheet.Paste
End Sub

What I need is the ability to paste it to a specific cell in the Purchase Order sheet, cell B3. However, if B3 is occupied with text I then need it to copy to B4 and so on to B5 all the way to B18.

Im hoping someone can help me. Thank you so much!
-Tim

Bob Phillips
08-09-2010, 03:10 PM
Sub Glycine()
'
' Glycine Macro
'
With Sheets("Purchase Order")

ActiveSheet.Range("A1:G1").Copy .Cells.Rows.Count,"B").End(xlUp).Offset(1, 0)
End With
End Sub

Artik
08-09-2010, 03:25 PM
...Or

Sub Glycine_1()
Dim FirstEmptyCell As Range
Dim a As Integer

With Worksheets("Purchase Order")
a = Application.WorksheetFunction.CountA(.Range("B3:B18"))

If a > 15 Then
MsgBox "Range B3:B18 in """ & .Name & """ is full!", vbExclamation
Exit Sub
End If

Set FirstEmptyCell = .Range("B3").Offset(a)
End With

ActiveCell.Resize(, 7).Copy FirstEmptyCell

Set FirstEmptyCell = Nothing
End Sub

Artik

timecarter
08-09-2010, 04:26 PM
Worked beautifully. Thank you for the quick reply!