There is something that I’m doing wrong because even with the revised version the second approach returns an error , I’ll use the first approach because it’s working really good but here is my full code in case you want to take a look at it:
[VBA]
Sub Import_Data()
Dim fnametwo As String
Dim CIGNrange As String, CIGWks As String
Dim sName As Name
Dim dName As Name
Dim i As Long
Dim B1 As Workbook
Dim B2 As Workbook
Dim rCopy As String
Dim rPaste As String
', I As Integer
Dim fpath As String, fname As String, copyName As String, pass As String
Dim j As Integer
Dim wbMstr As Workbook, wbCopy As Workbook
' On Error GoTo Import_Data_Errorhandler
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableAnimations = False
.EnableEvents = False
.Calculation = xlCalculationManual
.StatusBar = Empty
.Cursor = xlDefault
End With
If Not IsFolderExistsID(cTEMP) Then
CreateObject("Scripting.FileSystemObject").CreateFolder cTEMP
End If
KillItID (sDebugLogFileNm)
WriteDebugLogID "----------------------------------------------------------------"
WriteDebugLogID (Now & " : user: " & Environ("username"))
WriteDebugLogID (Now & " : " & cAddInFileNm & " : Version: " & cAddInRev)
WriteDebugLogID "----------------------------------------------------------------"
WriteDebugLogID (Now & " : ImportData_Initialize started")
Set wbMstr = ThisWorkbook
With wbMstr
fpath = .Path & "\Output\"
End With
wbMstr.Worksheets("Country Input Generation").Select
For j = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
fname = fpath & Cells(1, j) & ".xlsm"
fnametwo = Cells(1, j) & ".xlsm"
Set wbCopy = Workbooks.Open(fname)
WriteDebugLogID (Now & " : Opening " & fnametwo & " from " & Path)
With ActiveWorkbook
For K = 2 To wbMstr.Worksheets("Country Input Generation").Cells(Rows.Count, 1).End(xlUp).Row
If wbMstr.Worksheets("Country Input Generation").Cells(K, j) <> "Hide" And wbMstr.Worksheets("Country Input Generation").Cells(K, j) <> "Show" Then
CIGNrange = wbMstr.Worksheets("Country Input Generation").Cells(K, j).Value
CIGWks = wbMstr.Worksheets("Country Input Generation").Cells(K, 1).Value
' Set B1 = Workbooks(fnametwo)
' Set B2 = ThisWorkbook
WriteDebugLogID (Now & " : Copying range " & CIGNrange & " from " & fname & "\" & CIGWks)
Workbooks(fnametwo).Activate
' For Each ar In Split(Mid(Workbooks(fnametwo).Names(CIGNrange).RefersTo, 2), ",")
' ThisWorkbook.Sheets(CIGWks).Range(Split(ar, "!")(1)).Value = Evaluate(ar).Value
' Next
'
' For Each ar In Split(Mid(Workbooks(fnametwo).Names(CIGNrange).RefersTo, 2), ",")
' ThisWorkbook.Sheets(CIGWks).Range(Split(ar, "!")(1)).Value = Evaluate(ar).Formula
' Next
For Each ar In Workbooks(fnametwo).Range(CIGNrange).Areas
ThisWorkbook.Sheets(CIGWks).Range(ar.Address) = ar.Formula
Next
Workbooks(fnametwo).Activate
End If
Next
.Close SaveChanges:=False
End With
wbMstr.Worksheets("Country Input Generation").Select
Next
wbMstr.Worksheets("Start").Select
frmComp.Show
WriteDebugLogID (Now & " : Importing complete ")
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableAnimations = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.StatusBar = Empty
.Cursor = xlDefault
End With
Exit Sub
Import_Data_Errorhandler:
Call LogErrorID("ImportData", Err.Number, Err.Description)
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableAnimations = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.StatusBar = Empty
.Cursor = xlDefault
End With
End Sub
[/VBA]
