I have 2 lists in my excel: column b with country name and column c with country code.
Step 1: go through list in column c and copy it to specific places in sheet 2 and sheet 3
Step 2: create a new workbook with corresponding b value as the same and copy sheet 2 and sheet 3 into this new workbook.
Example:
Col b col c
Germany 23
Italy 42
- Excel copies 23 to sheet 2 and 3(in specific cells). Then creates a new workbook in path with name Germany.
- Excel copies 2 to sheet 2 and 3 (in specific cells). Then creates a new workbook in path with name Italy.
Code
Dim bankname As String, bankcode As Integer
Dim i, lastrow
lastrow = Sheets("Main").Range("B" & Rows.Count).End(xlUp).Row
For i = 9 To lastrow
If Sheets("Main").Cells(i, "C").Value <> "" Then
Sheets("Main").Cells(i, "C").Copy Destination:=Sheets("SNLDownload").Range("D6:R6")
'calls on the sub refresh data to use snl addin to refresh the data
Call RefreshSNLdata
'creates a new workbook with the 3 sheets in the workbook
Dim wbO As Workbook, wbN As Workbook
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Set wbO = ActiveWorkbook
Set wbN = Workbooks.Add
wbO.Sheets("SNLDownload").Copy wbN.Sheets(1)
wbO.Sheets("Financials").Copy wbN.Sheets(2)
wbO.Sheets("Ratio").Copy wbN.Sheets(3)
wbN.Sheets("Sheet1").Delete
wbN.Sheets("Customers").Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
ErrHandler:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End If
Next i
Display More