Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Match dates then copy data to templates

    Hi All,

    I have a workbook which contains templates ( jan, feb, mar,apr,may,jun,july,aug,sep,oct,nov,dec) and data files names (test1, test2,test3) . I need that code match the dates in column A of test1 (sheet) with dates of all the templates on row 8 and paste the data transpose to the templates. if the dates in the data file (test1) is missing then do not paste the data in those cells.


    Regards,
    Farrukh
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    See attached file.

    Notes:
    On sheet [Jan], I have placed some formulas in range [AL9:AL37]
    This block of formulas has been named as [formulaBlock], and is used in the VBA code to be copied on each month sheet, and then converted from formulas to values.

    The copying works for all months.

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2012-01-26)

  4. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Hi Zeddey ,

    I did not find any attached file .

    Cheers

    Farrukh

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Farrukh

    I originally saved the file as a .xlsb binary file but it seems not to have uploaded.

    So here it is again as a .zip file.

    zeddy
    Attached Files Attached Files

  6. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2012-01-26)

  7. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Hi zeddy,

    WOW ! THATS GREAT TO SEE ITS WORKS FOR ME . A LITTLE ASK THE FORMULA BLOCK IS IN AL9:AL37. I WANT TO CHANGE THIS AL9:AL37 TO AZ9:AZ37 WILL FORMULA BLOCK WILL PICK BY VBA AUTOMATICALLY ? AND I HAVE MANY SHEETS IN THE ORIGINALLY TEMPLATE WILL THIS CODE WILL WORK ON THE SAME TEMPLATES AND DATAFILES?

    THANKS ALOT

    FARRUKH

    NOTE: Please refrain from posting in caps as it is considered the same as shouting in speech.
    Last edited by Deadeye81; 2012-01-27 at 06:13. Reason: Using all caps in post

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Farrukh

    You can 'grab' and move the formula block to [AZ9:AZ37], since this will simply move the named range.
    The code will be OK since it refers to this named range.
    If you have many other sheets in the file, the code will still work provided these other sheets have names which are longer or shorter than 3 characters. If you have other sheets which have 3-letter names, e.g. VAT, TAX etc, then you would have to either change these sheetnames OR change the code to specifically select each sheet, JAN, FEB,.., DEC instead of looping through all sheets.

    By the way, I always save my Excel10 files as binary files (.xlsb) as these are the smallest, which means they load faster. You can save an Excel10 file as .xlsb even if it has VBA code in it.

    zeddy

  9. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2012-01-26)

  10. #7
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Cool

    Hi Zeddy,

    Wonderful thank you so much for your time and help


    Thank You


    Farrukh

Posting Permissions

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