Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 20-10-2009
Member
 
Join Date: Oct 2008
Posts: 115
How to copy data into next empty cell in new sheet

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.

Code:
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
Reply With Quote
  #2  
Old 20-10-2009
Member
 
Join Date: Mar 2008
Posts: 227
Copy data into next empty cell in new sheet

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.

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
Reply With Quote
  #3  
Old 20-10-2009
Member
 
Join Date: Jan 2009
Posts: 79
How to copy data into next empty cell in new sheet

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.
Reply With Quote
  #4  
Old 20-10-2009
Member
 
Join Date: Dec 2008
Posts: 165
How to copy data into next empty cell in new sheet

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.

Code:
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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to copy data into next empty cell in new sheet"
Thread Thread Starter Forum Replies Last Post
Want to copy Excel Chart to another sheet without the Data LuniA MS Office Support 5 25-02-2012 11:09 AM
How to get rid of empty space at start of Excel Cell AdilShaikh18 MS Office Support 2 09-02-2012 07:29 PM
Avoid Copy paste over Data Validation cell Bryant Software Development 7 15-07-2011 10:44 AM
how to copy data from sheet 1 to sheet 2 using macro Messenger Windows Software 3 26-10-2010 06:28 AM
Copy Text from One Cell to Another in Microsoft Excel without copy & paste Computer_Freak Tips & Tweaks 0 18-03-2009 10:00 PM


All times are GMT +5.5. The time now is 08:05 PM.