Copy to last line of data and paste

[AggyRJ]

I have a workbook with two spreadsheets. Sheet 1 is "working data" and sheet 2 is "Solution" As the title suggests, in sheet 1 is where all my calculations are performed, then it is pasted into sheet 2 and formatted. My issue is I need the information from "working data" to be copied and pasted into "Solution" but the information from "working data" will have a variable amount of rows (currently there are over 10,000, but their could be a few thousand more in any given month. The copied range from "working data" will always start in cell A1 and then should select all rows down until it finds a cell in column A empty, then the data should be pasted into "Solution" starting with cell A40. Any help will be greatly appreciated.

 

[Peter_SSs]

1. Should any existing data in "Solution", row 40 and down, be deleted first?

 

[Peter_SSs]

1. Should any existing data in "Solution", row 40 and down, be deleted first?

Assuming the answer is "Yes", try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> MoveWorkingData()<br>  <SPAN style="color:#00007F">With</SPAN> Sheets("Solution")<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    Intersect(.UsedRange, .Rows("40:" & .Rows.Count)).ClearContents<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    Sheets("Working Data").UsedRange.Copy<br>    .Range("A40").PasteSpecial Paste:=xlPasteValues<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[AggyRJ]

Exactly what I needed, thanks!