|
Copy Past Values
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 |
Copy Past Values
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 |
Copy Past Values
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 |
Copy Past Values
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 |
Copy Past Values
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 |
Copy Past Values
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 |
Copy Past Values
Had to remove the second , after myStr.
myStr shows correct but the myC and myS show nothing in the watch, however the myS shows sheets as type and I can see it listing the sheets as worksheet/sheet1, sheet2 and so on. -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
Copy Past Values
BTW I used a book that only has about 100 links this last time.
All my workbooks has spaces in the name I wonder if that could be the problem. But it was listing sheets so maybe not. -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... Had to remove the second , after myStr. myStr shows correct but the myC and myS show nothing in the watch, however the myS shows sheets as type and I can see it listing the sheets as worksheet/sheet1, sheet2 and so on. -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
Copy Past Values
|
Copy Past Values
Ed,
Select a cell with one of the links, press F2, press Shift-Home then Ctrl-C, press Esc, and then paste the exact formula into a reply to this message. HTH, Bernie MS Excel MVP "Ed Davis" wrote in message ... BTW I used a book that only has about 100 links this last time. All my workbooks has spaces in the name I wonder if that could be the problem. But it was listing sheets so maybe not. -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... Had to remove the second , after myStr. myStr shows correct but the myC and myS show nothing in the watch, however the myS shows sheets as type and I can see it listing the sheets as worksheet/sheet1, sheet2 and so on. -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
Copy Past Values
This is one of them all links in this book are to the same book listed here.
='C:\Posto Pote\[July 2009.xls]31-07-09'!B4 -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ed, Select a cell with one of the links, press F2, press Shift-Home then Ctrl-C, press Esc, and then paste the exact formula into a reply to this message. HTH, Bernie MS Excel MVP "Ed Davis" wrote in message ... BTW I used a book that only has about 100 links this last time. All my workbooks has spaces in the name I wonder if that could be the problem. But it was listing sheets so maybe not. -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... Had to remove the second , after myStr. myStr shows correct but the myC and myS show nothing in the watch, however the myS shows sheets as type and I can see it listing the sheets as worksheet/sheet1, sheet2 and so on. -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
Copy Past Values
Ed, Maybe it is some event giving you problems.... This is my formula ='K:\Documents\Excel\Delete these\[July 2009.xls]31-07-09'!B4 and this macro converted it to a value - note that I now turn off events prior to conversion. Sub CutLinks2() Dim myStr As String Dim myS As Worksheet Dim myC As Range myStr = "July 2009" Application.EnableEvents = False For Each myS In Worksheets FindAgain: Set myC = myS.Cells.Find(What:=myStr, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not myC Is Nothing Then myC.Value = myC.Value GoTo FindAgain End If Next myS Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Ed Davis" wrote in message ... This is one of them all links in this book are to the same book listed here. ='C:\Posto Pote\[July 2009.xls]31-07-09'!B4 -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ed, Select a cell with one of the links, press F2, press Shift-Home then Ctrl-C, press Esc, and then paste the exact formula into a reply to this message. HTH, Bernie MS Excel MVP "Ed Davis" wrote in message ... BTW I used a book that only has about 100 links this last time. All my workbooks has spaces in the name I wonder if that could be the problem. But it was listing sheets so maybe not. -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... Had to remove the second , after myStr. myStr shows correct but the myC and myS show nothing in the watch, however the myS shows sheets as type and I can see it listing the sheets as worksheet/sheet1, sheet2 and so on. -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
Copy Past Values
That worked perfectly.
Thank you so much. -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ed, Maybe it is some event giving you problems.... This is my formula ='K:\Documents\Excel\Delete these\[July 2009.xls]31-07-09'!B4 and this macro converted it to a value - note that I now turn off events prior to conversion. Sub CutLinks2() Dim myStr As String Dim myS As Worksheet Dim myC As Range myStr = "July 2009" Application.EnableEvents = False For Each myS In Worksheets FindAgain: Set myC = myS.Cells.Find(What:=myStr, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not myC Is Nothing Then myC.Value = myC.Value GoTo FindAgain End If Next myS Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Ed Davis" wrote in message ... This is one of them all links in this book are to the same book listed here. ='C:\Posto Pote\[July 2009.xls]31-07-09'!B4 -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ed, Select a cell with one of the links, press F2, press Shift-Home then Ctrl-C, press Esc, and then paste the exact formula into a reply to this message. HTH, Bernie MS Excel MVP "Ed Davis" wrote in message ... BTW I used a book that only has about 100 links this last time. All my workbooks has spaces in the name I wonder if that could be the problem. But it was listing sheets so maybe not. -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... Had to remove the second , after myStr. myStr shows correct but the myC and myS show nothing in the watch, however the myS shows sheets as type and I can see it listing the sheets as worksheet/sheet1, sheet2 and so on. -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
Copy Past Values
You're welcome. Glad we were able to figure it out.
"Ed Davis" wrote in message ... That worked perfectly. Thank you so much. -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ed, Maybe it is some event giving you problems.... This is my formula ='K:\Documents\Excel\Delete these\[July 2009.xls]31-07-09'!B4 and this macro converted it to a value - note that I now turn off events prior to conversion. Sub CutLinks2() Dim myStr As String Dim myS As Worksheet Dim myC As Range myStr = "July 2009" Application.EnableEvents = False For Each myS In Worksheets FindAgain: Set myC = myS.Cells.Find(What:=myStr, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not myC Is Nothing Then myC.Value = myC.Value GoTo FindAgain End If Next myS Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Ed Davis" wrote in message ... This is one of them all links in this book are to the same book listed here. ='C:\Posto Pote\[July 2009.xls]31-07-09'!B4 -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ed, Select a cell with one of the links, press F2, press Shift-Home then Ctrl-C, press Esc, and then paste the exact formula into a reply to this message. HTH, Bernie MS Excel MVP "Ed Davis" wrote in message ... BTW I used a book that only has about 100 links this last time. All my workbooks has spaces in the name I wonder if that could be the problem. But it was listing sheets so maybe not. -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... Had to remove the second , after myStr. myStr shows correct but the myC and myS show nothing in the watch, however the myS shows sheets as type and I can see it listing the sheets as worksheet/sheet1, sheet2 and so on. -- Thank You in Advance Ed Davis "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
| All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com