PDA

View Full Version : VBA Code help- Paste to next empty cell in another worksheet



imsonny
07-29-2011, 09:18 AM
How do I get the following code to paste into the next available cell in Sheets("P.O. SHEET") (G10), if (G10) is empty. Then to the next cell beneath it (G11), if (G11) is empty then to cell beneath it, and so on...

Sub C_3()
' C_3 Macro
Sheets("BMS").Select
Range("C3").Select
Selection.Copy
Sheets("P.O. SHEET").Select
Range("G10").Select
ActiveSheet.Paste
Sheets("BMS").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

any help at all will be very much appreciated, thank you

CatDaddy
07-29-2011, 10:34 AM
Sub C_3()
Sheets("BMS").Select
Range("C3").Select
Selection.Copy Destination:=ActiveWorkbook.Sheets("P.O. SHEET").Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
End Sub

CatDaddy
07-29-2011, 10:38 AM
Sub C_3()
Sheets("BMS").Select
Range("C3").Select
If ActiveWorkbook.Sheets("P.O. SHEET").Range("G10").Value = Empty Then
Selection.Copy Destination:=ActiveWorkbook.Sheets("P.O. SHEET").Range("G10")
Else
Selection.Copy Destination:=ActiveWorkbook.Sheets("P.O. SHEET").Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
End If
End Sub


sorry i didnt see that G10 had to be the first option...the earlier version would just look for first empty cell in column 7 (G)

Kenneth Hobs
07-29-2011, 10:38 AM
Sub C_3()
WorkSheets("BMS").Range("C3").Copy _
WorkSheets("P.O. SHEET").Range("G" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub

imsonny
07-29-2011, 01:01 PM
Thank you everyone for all your help. It definitely helpped me move on with my code.

Thank you
Sonny