Nilusha
09-28-2018, 06:02 PM
Since I am new to VBA I created a code which can open a .csv file and copy data from .csv to an excel file without opening both.(It looks like both are not opening)
My source file is a .csv (AAA.csv) and my destination file is a .xlsx (BBB.xlsx) which has various multiple sheets but I need to paste into sheet name "FEES"
Actually it works for excel files but When I use a .csv file it displays me an error message "SUBSCRIPT OUT OF RANGE".How do I solve this? Thank You!
Sub Copywb1()
Dim wkb1 As Workbook
Dim sht1 As Worksheet
Dim wkb2 As Workbook
Dim sht2 As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks.Open("C:\Desktop\BBB.xlsx")
Set wkb1 = Workbooks.Open("C\Reports\AAA.csv")
Set sht1 = wkb1.Sheets("Reports")
Set sht2 = wkb2.Sheets("Fees")
sht1.Range("A1:BM9").Copy
sht2.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
wkb2.Close True
wkb1.Close True
End Sub
My source file is a .csv (AAA.csv) and my destination file is a .xlsx (BBB.xlsx) which has various multiple sheets but I need to paste into sheet name "FEES"
Actually it works for excel files but When I use a .csv file it displays me an error message "SUBSCRIPT OUT OF RANGE".How do I solve this? Thank You!
Sub Copywb1()
Dim wkb1 As Workbook
Dim sht1 As Worksheet
Dim wkb2 As Workbook
Dim sht2 As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks.Open("C:\Desktop\BBB.xlsx")
Set wkb1 = Workbooks.Open("C\Reports\AAA.csv")
Set sht1 = wkb1.Sheets("Reports")
Set sht2 = wkb2.Sheets("Fees")
sht1.Range("A1:BM9").Copy
sht2.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
wkb2.Close True
wkb1.Close True
End Sub