Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Paste to specific column (dates)

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Working in Devon, England
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Working in Devon, England
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •