PDA

View Full Version : Copy Data to Next Empty Rrow



rjw29bs4
11-05-2017, 02:10 PM
hi
i am trying to copy data to next emty row
i have got this far

Sub Macro3()
'
' Macro3 Macro
'


'
Worksheets("copy and close").range("e4").copy Worksheets("INPUT").range("a5")
Worksheets("copy and close").range("a10").copy Worksheets("INPUT").range("b5")
Worksheets("copy and close").range("e3").copy Worksheets("INPUT").range("c5")
ActiveWindow.SmallScroll Down:=0
range("C31").Select
Selection.copy
Sheets("INPUT").Select
range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("copy and close").Select
range("E31").Select
Application.CutCopyMode = False
Selection.copy
Sheets("INPUT").Select
range("D5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("copy and close").Select
End Sub
can you help

Simon Lloyd
11-05-2017, 03:58 PM
Try
Worksheets("copy and close").range("e4").copy Worksheets("INPUT").Range("a" & Rows.Count).End(xlUp).Offset(1, 0)
Worksheets("copy and close").range("a10").copy Worksheets("INPUT").range("b" & Rows.Count).End(xlUp).Offset(1, 0)
Worksheets("copy and close").range("e3").copy Worksheets("INPUT").range("c" & Rows.Count).End(xlUp).Offset(1, 0)

p45cal
11-06-2017, 06:06 AM
Are all the data to be copied to the same row? or to the last cell in each respective column (these could be different if previous copy operations have copied a blank cell).
If a run of the code should copy all the data to the same row, then a single line of code to identify that row can come first, then the later copy code lines can use that same destination row.
Sub blah()
'Set Destn = Worksheets("INPUT").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 'you can use this simpler line to replace the two lines below if column A is suitable for determining the last row (guaranteed no blank cells copied into column A).
NextRow = Worksheets("INPUT").Range("A" & Rows.Count).End(xlUp).Row + 1 'uses column A to identify the next empty row; adjust yourself if column A is not suitable
Set Destn = Worksheets("INPUT").Cells(NextRow, "A")
With Worksheets("copy and close")
.Range("e4").Copy Destn
.Range("a10").Copy Destn.Offset(, 1)
.Range("e3").Copy Destn.Offset(, 2)
Destn.Offset(, 3).Value = .Range("E31").Value
Destn.Offset(, 4).Value = .Range("C31").Value
End With
End Sub