Problem in Excel Macro Creation

[Florad]

Hello. This is my first time posting, and I am not very good with
macros, so please bear with me and my explanations. I have a macro I'm
trying to run with little success. I'm trying to change information in a
link to another file. I have an opening sheet in my workbook where you
manually enter the prior filename and the current filename (which are
always dates). I've set up my macro so that the macro will automatically
find and replace the old file name with the new file name in my
datasheets, thus automatically updating new data. The problem is that
during my find and replace on the initial macro setup, the macro is
always using the actual names of the files, and refuses to copy any new
names in the macro, so the original names I have set up never change.
Below is what I have done. If you notice the "101203" and "100503" do
not change, and that's what I'm trying to copy and change. I want the
data that are in those cells to change, not use the specific file names
inside the cells. Hopefully I haven't rambled and confused anyone out
there. Any help would be appreciated! Thanks!



Range("C5").Select

Selection.Copy

Sheets("Hi-Grade").Select

Columns("D:J").Select

Sheets("Consolidated").Select

Range("C10").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Hi-Grade").Select

Selection.Replace What:="101203", Replacement:="100503",
LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

 

[Dick Kusleika]

Florad

I can't tell which cells contain the information or in what format the
filename (dates) are. Let's assume that the old filename is on
Consolidated!C5 and the new filename is on Consolidated!C10. Let's further
assume that you want to Find/Replace in columns D:J on sheet Hi-Grade.
Here's what your macro would look like

Sub ChangeNames()

Sheets("Hi-Grade").Columns("D:J").Replace _
What:=Sheets("Consolidated").Range("C5").Value, _
Replacement:= Sheets("Consolidated").Range("C10").Value, _
LookAt:=xlPart

End Sub

Be sure to make a backup copy of your workbook before you try this (or
anything else you don't fully understand, for that matter.)

Let me know if it needs some modification.