How do I make the copy/paste of one column contingent on the copy/paste of another?

Hello everyone,

Ive been working on a macro that pulls data from a given spreadsheet pastes that data into another sheet("sheet2"), then plots the pulled data from sheet2 using the average and standard deviation. I have a sheet called 'Mxxxx' in a series of sheets. From this sheet(and the others) I need to pull the relevent LotID's(Column A) and plot that against relevent parameters(in this case the Mixing Speed and pH parameters) showing the average and standard deviation on one graph. I have all of the avg and stdv stuff worked out, but the problem comes from the pulling of the graphing data. Unfortunately the relevent info on each sheet is not in a standardized format, so in some sheets data starts in column "A1" where in other sheets it starts in column "B2". I need to write the code in such a way that it knows to only copy the relevent info from a given column and nothing else. Here is the code I used to select the relevent data from my sheet to be pasted into another sheet that I graph from:

Please Login or Register  to view this content.

Now the piece of code where I use the left function works fine. As that column is always labeled "Lot Number", in ever case on every subsequent sheet the lots always start with an 'M' which allows me to isolate the LotID's. The problem is with my other piece of code that isolates the numeric values of a given parameter. It almost works! So close lol. The problem is that the method I used looks for the beginning of numbers and uses that to tell vba that those are the values to be copied onto the other sheet for graphing. This leads to the reference value(7, see attached) being included in the pH column, which throws the avg and stdv off. How can I tell VBA skip that reference line number and only grab the values?
I feel like a good way to do that would be to make it such that the parameter values are only pulled that are in line with the LotID's listed on the same sheet, as there is never a case where there is a difference in the number of LotID's and parameter values. How do I make my If/Then function for the parameter values contingent upon the start and end of the LotID values? Please let me know if additional info is needed. Thanks so much!