1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

macro to copy YYYY and replace the YYYY in format MM/DD/YYYY

Discussion in 'Business Applications' started by tdosk, Aug 8, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. tdosk

    tdosk Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    8
    For example i have wb1 which has ws1 containing "2013" cell updated every year. Another wb2 which has ws2 containing a column like this:

    1/1/2012
    2/1/2012
    3/1/2012
    ......
    12/1/2012




    I want to have a macro that every year when i run it, the year in wb2 will be updated the same as the year in wb1.





     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Hi there!

    So, this isn't very descriptive. Are these values or formulas? You could just make them formulas that point to that cell...

    Code:
    =DATE('ws1'!A1,1,1
    Does that help?
     
  3. tdosk

    tdosk Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    8
    No it's not a formula, it's a text. wb2 is actually a csv file and wb1 is excel file.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Ok, well this should get you what you need. Please read the comments in the code...

    Code:
    Sub UpdateDates()
        
        Dim WB1 As Workbook
        Dim WB2 As Workbook
        Dim WS1 As Worksheet
        Dim WS2 As Worksheet
        Dim rYear As Range
        Dim rDates As Range
        Dim rCell As Range
        
        '/// This ASSUMES you will already have the files open.
        '/// If you will NOT have the files open, we need more code.
        Set WB1 = Workbooks("Excel File's Name Here")
        Set WB2 = Workbooks("CSV File's Name Here")
        Set WS1 = WB1.Worksheets("Worksheet Name Here")
        Set WS2 = WB1.Worksheets("Worksheet Name Here")
        
        '/// Year cell gets set here
        Set rYear = WS1.Range("A1").Value
        
        '/// Dates to get changed is set here
        Set rDates = WS2.Range("A1:A12")
        
        '/// Perform the action
        For Each rCell In rDates.Cells
            rCell.Value = DateSerial(rYear.Value, Month(rCell.Value), Day(rCell.Value))
        Next rCell
        
    End Sub
    HTH
     
  5. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    you can open csv in word and do a replace.
     
  6. tdosk

    tdosk Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    8
    to Zack: i tried your code but it gives me "Object Required" error at the line
    Set rYear = ws1.Range("B1").Value

    All the files are opened though

    *I just figured it by myself. Since you use Dim ws1 as Range. There should be no Value after. Thanks alot.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    I declared WS1 as a worksheet but you need to change "Worksheet Name Here" to whatever your worksheet name is. It is not a range [object].
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1064326