Copy row with data to second sheet

[shy07014]

hi there,
i need help with a bit of code:

i need to have a macro that searches columns C and E (on sheet1) for any cell with data.
if finds data in either C or E, it copies that entire row to sheet2 in the next avilable row, and adds a date stamp to column F

thanx in advance

 

[HarryS]

Something like this may help you on your way

Private Sub CommandButton1_Click()
' if sheet1 is the active sheet you con leave out Sheets("sheet1").
' sheet 2 start
    outrow = 4
    'number of rows in sheet 1
    numrow = 40
    For rr = 1 To numrow  ' you nam need to start from row x
    
        If Sheets("sheet1").Cells(rr, 3) <> "" Or Sheets("sheet1").Cells(rr, 5) <> "" Then
            outrow = outrow + 1  ' next row in sheet 2
            ' do not know how much of the row you want
            For cc = 1 To 10   ' adjust this 10  for number of columns
                Sheets("sheet2").Cells(outrow, cc) = Sheets("sheet1").Cells(rr, cc)
            Next cc
            Sheets("sheet2").Cells(outrow, 6) = Date
        End If
    Next rr
End Sub

 

[Peter_SSs]

shy07014

Welcome to the MrExcel board!

You could also try this. (Make a backup of your file first)

My code assumes that:
1. The data on Sheet1 starts on row 2 with headings in row 1.
2. Sheet2, column F has something in it (eg a heading like "Date"). It may or may not already have some date stamps as well. I am using this column to help determine what row to copy the next lot of data to.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopyRows()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Ws1 <SPAN style="color:#00007F">As</SPAN> Worksheet, Ws2 <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> Ws1 = Sheets("Sheet1"): <SPAN style="color:#00007F">Set</SPAN> Ws2 = Sheets("Sheet2")<br>    lr2 = Ws2.Range("F" & Rows.Count).End(xlUp).Row<br>    Ws1.UsedRange.Offset(1).EntireRow.Copy Destination:= _<br>            Ws2.Range("A" & lr2 + 1)<br>    <SPAN style="color:#00007F">With</SPAN> Ws2.Range("C" & lr2 & ":E" & lr2 + Ws1.UsedRange.Rows.Count)<br>        .Resize(, 1).Offset(1, 3).Value = <SPAN style="color:#00007F">Date</SPAN><br>        .AutoFilter Field:=1, Criteria1:="="<br>        .AutoFilter Field:=3, Criteria1:="="<br>        .Offset(1).Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> Ws1 = Nothing: <SPAN style="color:#00007F">Set</SPAN> Ws2 = Nothing<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[HarryS]

Peter
Where do the drop down 'sort boxes' in row 2 come from on a blank sheet2 ???

and where do they go to if you repost the data ???

Good code but it is complex for me and yet very useful for increasing my knowledge of excel. However how does it seem to a first time postee.

 

[Peter_SSs]

ADVERTISEMENT

Peter
Where do the drop down 'sort boxes' in row 2 come from on a blank sheet2 ???

Well, if Sheet2 was completely blank the AutoFilter drop-downs (Filters not "sort boxes") would go in C1:E1 and that would be fine as I understand the problem. However, I did list in my assumptions that Sheet2 had something in column F. In that case, the AutoFilter drop-down will go in columns C:E but in the row where the last data in column F is.

... and where do they go to if you repost the data ???

The second (and subsequent) time(s) the code is run, the drop-downs will still go in columns C:E and in the row where the last data in column F is. That means they will actually be filtering the new data as that new data will start in the next row after the last data in column F.

Good code but it is complex for me and yet very useful for increasing my knowledge of excel.

Thank you.

However how does it seem to a first time postee.

Well, that depends on the level of knowledge the first-time poster has. I have no idea in advance, but if the poster wants further explanation, they can always ask - just as you did.

It all depends on just how you approach a task. Your code (I haven't actually tested it) loops through each row in Sheet1 looking for data in either col C or col E. If it finds data, that row is copied to Sheet2 and a date stamp applied. I have no problem with that logic though I do note that looping can be a bit slow if there are lots of rows, so I usually try to avoid it if possible.

I just took a different approach: Copy everything across to Sheet2, apply a date stamp to the whole copied range and then delete (at once using AutoFilter) all the copied rows that had no data in columns C or E.

I hope this answers your questions.

 

[HarryS]

Thank you for that.
My mathematics, mathematical oop type programming is ok but is based on 6502 assembler, basic, pascal, delphi, java script, background of loops and arrays. I am enjoying the experience of learning the array - range based concepts of Excel and do very much appreciate the advice of people with expert knowledge in this area. I have reached the level of understanding that I have much yet to learn.

 

[shy07014]

ADVERTISEMENT

Thanx Harryx for the code, it copy it to the second page in the exact format that i want. except for one thing, it always starts on the 4th column, and any time i run the macro again, it overwrites the previous info. im sure its an easy fix to tell it to search for the next empty row, but i just dont know how....help me please.

thanx alot so far.

 

[Peter_SSs]

Thanx Harryx for the code, it copy it to the second page in the exact format that i want. except for one thing, it always starts on the 4th column, and any time i run the macro again, it overwrites the previous info. im sure its an easy fix to tell it to search for the next empty row, but i just dont know how....help me please.

thanx alot so far.

Did you try my suggestion as well?

 

[shy07014]

yes i did, but all these drop down menus were created it each cell, wasnt sure what to, but harrys's code worked great, it just didnt place it in the next empty cell.
so i was going to take this:

Private Sub CommandButton1_Click()
' if sheet1 is the active sheet you con leave out Sheets("sheet1").
' sheet 2 start
    outrow = 4
    'number of rows in sheet 1
    numrow = 40
    For rr = 1 To numrow  ' you nam need to start from row x
    
        If Sheets("sheet1").Cells(rr, 3) <> "" Or Sheets("sheet1").Cells(rr, 5) <> "" Then
            outrow = outrow + 1  ' next row in sheet 2
            ' do not know how much of the row you want
            For cc = 1 To 10   ' adjust this 10  for number of columns
                Sheets("sheet2").Cells(outrow, cc) = Sheets("sheet1").Cells(rr, cc)
            Next cc
            Sheets("sheet2").Cells(outrow, 6) = Date
        End If
    Next rr
End Sub

and change it to this:

Private Sub CommandButton1_Click()
' if sheet1 is the active sheet you con leave out Sheets("sheet1").
' sheet 2 start
    outrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    'number of rows in sheet 1
    numrow = 40
    For rr = 1 To numrow  ' you nam need to start from row x
    
        If Sheets("sheet1").Cells(rr, 3) <> "" Or Sheets("sheet1").Cells(rr, 5) <> "" Then
            outrow = outrow + 1  ' next row in sheet 2
            ' do not know how much of the row you want
            For cc = 1 To 10   ' adjust this 10  for number of columns
                Sheets("sheet2").Cells(outrow, cc) = Sheets("sheet1").Cells(rr, cc)
            Next cc
            Sheets("sheet2").Cells(outrow, 6) = Date
        End If
    Next rr
End Sub

but im not sure what to do with the green, b/c i already have a + 1 by the red??
could i just delete the whole line with the green??

the only reason why im not just trying this, is b/c i dont have my project with me

sorry
thanx alot shy

 

[Peter_SSs]

yes i did, but all these drop down menus were created it each cell, wasnt sure what to, ...

By "drop down menus" if you mean AutoFilter drop-downs, then they should only be visible if the code was interrupted by error or manual intervention. Otherwise, the code should created the AutoFilters, use them and then remove them before the code finishes running.

The code should also append each new "run" of data below the previous data.