copy row to new sheet

[fritterpop]

I know nothing about programming or how to make a program work in Excel so please be gentle.

I have two sheets in the same workbook.

Sheet 1 contains data that is pasted in daily from another application. The data is hourly data so each day 24 rows are pasted. Each row ranges from column A through Column P. After the data is pasted I copy a row below the 24 new rows to total up the data in each column.

I want to copy the values (not formula's) from the "total" row in sheet 1 and paste the entire row into sheet 2 in the next unused row if the value in column M is greater than 1079.

Basically sheet 1 will contain all of the raw data including a "total" row for each day and sheet 2 will contain only "total" row data that meets a certain criteria.

I would like the code to trigger this copy and paste function automatically if possible such as right after the "total" row is pasted into sheet 1.

Beggars can't be choosers but if it is possible please include comments so I can try to figure out what your code is doing. I have been looking for the answer for days and have become intrigued with what can be done so I want to learn more.

Thanks in advance.

[mdbct]

Right click on Sheet1 and choose View code. Paste the following in the code window.

Please Login or Register  to view this content.

Macro assumes there is data in row 1 on sheet2. Change the sheet names in this part of the macro Worksheets("sheet1") if you have different names for your tabs.

[fritterpop]

I pasted the code in the source sheet, edited the sheet names in the code to match mine and checked the argument to ensure it was true but nothing was pasted to the destination page.

Do I have to run this code manually some how or will it trigger when a new "total" row is pasted?

If I am interpreting your code right, it looks like it is grabbing what is in row 25 of the source sheet only. Tomorrow when I paste in my new data, I want the code to copy and paste it too (I do this every day). When I paste in this new data I am appending to the old so the source row # will no longer be 25.

I don't think I want to loop through the whole spreadsheet everyday and copy/paste old data again because the source spreadsheet contains a huge # of rows. Can you make it look for and copy only new data?

Thanks for adding the comments, they were very helpful.

[davesexcel]

Hi,

The code provided to you belongs in the worksheet module.

It would be best to attach a sample workbook instead of having somebody write a blind code for you.

[mdbct]

I was under the assumption that the data was cleared every day on sheet one. The following will fire every time something is changed in every 25th row. No intervention is needed on your part.

Please take notice there are two "If Target.Row" statements at the beginning. Comment out (or remove) the one that does not apply to your set up. The code is currently set up for a sheet with a header row and will fire on changes rows 26, 51, 76, etc.

Please Login or Register  to view this content.

[fritterpop]

You have made my life so much easier! Thank you very much. Can you help me understand why this works by explaining what is going on in the two lines of code below? I think I understand the others. Thanks again and I hope you will be around again when I get some data in sheet two and need help with a calculation script.

Please Login or Register  to view this content.

'for sheet with header row
'checks to see if something new in every 25th row changes

The "and" is throwing me off.

Please Login or Register  to view this content.

means if the 25th row is blank I think.

and

Please Login or Register  to view this content.

means what???

And the 2nd line of code that I am confused about:

Please Login or Register  to view this content.

'copies the row

what is the 16 at the end of this line of code?

Thanks again!

[mdbct]

The first two lines (without the comments)

Please Login or Register  to view this content.

are telling Excel that if the remainder of row number being altered is divided by 25 is not equal to 1 and the row number is greater than 1 (since 1 divided by 25 gives a remainder of 1, you have to exclude row 1) then stop the macro.

In this part,

Please Login or Register  to view this content.

the 16 is the column number for column P because in your original post you stated:

Each row ranges from column A through Column P

[fritterpop]

My thinking is that "target" is any row that I change in the sheet that contains the subroutine.
Mod is the remainder of any row that changes divided by 25. Since you used if the remainder <> 1 the routine does not abort meaning:

1st row is header
rows 2 through 25 are raw data
row 26 is the total row I want to copy then paste in sheet 2.

so 26/25 = 1 with a remainder of 1 so it would copy and paste.

row 27 through 50 are the next row #'s with raw data
row 51 is the next total row that will be copied and pasted into sheet 2.

so 51/25 = 1 with a remainder of 1 so it would copy and paste.

when all other rows are changed since the remainder is either 0 or > 1 they abort?

It took me a little while but I was finally able to figure out what the 16 meant.

Thanks again for the help.