Solved: New to VBA - Help me in making this code shorter and better

[Bob Phillips]

Works okay for me. What error do you get?

[vicks]

After opening the second file , i get the error on line [saying Run time error'-2147221080 (800401a8)
Automation errror]

wb.Sheets(1).Copy After:=ThisWb.Sheets(1)

[vicks]

Hi I did some trial and error and could solve it. Here is the code which i used. Apprantely Set Wb = was missing from the second loop. Can you tell me what does Set do?


[VBA]Sub Copy_Data()
Dim ThisWb As Workbook
Dim wb As Workbook

Set ThisWb = Workbooks("Consolidated.xls")

Set wb = Workbooks.Open(FileName:=("C:/VBA Source/Euro.xls"))
wb.Sheets(1).Copy After:=ThisWb.Sheets(1)
ThisWb.Worksheets(2).Name = Replace(Replace(wb.Name, ".xls", ""), "C:/VBA Source/", "")
wb.Close

Set wb = Workbooks.Open(FileName:=("C:/VBA Source/USD.xls"))
wb.Sheets(1).Copy After:=ThisWb.Sheets(1)
ThisWb.Worksheets(2).Name = Replace(Replace(wb.Name, ".xls", ""), "C:/VBA Source/", "")
wb.Close

Set wb = Workbooks.Open(FileName:=("C:/VBA Source/JPY.xls"))
wb.Sheets(1).Copy After:=ThisWb.Sheets(1)
ThisWb.Worksheets(2).Name = Replace(Replace(wb.Name, ".xls", ""), "C:/VBA Source/", "")
wb.Close

Set wb = Workbooks.Open(FileName:=("C:/VBA Source/GBP.xls"))
wb.Sheets(1).Copy After:=ThisWb.Sheets(1)
ThisWb.Worksheets(2).Name = Replace(Replace(wb.Name, ".xls", ""), "C:/VBA Source/", "")
wb.Close

MsgBox ("Copying worksheets completed")

End Sub[/VBA]

[vicks]

If I want to run above Macro in the same file again How to first delete existing sheets in the file with name USD, Euro, JPY and GBP (Or to say other way round delete all sheets except for Sheet1), so that programme runs properly.

[Bob Phillips]

Hi I did some trial and error and could solve it. Here is the code which i used. Apprantely Set Wb = was missing from the second loop. Can you tell me what does Set do?

It assigns an object to an object variable.

Most variables such as

myvar = 10

are really saying

Let myvar = 10

Set is used to distinguish objects from data types.