Copy row with data to second sheet

shy07014

New Member
Joined
Jun 4, 2008
Messages
16
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Something like this may help you on your way

Code:
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
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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:

Rich (BB code):
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:

Rich (BB code):
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:biggrin:

sorry
thanx alot shy
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top