Copy data from an unknown range, create new worksheet, and paste data there

[ksterling]

I am trying to write a VBA code to copy data from sheet "Data1". I need to copy data from C3 and down to the end (the end differs every time), then create a new sheet titled "Graph" and paste the data into cell A2 and down. The same needs to be done from B3 and down to the same end as column C and then pasted into the "Graph" worksheet into cell E2.



The data in the image titled "Data1 Worksheet" is how the data is obtained. The data in the image titled "Graph Worksheet" is how I need it to end up after copying the data and inserting it into cells A2 and E2. Also, how could I make it automatically populate that top row when it creates the "Graph" Worksheet?

I uploaded an excel sheet titled "Example" that shows the end result. The data is not accurate as I deleted a few thousand rows to make it a smaller file, but that has the idea of what I am looking for.

Thank you anyone in advance for the help!!!

[WideBoyDixon]

Here you go:

Please Login or Register  to view this content.

Note that since you said you wanted to create a new sheet, if there's an existing sheet called "Graph", it will prompt you to delete it.

WBD

[ksterling]

Thank you so much!!

My follow up question would now be, how could I write a code that will automatically graph the data that appears in column D (x-axis) and column I (y-axis). I would also need to put in the equations into columns B,C,D and F,G,H,I and make sure the equations fill all the way down to match the data range of the column before it.

The equations are as follows:
Column B: =AVERAGE(OFFSET(A$3,(ROW(A3)-ROW(A$3))*10,,10,))
Column C: =AVERAGE(OFFSET(B$3,(ROW(B3)-ROW(B$3))*5,,5,))
Column D: =AVERAGE(OFFSET(C$3,(ROW(C3)-ROW(C$3))*2,,2,))
Column F: =E2*0.25
Column G: =AVERAGE(OFFSET(F$3,(ROW(F3)-ROW(F$3))*10,,10,))
Column H: =AVERAGE(OFFSET(G$3,(ROW(G3)-ROW(G$3))*5,,5,))
Column I: =AVERAGE(OFFSET(H$3,(ROW(H3)-ROW(H$3))*2,,2,))


Is it possible to format the cells to be in number format - columns A-D would be 2 decimals and columns E-I as a whole number?

[WideBoyDixon]

Well, I changed the "3" to "2" in the formulas but you can change them back if you like:

Please Login or Register  to view this content.

WBD

[ksterling]

You are awesome, thank you so much!!

[ksterling]

Are you able to turn this into a macro that can batch process from a folder? I have about 160 files I need to do this for, not sure if it is possible to batch process it all with just one click.

[WideBoyDixon]

I haven't tested this but it looks like it should work:

Please Login or Register  to view this content.

WBD

[ksterling]

I ran it and checked a few, seemed to work good. Thank you for all of this, you are a genius!!!