0

If I create a new workbook, example "combined.xlsx" and attempt to run a macro named "xxx", the macro isn't available until I unhide the Personal.xlsb file, however, when I run the macro it runs(completes the steps of the macro correctly) in the Personal.xlsb file when I intended it to complete the steps of the macro in the "combined.xlsx file.

I would like advice as to how to run it in the "combined.xlsx" file.

Sub azazaz()
Dim SrcPath As String
SrcPath = "C:\test\testing\"
Dim SrcFileName As String
'SrcFileName = Dir(SrcPath & "x*.xlsx")
SrcFileName = Dir("C:\test\testing\" & "x*.xlsx")
Dim Targetws As Worksheet
Dim top As Range
Dim wrkBk As Workbook
Set top = Range("A1")


Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While SrcFileName <> ""
Set wrkBk = Workbooks.Open(SrcPath & SrcFileName)
    For Each Targetws In wrkBk.Sheets
        Targetws.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        Cells.ClearFormats
        Cells.Select
        Cells.EntireColumn.AutoFit
        Selection.ClearFormats
        ActiveWindow.FreezePanes = False
        Range("A1").Select
        



    Next Targetws
    Workbooks(SrcFileName).Close
    SrcFileName = Dir()
Loop

    For Each Targetws In ActiveWorkbook.Sheets
        Set tbl = Targetws.ListObjects.Add(xlSrcRange, Targetws.Range("A1").CurrentRegion, , xlYes)
        tbl.Name = "tbl" & Targetws.Name
    Next Targetws


Application.ScreenUpdating = False
Application.DisplayAlerts = False

End Sub


Sub xxx()

Dim SrcPath As String
SrcPath = "C:\test\"

Dim SrcFileName As String
SrcFileName = Dir(SrcPath & "*.xlsx")

Dim Targetws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While SrcFileName <> ""
    Workbooks.Open SrcPath & SrcFileName
    For Each Targetws In ActiveWorkbook.Sheets
        Targetws.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Next Targetws
    Workbooks(SrcFileName).Close
    SrcFileName = Dir()
Loop

'Worksheets(1).Delete

Application.ScreenUpdating = False
Application.DisplayAlerts = False

End Sub
JL Thalacker
  • 45
  • 7
  • 1
    https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – braX Sep 20 '21 at 23:58
  • 1
    When your macro does `Range("A1").Select` which workbook will it use? Maybe you need to fully qualify the location so it doesn't get confused. Does using `ActiveSheet` help? – Jerry Jeremiah Sep 21 '21 at 01:47
  • 1
    `ThisWorkbook.ActiveSheet`. A couple of lines earlier you open a workbook which will become the active workbook. `ThisWorkbook` is the file with the code in, the `ActiveSheet` is in another workbook. There's no need to reference the workbook when you have the sheet reference - the code knows which book is the parent for the sheet. – Darren Bartrup-Cook Sep 21 '21 at 07:40
  • Hello and thank you for your comments, I have simplified the macro and made variables more explicit. However, @Darren I don't fully understand where to explicitly select the target workbook. – JL Thalacker Sep 21 '21 at 16:26
  • 1
    Change this line `Workbooks.Open SrcPath & SrcFileName` to `Set wrkBk = Workbooks.Open(SrcPath & SrcFileName)` (declare wrkBk variable as a workbook - `Dim wrkBk as Workbook`) and move it to before `Do While` line. You can then reference that workbook with the variable rather than using `ActiveWorkbook` – Darren Bartrup-Cook Sep 22 '21 at 07:49
  • @darren, thanks for your patience and help on this, I've added a code section above the first to incorporate the changes you recommended, the"collected" excel files are still being aggregated in the PERSONAL.XLSB file and not the combined.xlxm file. I've also changed a few items to expressly note the source and target location. I've also left the ref to 'For Each Targetws In ActiveWorkbook.Sheets' as it was, is this correct? – JL Thalacker Sep 28 '21 at 18:28
  • I'd be happy to NOT have this run in the personal.xlsb file and not be "always" available, @Darren, do you have any other ways to do this? – JL Thalacker Sep 28 '21 at 18:41

0 Answers0