Fixing macro to choose a blank row

[[email protected]]

Hi

I am trying to get this macro to paste the data into a blank row on
another sheet any ideas?

Private Sub SUBMIT_Click()
Application.ScreenUpdating = False
Sheets("LOG TASK").Select
Range("b1:b10").Select
Selection.Copy
Sheets("TASKS TO DO").Select
Range("A1").End(xlDown)(2).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

End Sub

[Norman Jones]

Hi Nick,

One way:

'=============>>
Private Sub SUBMIT_Click()
Dim srcRng As Range
Dim destRng As Range

Set srcRng = Sheets("LOG TASK").Range("B1:B10")
Set destRng = Sheets("TASKS TO DO"). _
Cells(Rows.Count, "A").End(xlUp)(2)

With srcRng
destRng.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

End Sub
'<<=============


---
Regards,
Norman


<[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I am trying to get this macro to paste the data into a blank row on
> another sheet any ideas?
>
> Private Sub SUBMIT_Click()
> Application.ScreenUpdating = False
> Sheets("LOG TASK").Select
> Range("b1:b10").Select
> Selection.Copy
> Sheets("TASKS TO DO").Select
> Range("A1").End(xlDown)(2).Select
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=True
>
> End Sub
>

[Nick Marcell]

Thanks , that works great. the only problem i have is the pasted data
needs to go from being in a column to a row.

[Norman Jones]

Hi Nick,

> Thanks , that works great. the only problem i have is the pasted data
> needs to go from being in a column to a row.

Try:
'=============>>
Private Sub SUBMIT_Click()
Dim srcRng As Range
Dim destRng As Range

Set srcRng = Sheets("LOG TASK").Range("B1:B10")
Set destRng = Sheets("TASKS TO DO"). _
Cells(Rows.Count, "A").End(xlUp)(2)

With srcRng
destRng.Resize(1, .Rows.Count).Value = _
Application.Transpose(.Value)
End With

End Sub
'<<=============

Alternatively, try:
'=============>>
Private Sub SUBMIT_Click()
Dim srcRng As Range
Dim destRng As Range

Set srcRng = Sheets("LOG TASK").Range("B1:B10")
Set destRng = Sheets("TASKS TO DO"). _
Cells(Rows.Count, "A").End(xlUp)(2)

srcRng.Copy
destRng.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
Application.CutCopyMode = False
End Sub
'<<=============


---
Regards,
Norman

[Nick Marcell]

Thats great first one works a charm Thank you