Excel Macro - Copy worksheets from multiple workbooks

[alexed]

Hi, really hoping someone can assist.

I have a file that I want to use a table of variables set out in columns A:D to find specific sheets in multiple workbooks and copy them across. I've tried multiple methods but the limits of my VBA knowledge are starting to show.

In the columns I have,
A a list of the files (filepaths+filenames) I want to open
B the passwords to open those files
C the filenames (i.e. in order to allow ability to select workbooks)
D the sheet names that I want to select/copy across in the files

So, I want the macro to start at A1, open that file using the password (B1), copy a specified sheet (D1) then loop down the list of files I have.

Below is as far as I have got - it works, bit only if I include the workbook and worksheet name which will all be different when in use -


' Macro4 Macro
'

'
Dim i As Integer

i = 1

Do While Cells(i, 1).Value <> ""

Workbooks.Open Filename:=Cells(i, 2), Password:=Cells(i, 3)
Sheets("ISC201802").Copy Before:=Workbooks("Cons File.xlsm").Sheets(1)
Windows("IS Service Stability 2018 Opex IP3000 ISC201808.xlsx").Activate
ActiveWindow.Close savechanges:=False
ThisWorkbook.Activate
Sheets("Sheet3").Activate

i = i + 1

Loop

End Sub


Any assistance would be very much appreciated,

Many Thank,<

Alex

 

[footoo]

Also posted at : https://www.ozgrid.com/forum/forum/...macro-copy-worksheets-from-multiple-workbooks