Copy and paste range

[Sharid]

I'm a bit suck on this one,

In need some help please to work this one out.

I need to copy ALL DATA on sheet2 Range A-M and paste it in next blank row on sheet1 column B so all the copied data will sit in columns B to N

And I need Todays date going into column A for each row that has been copied over,

Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Sheet2")
Set pasteSheet = Worksheets("Sheet1")

copySheet.Range("A1:M1").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

Thanks for having a look

 

[kvsrinivasamurthy]

Change this line

pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

as

pasteSheet.Cells(Rows.Count, [COLOR=#ff0000][B]2[/B][/COLOR]).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

 

[Sharid]

Hi

Thanks fot that code update, it now pastes the coppied data in to column B, which is good.

My range is wrong as it only copies A1:M1 not all the data in that sheet in this range so if there was 10 rows, then all ten will be copied in range A1:M1. If 1000 rows the 1000 will be copied.

Also I can't get it to put todays date into column A

Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Sheet2")
Set pasteSheet = Worksheets("Sheet1")

copySheet.Range("A1:M1").Copy
pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("B:B").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.row, "A").Value = Int(Now)
        Else
            Cells(Cell.row, "A").Value = ""
        End If
    End If
Next Cell

Application.ScreenUpdating = True

 

[Sharid]

Ok I have fixed my code now so that when if pastes in the date in to column B it also adds todays date in Column A

I am still stuck on copying all the data from sheet2 - My range only does 1 row

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Sheet2")
Set pasteSheet = Worksheets("Sheet1")

[COLOR=#ff0000]copySheet.Range("A1:M1").Copy[/COLOR]
pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    With Range("A2:A" & LastRow)
        .Value = Date
        .NumberFormat = "dd/mm/yy"
    End With

Application.ScreenUpdating = True
End Sub

 

[kvsrinivasamurthy]

Change this line
copySheet.Range("A1:M1").Copy

as
copySheet.Range("A1").CurrentRegion.Copy

 

[Sharid]

This is good, the problem now is

1) It copies the heading from sheet2 over to sheet1
2) I loose all format from sheet2, some rows have colors etc.

Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Sheet2")
Set pasteSheet = Worksheets("Sheet1")

copySheet.Range("A1").CurrentRegion.Copy
pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    With Range("A2:A" & LastRow)
        .Value = Date
        .NumberFormat = "dd/mm/yy"
    End With

Application.ScreenUpdating = True

 

[Sharid]

Still stuck on this one, If anyone could please help on the last few steps, I would save my life

 

[kvsrinivasamurthy]

Change this line

copySheet.Range("A1").CurrentRegion.Copy
pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

as

copySheet.Range("A1").CurrentRegion.Offset(1, 0).Copy
pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Paste

 

[Sharid]

I'm getting a run time error message 438

"Object doesn't support this property or method"

on this like

pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Paste

 

[kvsrinivasamurthy]

For copy and paste use

copySheet.Range("A1").CurrentRegion.Offset(1, 0).Copy pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)