Copy HTM Files from Closed Workbook and Copy to Active Workbook if Sheet Name=File Name

I need the macro to copy 7 htm files named, -ACH, -AHH, -FSS, -GRR..ect files located in a ACS/Test to my workbook Users\Test if sheet name is equal to -ACH, -AHH, -FSS, -GRR. Trying to do this as its a daily copy and paste for the data. I think the macro below is grabbing the files that match I just need a way to not list each directory where I am copying from as well as not list every page it is pasting to. Is this possible? There is also a pivot I run which has formulas to these documents so trying to make it a fast process. I had a macro to copy in there, but it was copying ACH to a new sheet not the existing one. I closed out and lost the macro. I am copying the whole sheet which due to htm only appears in column A.

Copy From Here Q:\ACS\Test\11-13-2017-ACH.htm
Copy From Here Q:\ACS\Test\11-13-2017-AHH.htm
Paste Here C:\Users\Test\Desktop\Book2.xlsm

These fixed widths also need applied to htm file.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Sub MyMacro(strDesiredWkb As String)

ActiveSheet.Copy ThisWorkbook.Sheets(Sheets.Count)

MsgBox strDesiredWkb

End Sub]


The macro works only problem I am having is its copying onto a new tab within the workbook instead of pasting into same sheet. So instead of having one tab labeled ACH it has one that says ACH and another that says ACH(2). How do I get the data to paste into the same tab?