How to copy data into next empty cell in new sheet

[Saaarc]

Hi,

I have created a file in excel where i want to copy data into next empty cell in new sheet and this changes are going to happen on monthly bases but the info in this cell should then be inserted into the first blank cell in Column A in Sheet 2. I have try to use the below code but it didn't help me a lot. Can anyone help me out with this issue.

Sub Copyt_Nextsheet() 
With Sheets("Sheet2") 
n = .Cells(Rows.Count, "A").End(xlUp).Row + 1 
.Cells(n, "A").Resize(, 4).Value = _ 
Cells(1, "A").Resize(, 4).Value 
End With 
End Sub

[DllD]

If you want to copy data into next empty cell in new sheet then try to use the following code i hope by using the following code.

Sub Rows_with_numbers() 
Dim X11 As Long 
Dim Last_row As Long 
Dim Source1 As Worksheet 
Dim Destination1 As Worksheet 
Dim Rows_with_numbers As Range 
Set Source1 = Worksheets("name") 
Set Destination1 = Worksheets("name") 
With Source1 
Last_row = .Cells(.Rows.Count, "E").End(X1lUp).Row 
For X1 = 2 To Last_row 
If IsNumeric(.Cells(X1, "E").Value) And .Cells(X1, "E").Value <> "" Then 
If Rows_with_numbers Is Nothing Then 
Set Rows_with_numbers = .Cells(X1, "E") 
Else 
Set Rows_with_numbers = Union(Rows_with_numbers, .Cells(X1, "E")) 
End If 
End If 
NeX1t 
If Not Rows_with_numbers Is Nothing Then 
Rows_with_numbers.EntireRow.Copy Destination1.Range("A4") 
End If 
End With 
MsgBoX1 "Data has been updated !", vbEX1clamation + vbInformation, " Name" 
End Sub

[Jayden]

I am also facing similar type of issue where in Sheet1 is my current working sheet and A6 displays the current date which is in dd/mm/yyyy format. Over i would like to have a macro button which will check out A6 cell and if it's first of the month for example 01/10/2009 then it should insert a new sheet into the workbook and put the name as Sheet2. then i should copy Cell A6:K6and pastes the data in to monthly sheet starting in A3 to K3 and this process should start with each new month. Any help would be appreciated.

[Bassus]

Try to run the following macro which will create a new sheet on first of every month and then it will copy data into it's respective sheet and at the same time i have inserted a condition to check whether the copied data exist or not. In this way it will restrict copying more than one time in a day.

Dim sht_Main As Worksheet 
    Dim cur_date As Date 
    Dim sht_name As String 
    Dim sht_to_paste As Worksheet 
     
    Set sht_Main = Sheets("Main") 
    cur_date = sht_Main.Range("A17") 
    sht_name =  Format(cur_date, "mmm yy") 
     
    If sht_Main.Range("IV1") = Date Then 
        MsgBox "Today's Data already copied", vbCritical 
        Exit Sub 
    End If 
     
     
    If cur_date = DateSerial(Year(cur_date), Month(cur_date), 1) Then 
        Worksheets.Add After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = sht_name 
    End If 
     
    Set sht_to_paste = Sheets(sht_name) 
     
    sht_Main.Range("A17:K17").Copy 
     

    sht_to_paste.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues 
    Application.CutCopyMode = False 
    sht_Main.Range("IV1").Value = Date ' Insert todays date in main sheet Cell IV1
     
     
End Sub