CODEWITHSUNDEEP
vbaexcel
Piyush Divyanakar
Piyush Divyanakar

Reputation: 241

Copying data from one excel workbook to another using vba

I have a workbook which I am working with. I need to import data from another workbook. The first workbook is called MyBook.xls and second one is called protfolioData.xls. The path to protfolio data is in the cell D12. How can I create a vbscript that takes the path form D12 and copies data in cells A4:D26 from protfolio data and create a newSheet in MyBook.xls. I am currently trying the following, it throws an error ambigous name when macro is run from MyBook.xls. 

    ext_file = Workbooks(mainFile).Worksheets("config").Range("D12").Value
    Workbooks(ext_file).Sheets("Sheet1").Rows("4:26").Copy _
    Workbooks(mainFile).Sheets("Sheet2").Rows("1:22")

Upvotes: 0

Views: 2541

Answers (1)

LatifaShi
LatifaShi

Reputation: 440

Maybe Try the following code :

    Sub test()

        'Get the Path
        ext_file = ThisWorkbook.Sheets("config").Range("D12").Value

        'Get the workbook name from the path
        GetFileName = Right(ext_file, Len(ext_file) - InStrRev(ext_file, "\"))

        'open and activate the workbook
        Workbooks.Open Filename:=ext_file
        Workbooks(GetFileName).Activate

        'Add new sheet to thisworkbook ( MyBook.xls )
        With ThisWorkbook
            Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        End With

        'Copy rows 4 to 26 from protfolioData.xls and paste in rows 1 to 23 in ( MyBook.xls )
        Workbooks(GetFileName).Sheets("Sheet1").Rows("4:26").Copy _
        Destination:=ws.Rows("1:23")

        'close protfolioData.xls
        Workbooks(GetFileName).Close savechanges:=False


    End Sub

Upvotes: 2

Related Questions