I Need a Excel VBA code for to copy paste a range of cell

Question

To be short and sweet with my requirement, I need a code to do the conditions below.

Select from range A2:G5
Then check if a sheet named with current date i:e 29-02-2016

If yes, then copy paste the range in A1 leave 3 rows below for the next data to be pasted below that. If no, create a new sheet and name it with current date and then copy paste the range in A1 leave 3 rows below for the next data to be pasted below that.

I tried the below code but it give me error once the current date sheet is created.

Sub Macro1()

    Sheets("Sheet1").Select
    Range("D3:G12").Select
    Selection.Copy
    sheets = "todaysdate".select
    Dim todaysdate As String
    todaysdate = Format(Date, "dd-mm-yyyy")
AddNew:
    Sheets.Add , Worksheets(Worksheets.Count)
    ActiveSheet.Name = todaysdate
    On Error GoTo AddNew
    Sheets(todaysdate).Select
    Range("A1048576").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(3, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Unknown Unknown · Accepted Answer · 2016-02-29T13:09:31

Try these modifications.

Sub Macro1()
    Dim todaysdate As String

    With Worksheets("Sheet1")
        .Range("D3:G12").Copy
    End With

    todaysdate = Format(Date, "dd-mm-yyyy")

    On Error GoTo AddNew
    With Worksheets(todaysdate)
        On Error GoTo 0
        With .Cells(Rows.Count, "A").End(xlUp).Offset(3, 0)
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
        End With
    End With

    Exit Sub
AddNew:
    With Worksheets.Add(after:=Sheets(Sheets.Count))
        .Name = todaysdate
        With .Cells(Rows.Count, "A").End(xlUp)
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
        End With
    End With
End Sub

Step through the modified procedure with the [F8] key to watch how it handles the thrown error and continues on to exit or processes the paste with a three row offset.

I Need a Excel VBA code for to copy paste a range of cell

1 Answers