Macro to Copy (1st book), Clear then Paste (2nd book)

[graaaaant]

Hi there,

I have a workbook:
Sheet1: Has all the calculations and output graphs - I'm using it as a data processor really.
Sheet2: Source data for the calculations in Sheet1.

I have multiple other tests (test1.csv, test2.csv) which I'd like to copy and paste into Sheet2. The problem is, is that I need to clear the data on Sheet2 before pasting it (different tests have different numbers of rows of data). Since the filename of each test is unique, the macro cannot reference the test data file.

Any ideas on how to solve this one? (I've tried recording one but it would not work on other test data files)

Many thanks for your time.
-Grant

 

[paddydive]

Are the heading of all the tests file are same ?

 

[graaaaant]

I'm not too sure what you mean by the headings of the test files? The filenames are definately unique though (test1 - description1.csv, test2 - description2.csv ....). The sheet name within the CSV file seems to be the first 31 characters of the filename.

As far as I can tell, the operation needs to be done in one direction (copy from test CSV file, then clear Sheet2 and paste in Sheet2. The problem is, is that when you clear Sheet2, this command occurs:

Application.CutCopyMode = False (which removes the "marching ants").

Is there any way I can paste the data over the existing data on Sheet2, then clear any other rows that may still exist? (Eg. Sheet2 has 20 rows of data. I paste 10 rows of new data over the top, then I need to clear rows 11-20 from the original data). Is that codeable?

Many thanks,
-Grant D