[SOLVED] Import data from a closed workbook into an existing worksheet using VBA?

Import data from a closed workbook into an existing worksheet using VBA?

I've got a piece of VBA code that will import a piece of data from another workbook into my open excel spreadsheet however, it won't import if into the sheet that I have already created. It wants to open a new sheet every time.

This is the code:

Sub ImportCurrentMonthData()
' Get workbook...
Dim ws As Worksheet
Dim filter As String
Dim targetWorkbook As Workbook, wb As Workbook
Dim Ret As Variant

Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Excel files (*.xlsb),*.xlsb"
Caption = "Please Select an input file "
Ret = Application.GetOpenFilename(filter, , Caption)

If Ret = False Then Exit Sub

Set wb = Workbooks.Open(Ret)

wb.Sheets(1).Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)

ActiveSheet.Name = "CurrentMonth"

I have a sheet called "CurrentMonth" already and I want to data to go into that spreadsheet. What do I need to change in the VBA code for this happen?

Thanks in advance.

Solution

If you just want top copy the data to an existing sheet....

Sub ImportCurrentMonthData()

    ' Get workbook...
    Dim ws As Worksheet
    Dim filter As String
    Dim targetWorkbook As Workbook, wb As Workbook
    Dim Ret As Variant

    Set targetWorkbook = Application.ActiveWorkbook 'ThisWorkbook?

    ' get the customer workbook
    filter = "Excel files (*.xlsb),*.xlsb"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(filter, , Caption)

    If Ret = False Then Exit Sub

    Set wb = Workbooks.Open(Ret)

    wb.Sheets(1).Usedrange.Copy targetWorkbook.Sheets("CurrentMonth").Range("A1")