Compare worksheets from different workbooks with the same sheets name

[Canadianexcel]

Good Evening,

I am hoping someone can get me started. I have done numerous google searches but didn't find exactly what I wanted to start me off. I have built out a xlsm file with multiple macros and VBA code due to security reason I had a request to build an export macro to export certain sheets to an xlsx file to prevent whoever we send the file to can't see the logic or VBA code which I completed and works well. The next step I would like to accomplish is when we receive the xlsx file I would like to compare the xlsx and xlsm file and copy columns C:E from sheet1 from whatever.xlsx file name is into myfile.xlsm sheet1. I could figure this out whoever the wall I am hitting is it maybe sheet 2 or what ever sheet may have been selected to send out to the person these could be one sheet or multiple sheets. I know about arrays and the For each code for sheets with one workbook but not quite sure how to do this with multiple files. I hope I explained this correctly for someone to give me a start.

 

[Canadianexcel]

I have been playing with my code and almost have it working however I am still hitting a wall.

Sub ImportData()

Dim FileToOpen As Variant

Dim WB As Workbook

Dim th As Workbook

Dim x As Long

Dim xa as variant

Dim ma as variant

Set th = ThisWorkbook

Apllication.Screenupdating = False

FileToOpen = Application.GetOpenFilename(Title:="Get the File to import", FileFilter:="Excel files(*.xls*),*.xls*")

If FileToOpen <> False Then

Set WB = Application.Workbooks.Open(FileToOpen)

End If

xa = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", _

"Sheet5", "Sheet6", "Sheet7")

ma = Array xa = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", _

"Sheet5", "Sheet6", "Sheet7")



For m = LBound(ma) To UBound(ma)

With th.Worksheets(ma(M))

Debug.Print .Name ‘This was testing my code

For x = LBound(xa) To UBound(xa)

With WB.Worksheets(xa(x))

Debug.Print .Name ‘ Same here

If m = x Then

‘ I know there is a different way to do this. But after banging my head for a few hours I thought I would reach out to you guys.

WB.Worksheets(xa(x)).Select

WB.Worksheets(xa(x)).Range("E5").Copy _

th.Worksheets(ma(M)).Range("E5")

End If

End With

Next x

End With

Next M

'Application.ScreenUpdating = True



End Sub