Run Macro Repeatedly To Load New Sheets

[kalcerro_1]

Hello.
I have a macro which is loading two worksheets from other workbook.
I want the macro to assign a name to those new sheets using a cell value. (Sheet #1 taking value from cell B4, sheet #2 need a generic one)
A third sheet inside the workbook is taking some values from new sheet #2.
The problem I'm facing is that with the code I have written I can only load one pair of sheets, when I try to load a new pair, one sheet already have that name taken.
Please take a look at the code below, and tell me what can I improve to have a functional code to load 'n" pair of sheets, with different names in it.

Thanks

Karin.

Private Sub CommandButton1_Click()
 
 Application.DisplayAlerts = False
 Application.ScreenUpdating = False
 
 Dim FileToImport As Variant
 Dim WbCount As Integer

 
 
    FileToImport = Application.GetOpenFilename(FileFilter:="XLSM's  (*.xlsm), *.xlsm", Title:="Select Partner Result to import")
        If FileToImport = False Then Exit Sub
    MsgBox "This process will take a few seconds, please wait", vbOKOnly

 Workbooks.Open FileToImport

    Worksheets(Array("6.Results", "TechResults")).Copy After:=ThisWorkbook.Sheets(1)
    Sheets("6.results").Select
    Sheets("6.Results").Rows("38:67").Select
    Selection.Delete Shift:=xlUp
    Sheets("6.Results").Range("B4").Select
    Sheets("6.Results").Name = ActiveSheet.Range("B4")
    Sheets("TechResults").Visible = True
    Sheets("TechResults").Select
    Sheets("TechResults").Range("G1").Value = ("TPR1")
    Sheets("TechResults").Name = ActiveSheet.Range("G1")
    
    
    Sheets("Sheet1").Range("AA12").Formula = "=IFERROR('tpr1'!A2,0)"
    Sheets("Sheet1").Range("AB12").Formula = "=IFERROR('tpr1'!F50,0)"
    Sheets("Sheet1").Range("AC12").Formula = "=IFERROR('tpr1'!I50,0)"
    Sheets("Sheet1").Range("AD12").Formula = "=IFERROR('tpr1'!L50,0)"
    Sheets("Sheet1").Range("AE12").Formula = "=IFERROR('tpr1'!O50,0)"
    Sheets("Sheet1").Range("AF12").Formula = "=IFERROR('tpr1'!R50,0)"
    Sheets("Sheet1").Range("AG12").Formula = "=IFERROR('tpr1'!U50,0)"
    Sheets("Sheet1").Range("AH12").Formula = "=IFERROR('tpr1'!Y50,0)"
    Sheets("Sheet1").Range("AI12").Formula = "=IFERROR('tpr1'!AG50,0)"
    Sheets("Sheet1").Range("AJ12").Formula = "=IFERROR('tpr1'!AO50,0)"
    Sheets("Sheet1").Range("AK12").Formula = "=IFERROR('tpr1'!AS50,0)"
    Sheets("Sheet1").Range("AL12").Formula = "=IFERROR('tpr1'!AV50,0)"


WbCount = Workbooks.Count
For i = WbCount To 1 Step -1
If Workbooks(i).Name <> ThisWorkbook.Name Then
Workbooks(i).Close
End If
Next i


Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

 

[Zot]

Not sure I understood overall picture. Do all the workbook you are opening have sheets named 6.Results and TechResults? If so, does each of the workbook have range B4 and G1 has unique names? If not, then it is causing the problem you are seeing.

 

[kalcerro_1]

Not sure I understood overall picture. Do all the workbook you are opening have sheets named 6.Results and TechResults? If so, does each of the workbook have range B4 and G1 has unique names? If not, then it is causing the problem you are seeing.

Hello @Zot, Yes, All the workbooks have the 6.Results and TechResults sheets, I managed to change the 6.Results name, with a cell value, and works fine, but a need a new name for TechResults every time a new pair of sheets is loaded and then use that sheet name to assign a formula.

 

[Zot]

Hello @Zot, Yes, All the workbooks have the 6.Results and TechResults sheets, I managed to change the 6.Results name, with a cell value, and works fine, but a need a new name for TechResults every time a new pair of sheets is loaded and then use that sheet name to assign a formula.

I guess what you were trying to do is to open a workbook. Insert 2 sheets which are copies of 6.Results and TechResults. Rename and close workbook. Then I don't see why you need to loop to close workbook at the each of each routine. Does this also close you macro workbook?

I presumed you are running the macro from another workbook. Once button is click, you are prompt to select a file. The opened workbook will have 2 sheets created, renamed and then you need to close the workbook you opened, right?

I believe the problem you are facing because the macro runs into confusion to which workbook the sheets are referring to. It is good practice to create references for range, sheet and also workbook.

I'm just guessing what you were trying to do. So, my code below might not do what you intend to do. If so, then treat it like a reference.

Private Sub CommandButton1_Click()
 
 Application.DisplayAlerts = False
 Application.ScreenUpdating = False
 
 Dim FileToImport As Variant
 Dim WbCount As Integer
 Dim wbProgram As Workbook, wb As Workbook
 Dim ws6 As Worksheet, wsTech As Worksheet
 
    Set wbProgram = ActiveWorkbook
    
    FileToImport = Application.GetOpenFilename(FileFilter:="XLSM's  (*.xlsm), *.xlsm", Title:="Select Partner Result to import")
        If FileToImport = False Then Exit Sub
    MsgBox "This process will take a few seconds, please wait", vbOKOnly
    
    ' Create refernce to open workbook
    Set wb = Workbooks.Open(FileToImport)

    wb.Worksheets(Array("6.Results", "TechResults")).Copy After:=ThisWorkbook.Sheets(1)
    
    Set ws6 = wb.Sheets("6.results")
    Set wsTech = wb.Sheets("TechResults")
    
    With ws6
        .Rows("38:67").Delete Shift:=xlUp
        .Name = .Range("B4")
    End With
    With wsTech
        .Visible = True
        .Range("G1").Value = "TPR1"
        .Name = .Range("G1")
    End With
    
    With wb.Sheets("Sheet1")
        .Range("AA12").Formula = "=IFERROR('tpr1'!A2,0)"
        .Range("AB12").Formula = "=IFERROR('tpr1'!F50,0)"
        .Range("AC12").Formula = "=IFERROR('tpr1'!I50,0)"
        .Range("AD12").Formula = "=IFERROR('tpr1'!L50,0)"
        .Range("AE12").Formula = "=IFERROR('tpr1'!O50,0)"
        .Range("AF12").Formula = "=IFERROR('tpr1'!R50,0)"
        .Range("AG12").Formula = "=IFERROR('tpr1'!U50,0)"
        .Range("AH12").Formula = "=IFERROR('tpr1'!Y50,0)"
        .Range("AI12").Formula = "=IFERROR('tpr1'!AG50,0)"
        .Range("AJ12").Formula = "=IFERROR('tpr1'!AO50,0)"
        .Range("AK12").Formula = "=IFERROR('tpr1'!AS50,0)"
        .Range("AL12").Formula = "=IFERROR('tpr1'!AV50,0)"
    End With
    wb.Close True    ' Put False if you want to close workbook without saving.

Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub