Hello everybody,
I have built this code which import data from a workbook and paste it to another one.
The original workbook is composed by hundred of sheets (one sheet for each country, identified by the ISO 2 digit code: AE, AL, AM, AR etc...). The macro is opening each one of these sheets, copying the same cell, and printing all these cells in a new workbook.
The problem is that if, for example, the sheet F(AM) doesn't exists, the macro stops. I would like to make sure that if a sheet doesn't exist, the macro continues with all the other sheets (namely F(AR), F(AT), F(AU)) till the end.
Someone has any suggestion?
Many thanks in advance!
I have built this code which import data from a workbook and paste it to another one.
The original workbook is composed by hundred of sheets (one sheet for each country, identified by the ISO 2 digit code: AE, AL, AM, AR etc...). The macro is opening each one of these sheets, copying the same cell, and printing all these cells in a new workbook.
The problem is that if, for example, the sheet F(AM) doesn't exists, the macro stops. I would like to make sure that if a sheet doesn't exist, the macro continues with all the other sheets (namely F(AR), F(AT), F(AU)) till the end.
Someone has any suggestion?
Many thanks in advance!
Code:
Sub ImportData()
Dim Wb1 As Workbook
Dim MainBook As Workbook
Dim Path As String
Dim SheetName As String
'Specify input data
Path = Worksheets("Input").Range("C6").Value
'Decide in which target sheet print the results
SheetName = "Data"
'From which sheets you need to take the data?
OriginSheet145 = "F(AE)"
OriginSheet146 = "F(AL)"
OriginSheet147 = "F(AM)"
OriginSheet148 = "F(AR)"
OriginSheet149 = "F(AT)"
OriginSheet150 = "F(AU)"
'Set the origin workbook
Set Wb1 = Workbooks.Open(Path & "_20171231.xlsx")
'Set the target workbook
Set MainBook = ThisWorkbook
'Vlookup to identify the correct data point
Wb1.Sheets(OriginSheet145).Range("N25").FormulaR1C1 = "=VLOOKUP(""010"",C[-10]:C[-7],2,FALSE)"
Wb1.Sheets(OriginSheet146).Range("N26").FormulaR1C1 = "=VLOOKUP(""010"",C[-10]:C[-7],2,FALSE)"
Wb1.Sheets(OriginSheet147).Range("N27").FormulaR1C1 = "=VLOOKUP(""010"",C[-10]:C[-7],2,FALSE)"
Wb1.Sheets(OriginSheet148).Range("N28").FormulaR1C1 = "=VLOOKUP(""010"",C[-10]:C[-7],2,FALSE)"
Wb1.Sheets(OriginSheet149).Range("N29").FormulaR1C1 = "=VLOOKUP(""010"",C[-10]:C[-7],2,FALSE)"
Wb1.Sheets(OriginSheet150).Range("N30").FormulaR1C1 = "=VLOOKUP(""010"",C[-10]:C[-7],2,FALSE)"
'Copy the data point and paste in the target sheet
Wb1.Sheets(OriginSheet145).Range("N25").Copy
MainBook.Sheets(SheetName).Range("AW5").PasteSpecial xlPasteValues
Wb1.Sheets(OriginSheet146).Range("N26").Copy
MainBook.Sheets(SheetName).Range("AW6").PasteSpecial xlPasteValues
Wb1.Sheets(OriginSheet147).Range("N27").Copy
MainBook.Sheets(SheetName).Range("AW7").PasteSpecial xlPasteValues
Wb1.Sheets(OriginSheet148).Range("N28").Copy
MainBook.Sheets(SheetName).Range("AW8").PasteSpecial xlPasteValues
Wb1.Sheets(OriginSheet149).Range("N29").Copy
MainBook.Sheets(SheetName).Range("AW9").PasteSpecial xlPasteValues
Wb1.Sheets(OriginSheet150).Range("N30").Copy
MainBook.Save
Wb1.Close savechanges:=False
MsgBox "Data: imported!"
End Sub