[tdosk]

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.

[Zack Barresse]

Hi there!

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

=DATE('ws1'!A1,1,1

Does that help?

[tdosk]

No it's not a formula, it's a text. wb2 is actually a csv file and wb1 is excel file.

[Zack Barresse]

Ok, well this should get you what you need. Please read the comments in the 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

[Garf13LD]

you can open csv in word and do a replace.

[tdosk]

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.

[Zack Barresse]

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].