Edit Replace (Ctr-H) With A Macro

[Oasisnutt75]

OK, this should be a simple one I hope.

I have a file that I update daily with links to another daily report (named 0109,0110,0111,etc.). I usually just highlight the cells I need, drag it down 1 row, and do an edit replace (i.e. 0109 with 0110). What I want to know is, can I use a macro to do this for me. I need it to find the last row with information, drag down 4 cells, and do an edit replace. I hope this makes sense.

Thanks,

Paul

 

[JPM]

I am not sure what you are asking for, but a good way to start out is to just record your steps in a macro and then try to adjust them so that they work each time. You can post the code if you need help.

JPM

 

[Oasisnutt75]

ok, let me try to make it more simple. bear with me as I am a VBA newbie.

I create a file every day, the name changes of the file changes in reference to the date (i.e. 0110Day for the report on Jan 10).

In another file I reference this daily file. In this file, on sheet "Daily", in column A I have every day of the year straight down. In column B I have a link to the daily report that corresponds. What I want to know is this:

Is there a way to write a macro that would go to the last cell in column B with a formula, drag it down to the next row, and then edit-replace within the link (so that c:\data\0110Day.xls becomes c:\data\0111Day.xls).

Hope that helps.

Any insights/links/help is much appreciated.

Paul

 

[JPM]

Ok,
Would this one work for you:

Sub CopyDay()

    Sheets("Daily").Select
    Range("B1").Select
    Selection.End(xlDown).Select 'This step only works if you have something in B2
    ActiveCell.Offset(1, -1).Activate
    Date1 = Month(ActiveCell.Value)
    Date2 = Day(ActiveCell.Value)
    If Len(Date1) = 1 Then
    Date1 = 0 & Date1
    End If
    If Len(Date2) = 1 Then
    Date2 = 0 & Date2
    End If
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Formula = "c:\data\" & Date1 & Date2 & "Day.xls"
    Application.CutCopyMode = False
    
End Sub

I assumed that in column A you have each day listed in a date format.
JP