Copy and Paste into next available row.

[cguy3000]

Alright folks,

I am having an issue with copying and pasting into the next blank cell onto a new sheet.

I currently have a macro in excel that adds a new row at the bottom of a data set and then sets the current date into the cell A1 of that new row and the rest of the cells are blank. What I am trying to do next is take a row of data from a different sheet and past it into the new row i just inserted but starting at cell B1 and then continuing down the row to paste the rest of the values.

This is the current code I have and it wont work. Can anybody either revise this one to work or give me a new one entirely so that it does copy and paste the way I want it to.


Sheets("Weekly Placements").Activate


Range("A100").End(xlDown).Select


r = Selection.Row
Cells(r + 1, 1).EntireRow.Insert
Cells(r, 1).Copy Destination:=Cells(r + 1, 1)

Range("A100").End(xlDown).Select


Dim ts As Date
With Selection
.Value = Now
.NumberFormat = "m/d/yyyy"
'--------------------------------------------------------------------------------
Dim LastRow As Long
LastRow = Sheets("Source Data2").UsedRange.Rows.Count
Sheets("Source Data2").Range("C4:N4").Copy
Sheets("Weekly Placements").Range ("B" & LastRow + 1)

Sheets("Weekly Placements").Select


End With
End Sub



Now everything below the ------- line is the part of the code in question that doesnt work correctly. Everything above the -------- currently works and adds a new row of data at the first empty cell of A column and adds the current date.

The references are all right as well so if you guys know how to make that copy and paste work that would be so helpful!!

 

[fhqwgads]

maybe change

Sheets("Source Data2").Range("C4:N4").Copy
Sheets("Weekly Placements").Range ("B" & LastRow + 1)

to

Sheets("Source Data2").Range("C4:N4").Copy
Sheets("Weekly Placements").Range ("B" & LastRow + 1).Paste

 

[DanteAmor]

Try this

Adjust the sheet for the LastRow

    Dim LastRow As Long
    LastRow = Sheets("[COLOR=#ff0000]Weekly Placements[/COLOR]").UsedRange.Rows.Count

    Sheets("Source Data2").Range("C4:N4").Copy Sheets("Weekly Placements").Range("B" & LastRow + 1)   'In the same line

Or

    Dim LastRow As Long
    LastRow = Sheets("[COLOR=#ff0000]Weekly Placements[/COLOR]").UsedRange.Rows.Count
    Sheets("Source Data2").Range("C4:N4").Copy
    Sheets("Weekly Placements").Range("B" & LastRow + 1).PasteSpecial xlAll

 

[kweaver]

Does this do it?

Sub Weekly()
Dim LR As Long
Sheets("Weekly Placements").Activate
LR = Cells(Rows.Count, "A").End(xlUp).Row
Cells(LR + 1, 1).Value = Date
Cells(LR + 1, 1).NumberFormat = "m/d/yyyy"
Range("B" & (LR + 1) & ":M" & (LR + 1)) = Sheets("Source Data2").Range("C4:N4").Value
End Sub