Paste to specific column (dates)

[rocknrisk]

I have a file open dated 14.04.02.xls, and I need to copy the data from the open file to the correct day (14.04.02) in a weekly file, all the days are all listed on one sheet in the weekly file in columns ( so i can see a running total of the week to date
I need this to be in VB, so I can automate it to work for every previous day for the week.

I need the data for 14.04.02 to be pasted into the 14.04.02 column

thanks in advance for any help on this.

 

[Jay Petrulis]

On 2002-04-18 03:11, rocknrisk wrote:
I have a file open dated 14.04.02.xls, and I need to copy the data from the open file to the correct day (14.04.02) in a weekly file, all the days are all listed on one sheet in the weekly file in columns ( so i can see a running total of the week to date
I need this to be in VB, so I can automate it to work for every previous day for the week.

I need the data for 14.04.02 to be pasted into the 14.04.02 column

thanks in advance for any help on this.

Hi,

I don't have the specifics for you, but here is the way I usually do this...

1. Open the data file (with the destination file) by using the Workbooks.Open (or .OpenText) method.

2. Identify the date/name of the file and assign it to a variable. Coerce the variable to a string, if needed, by using CStr(variable).

3. Search the destination sheet for the column that matches your string variable (I assume that the headers are also strings. Just match the data types and you'll be fine.

4. Copy the data file to the destination worksheet column (or append to the end, etc).

5. Close the source workbook.

This should be pretty quick. The original data is not changed, so if you need to do any data manipulation, you can do so without altering the source file.

HTH,
Jay

 

[rocknrisk]

Thanks for that suggestion, it has helped a lot but I am still stuck with the following.
I am trying to get the find statement to locate the variable, if i set the variable to a range b3, and b3 is entered as a date - 17/04/02, and the place to find the data is set as the same it finds the data on the othersheet. if the variable in b3 is set as - =text(now()-1,"dd/mm/yy"), and the place to find the data is set as date format it doesn't find the data.
Any idea's for this dilemma, VB coding would be excellent.

thanks in advance.