I have several workbooks with as many as 35 - 40 worksheets.
Each workbook (And as many as 20 sheets from each workbook) Calls cells from
the previous months workbooks.
I would like to Copy Paste Values using as an example any cell in the
workbook that has 09-2009 in the Formula. The file name may be Sales -9-2009
or Loja 09-2009 and there are several others, However the 09-2009 is in the
filename for each workbook.
If I can do this I would be able to move or remove the older files from
their default directory.



--
Thank You in Advance
Ed Davis

Ed,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub CutLinks()
Dim myStr As String
Dim myS As Worksheet
Dim myC As Range

myStr = "09-2009"

For Each myS In Worksheets
FindAgain:
Set myC = myS.Cells.Find(myStr, , xlFormulas, xlPart)
If Not myC Is Nothing Then
myC.Value = myC.Value
GoTo FindAgain
End If
Next myS

End Sub


"Ed Davis" wrote in message ...
I have several workbooks with as many as 35 - 40 worksheets.
Each workbook (And as many as 20 sheets from each workbook) Calls cells from the previous months
workbooks.
I would like to Copy Paste Values using as an example any cell in the workbook that has 09-2009 in
the Formula. The file name may be Sales -9-2009 or Loja 09-2009 and there are several others,
However the 09-2009 is in the filename for each workbook.
If I can do this I would be able to move or remove the older files from their default directory.



--
Thank You in Advance
Ed Davis

Nothing at all happens.
I added a watch for myStr and no values shows.
Ran it in a workbook that has about 500 links.



--
Thank You in Advance
Ed Davis
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ed,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub CutLinks()
Dim myStr As String
Dim myS As Worksheet
Dim myC As Range

myStr = "09-2009"

For Each myS In Worksheets
FindAgain:
Set myC = myS.Cells.Find(myStr, , xlFormulas, xlPart)
If Not myC Is Nothing Then
myC.Value = myC.Value
GoTo FindAgain
End If
Next myS

End Sub


"Ed Davis" wrote in message
...
I have several workbooks with as many as 35 - 40 worksheets.
Each workbook (And as many as 20 sheets from each workbook) Calls cells
from the previous months workbooks.
I would like to Copy Paste Values using as an example any cell in the
workbook that has 09-2009 in the Formula. The file name may be
Sales -9-2009 or Loja 09-2009 and there are several others, However the
09-2009 is in the filename for each workbook.
If I can do this I would be able to move or remove the older files from
their default directory.



--
Thank You in Advance
Ed Davis

Ed,

It worked perfectly for me.
Is the workbook with the links the active workbook at the time that you are running the macro?
Are you sure that the filename includes the exact string "09-2009" and not some variant on that,
like "09- 2009"?

HTH,
Bernie
MS Excel MVP


"Ed Davis" wrote in message ...
Nothing at all happens.
I added a watch for myStr and no values shows.
Ran it in a workbook that has about 500 links.



--
Thank You in Advance
Ed Davis
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ed,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub CutLinks()
Dim myStr As String
Dim myS As Worksheet
Dim myC As Range

myStr = "09-2009"

For Each myS In Worksheets
FindAgain:
Set myC = myS.Cells.Find(myStr, , xlFormulas, xlPart)
If Not myC Is Nothing Then
myC.Value = myC.Value
GoTo FindAgain
End If
Next myS

End Sub


"Ed Davis" wrote in message
...
I have several workbooks with as many as 35 - 40 worksheets.
Each workbook (And as many as 20 sheets from each workbook) Calls cells from the previous months
workbooks.
I would like to Copy Paste Values using as an example any cell in the workbook that has 09-2009
in the Formula. The file name may be Sales -9-2009 or Loja 09-2009 and there are several others,
However the 09-2009 is in the filename for each workbook.
If I can do this I would be able to move or remove the older files from their default directory.



--
Thank You in Advance
Ed Davis

I changed the strung to "July 2009"
It was the only workbook open.


--
Thank You in Advance
Ed Davis
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ed,

It worked perfectly for me.
Is the workbook with the links the active workbook at the time that you
are running the macro?
Are you sure that the filename includes the exact string "09-2009" and not
some variant on that, like "09- 2009"?

HTH,
Bernie
MS Excel MVP


"Ed Davis" wrote in message
...
Nothing at all happens.
I added a watch for myStr and no values shows.
Ran it in a workbook that has about 500 links.



--
Thank You in Advance
Ed Davis
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ed,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub CutLinks()
Dim myStr As String
Dim myS As Worksheet
Dim myC As Range

myStr = "09-2009"

For Each myS In Worksheets
FindAgain:
Set myC = myS.Cells.Find(myStr, , xlFormulas, xlPart)
If Not myC Is Nothing Then
myC.Value = myC.Value
GoTo FindAgain
End If
Next myS

End Sub


"Ed Davis" wrote in message
...
I have several workbooks with as many as 35 - 40 worksheets.
Each workbook (And as many as 20 sheets from each workbook) Calls cells
from the previous months workbooks.
I would like to Copy Paste Values using as an example any cell in the
workbook that has 09-2009 in the Formula. The file name may be
Sales -9-2009 or Loja 09-2009 and there are several others, However the
09-2009 is in the filename for each workbook.
If I can do this I would be able to move or remove the older files from
their default directory.



--
Thank You in Advance
Ed Davis

Ed,

It may be one of your search settings is interfering. Try changing to

Set myC = myS.Cells.Find(myStr, , xlFormulas, xlPart)

Set myC = myS.Cells.Find(What:=myStr, , LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)


HTH,
Bernie
MS Excel MVP


"Ed Davis" wrote in message ...
I changed the strung to "July 2009"
It was the only workbook open.


--
Thank You in Advance
Ed Davis
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ed,

It worked perfectly for me.
Is the workbook with the links the active workbook at the time that you are running the macro?
Are you sure that the filename includes the exact string "09-2009" and not some variant on that,
like "09- 2009"?

HTH,
Bernie
MS Excel MVP


"Ed Davis" wrote in message
...
Nothing at all happens.
I added a watch for myStr and no values shows.
Ran it in a workbook that has about 500 links.



--
Thank You in Advance
Ed Davis
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ed,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub CutLinks()
Dim myStr As String
Dim myS As Worksheet
Dim myC As Range

myStr = "09-2009"

For Each myS In Worksheets
FindAgain:
Set myC = myS.Cells.Find(myStr, , xlFormulas, xlPart)
If Not myC Is Nothing Then
myC.Value = myC.Value
GoTo FindAgain
End If
Next myS

End Sub


"Ed Davis" wrote in message
...
I have several workbooks with as many as 35 - 40 worksheets.
Each workbook (And as many as 20 sheets from each workbook) Calls cells from the previous
months workbooks.
I would like to Copy Paste Values using as an example any cell in the workbook that has
09-2009 in the Formula. The file name may be Sales -9-2009 or Loja 09-2009 and there are
several others, However the 09-2009 is in the filename for each workbook.
If I can do this I would be able to move or remove the older files from their default
directory.



--
Thank You in Advance
Ed Davis