Strange. Is your macro attached to a commandbutton ? In such a case,
"activesheet" refers to the command button sheet; but I think you
should have noticed it when pasting on it. If you get no valid answer,
perhaps you could upload your file on a site such as
http://www.filedropper.com
after blurring confidential data
Hi and thank you for answering!
You're right, but that's only due to the fact that I did some changes in the
code when posting it to this group!
Anyway, I have isolated the problem to be that I can't replace when including
this in the main macro. When I copied the code and run the replace-part as a
separate macro, it worked!
Why don't the code work when included in the main macro? (see code below).
André
My whole macro is like this:
Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i angitt
katalog
' ved ĺ kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn pĺ koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)
With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for ĺ evt. begrense til spesifikke filnavn)
If .Execute 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle filer.
'Ĺpner workbook x og setter en variabel til den
Set wbResults = Workbooks.Open(filename:=.FoundFiles(lCount),
UpdateLinks:=0)
Sheets("Historikk").Visible = True
Sheets("Historikk").Select
ActiveSheet.Unprotect
Range("J1").Select
wbMal.Activate
Sheets("Historikk").Select
Columns("J:J").Select
Selection.Copy
wbResults.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J2").Select
ActiveWorkbook.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", Type:=xlExcelLinks 'NB! Endre
sti og filnavn til koblingen her
wbMal.Activate
Sheets("Sammenligning ny").Activate
Columns("B:K").Select
Selection.Copy
wbResults.Activate
Sheets("Sammenligning").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'HERE IS THE REPLACE-CODE
ActiveSheet.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
Range("B1").Select
wbResults.Close savechanges:=True
Next lCount
End If
End With
On Error GoTo 0
wbMal.Close savechanges:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"
End Sub
"Daniel.C" skrev i melding
...
Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel
Hi,
I have a macro that updates values and formulas from another workbook.
When copy a range from Book1 to Book2 I get the file-reference in Book2,
which I want to remove. The formula I copy from Book1 refers to a cell on
another sheet in the same workbook. When I copy this formula to Book2, the
formula refers to path\Book1.
Since I'm not a programmer, I tried to record a macro which is:
ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and also
tried LookIn:=xlFormulas. But no luck so far!
Do you have any suggestions? I need to either change the code above or
copy (with vba code) the formula in a way that not includes the
path+filename from Book1.
André