Excel Macro not working after change in file name!!!

As far as I know, that would be difficult to do, short of creating a macro that would open that master workbook, run your specific macro, then close the master workbook. That almost seems not worth the effort for the additional problems it would cause with loading, etc. The best method would just to copy the vba code for the macro you want into the new worksheet. In the code, you'd then want to remove the portion of the calling the macro that refers specifically to the previous workbook.

I'm not an expert in VBA though, so maybe its possible. But just copying the code, or keeping the other workbook open would be the only way I know of.

Additionally, you should put a line of code in the beginning of the macro that says something like

Dim ThisWorkbook as String Dim path as String ThisWorkbook = ActiveWorkbook.Name path = ActiveWorkbook.Path

and then later in the code:

Application.Run path & "\'" & ThisWorkbook & ".xls'!Macro24" ActiveCell.Offset(0, -6).Range("A1").Select

Does that make sense? this would work even if the two workbooks are in different directories, but if they are in the same directory then you can just take out the "path" variable all together. I think that solves what youre asking...

I just don't follow why if you're going to point it out to this worksheet anyway you don't just simply use:

Application.Run "Macro24", so it just runs the macro in the current book. Am I missing something?

he is working in other books, doing "Run Macro24" will only run the macro in the activeworkbook... i believe his macro jumps around workbooks, so the active one may not have the desired macro.

Alright I see what you're saying, I was just a little unclear about what you meant. I still think its probably simplest to just copy the code haha.

Here's one I have that does just that without visibly opening the Excel file:

Note: in order for this to work, in the VBA window goto Tools-->References. Then find one that says something to the effect of "Microsoft Excel 10.0 Object Library" and select it, then click OK.

Sub FormatExcelFile(filename As String, NumberRecords As Integer) Dim xl As Excel.Application Dim wbk As Excel.Workbook Dim wks As Excel.Worksheet Set xl = New Excel.Application Set wbk = xl.Workbooks.Open(filename) Set wks = wbk.Sheets(1) wks.Activate With wks .Range("H2:H" & NumberRecords + 1).NumberFormat = "#,##0" For i = 3 To (NumberRecords + 1) Step 2 If i / 2 = Int(i / 2) Then .Range("A" & i & ":M" & i).Interior.ColorIndex = 2 Else .Range("A" & i & ":M" & i).Interior.ColorIndex = 36 End If Next i With .Range("A2:M" & NumberRecords + 1, "A1:J1").Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With .Range("A2:M" & NumberRecords + 1, "A1:J1").Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With .Range("A2:M" & NumberRecords + 1, "A1:J1").Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With .Range("A2:M" & NumberRecords + 1).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With .Range("A2:M" & NumberRecords + 1, "A1:J1").Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Range("A2:M" & NumberRecords + 1).Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlHairline .ColorIndex = xlAutomatic End With With .Range("A1:M1").Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With .Range("A1:M1").Font.Bold = True .Range("A1:M1").Interior.ColorIndex = 15 .Range("A1:M1").HorizontalAlignment = xlCenter .Columns("A:M").EntireColumn.AutoFit .Range("A2").Select xl.ActiveWindow.FreezePanes = True .Range("A1").AutoFilter End With wbk.Save wbk.Close xl.Quit Set wks = Nothing Set wbk = Nothing Set xl = Nothing End Sub