Creating an Editing Macro (Still Searching for Help)

[Ted]

I have created two workbooks that are linked.

Workbook 1. Contains multiple worksheets broken down by
month, each worksheet has multiple rows broken down by
month.

Workbook 2. Contains multiple worksheets broken down by
month, each worksheet has cells linked to specific cells
in Workbook 1. I need to create a macro that allows me to
edit the linked cells in Workbook 2 to the appropriate
cells in Workbook 1. Otherwise there will be over 1000
manual edits.

All of the worksheets in Workbook 1 currently have the
same links to the same cells in Workbook 2. I need to go
in month by month and edit the links. Here is an example
of what I need the macro to do.

Workbook 2.
Worksheet: Feb-03
Currently Linked to Workbook 1: Jan-03'!$N$973
Needs to be Linked to Workbook 1: Feb-03'!$N$974

I will be grouping the worksheets together as I edit the
current month, then ungroup the current from the remaining
so that it's always an increase by 1.

If the whole process cannot be done, fine, but if I could
at least create a macro to increase the existing number by
1, I can always find and replace the month.

Help, please!

 

[Harlan Grove]

...
...

All of the worksheets in Workbook 1 currently have the
same links to the same cells in Workbook 2. I need to go
in month by month and edit the links. Here is an example
of what I need the macro to do.

Workbook 2.
Worksheet: Feb-03
Currently Linked to Workbook 1: Jan-03'!$N$973
Needs to be Linked to Workbook 1: Feb-03'!$N$974

I will be grouping the worksheets together as I edit the
current month, then ungroup the current from the remaining
so that it's always an increase by 1.

...

You're changing Jan to Feb. While that may appear to be incrasing by 1, it's
not. It's changing text. Period.

If your link formulas look like

'[file 2.xls]Jan-03'!X99: ='[file 1.xls]Jan-03'!$N$973
'[file 2.xls]Feb-03'!X99: ='[file 1.xls]Jan-03'!$N$973
'[file 2.xls]Mar-03'!X99: ='[file 1.xls]Jan-03'!$N$973
:
'[file 2.xls]Dec-03'!X99: ='[file 1.xls]Jan-03'!$N$973

and you want them to become

'[file 2.xls]Jan-03'!X99: ='[file 1.xls]Jan-03'!$N$973
'[file 2.xls]Feb-03'!X99: ='[file 1.xls]Feb-03'!$N$973
'[file 2.xls]Mar-03'!X99: ='[file 1.xls]Mar-03'!$N$973
:
'[file 2.xls]Dec-03'!X99: ='[file 1.xls]Dec-03'!$N$973

i.e., you want the link references in each worksheet in the second workbook to
refer to the corresponding worksheet in the fist workbook, you could use a macro
like


Sub foo()
Const fw As String = "]Jan-03'"

Dim ws As Worksheet, c As Range
Dim cf As String, n As Long, p As Long, q As Long

n = Len(fw)


For Each ws In ActiveWorkbook.Worksheets

Set c = ws.Cells.Find( _
What:=fw, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchDirection:=xlNext _
)

If ws.Name = Mid(fw, 2, n - 2) Then Set c = Nothing


If Not c Is Nothing Then

Do

cf = c.Formula
q = 1
p = InStr(q, cf, fw)

Do While p > 0

q = p + n
cf = Left(cf, p) & ws.Name & Mid(cf, q - 1)
p = InStr(q, cf, fw)

Loop

c.Formula = cf

Set c = ws.Cells.FindNext(After:=c)

Loop Until c Is Nothing

End If

Next ws

End Sub