Problem with switching between workbooks with a macro

[Dinictus]

Hi,

I have a curious problem. I'm using a specific excel file to copy a format from. I want to use this format to format another workbook with a macro.

However, when I record the marco it records the specific names of the file you are switching between. (ofcourse)

1. The file from which I take the format is always in the same place and hase the same name.
2. the file which I want to format can be named differently. (i'm running the macro from personal)

Somehow I need to make a reference to: "the workbook from which the marcro is ran"

So:

Windows("filename1.xlsx").Activate

Needs to become:

Windows(working reference to the workbook in which the format macro is activated)Activate

I hope explained this in a clear manner. Is there a solution for this or should I take another route?

 

[VoG]

Try

ThisWorkbook.Activate

 

[Dinictus]

Unfortunatly it does not seem to work,

I can't get it.

this is the code:

Sub getextended()
'
' getextended Macro
'

'
Workbooks.Open Filename:= _
"O:\bla\file.xlsx"
ActiveWindow.SmallScroll Down:=84
Range("A94:S128").Select
Selection.Copy
Windows("otherfile.xlsx").Activate
ActiveWindow.SmallScroll Down:=66
Range("A94").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-99
Range("L3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Extended data added"
Range("L3:P3").Select
With Selection.Font
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.599993896298105
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("A1:AA1").Select
End Sub

So basically the name of "file.xlsx changes all the time and the reference here need to be: the file that is open when you run the macro (macro is ran from personal.xls). Anyone to help me out? it is appreciated.

 

[VoG]

You shouldn't need to activate anything

Workbooks.Open Filename:="O:\bla\file.xlsx"
Range("A94:S128").Copy Destination:=Workbooks("otherfile.xlsx").Sheets("Sheet1").Range("A94")

 

[Dinictus]

Thanks for your reply.
The problem is the basis file from which i'm working. It always looks the same but sometimes when you have opened it more that once is get an a number at the end of the file (1) (2) making it impossible to directly reference it.

I have created a workaround and that is to save the basefile under a specific name. Making it possible for me to swith back to it a again using a fixed name.