New VBA to Import Data

[Jvollmer33]

I am trying to create an Excel Macro that will do the following.
1. Prompt me to choose another excel sheet to to open and allow me to pick the file using MSO FILE DIALOG OPEN
2. CLear the contents of the one of the sheets on my workbook("Data")
3. Take the file that I Choose and copy range A1:1650 and then paste special with formating the cells into the current workbook to sheet("Data")

Right now I have a Macro that does this except that I have to choose the range with a Input box and I dont want to have to do that. Please see below.

Sub ImportData()
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook

    Dim rngSourceRange As Range
    Dim rngDestination As Range

    Set wkbCrntWorkBook = ActiveWorkbook

    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2002-03", "*.xls", 1
        .Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2
        .AllowMultiSelect = False
        .Show
     Sheets("Data").Range("a1:l650").ClearContents

        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1:l650", Type:=8)
            wkbCrntWorkBook.Activate
            Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A1:l650", Type:=8)
            rngSourceRange.Copy rngDestination
            rngDestination.CurrentRegion.EntireColumn.AutoFit
            wkbSourceBook.Close False
        End If
    End With
End Sub

[JBeaucaire]

Like so:

            Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
            wkbSourceBook.Sheets("Sheet1").Range("A1:1650").Copy
            wkbCrntWorkBook.Sheets("Data").Range("A1").PasteSpecial xlPasteFormats
            wkbCrntWorkBook.Sheets("Data").Range("A1").PasteSpecial xlPasteValues
            wkbSourceBook.Close False

[Jvollmer33]

What if the sourcebook that is opened has a worsheet name that always changes. How can I have the macro just choose that sheet that will be active when the file is opened instead of putting a worksheet name into the macro?

[JBeaucaire]

That's a dangerous approach. But you can replace the workbook.sheet("name") portion of the code with the simple reference to ActiveSheet since it will BE the activesheet once the workbook was just opened.

            Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
            Activesheet.Range("A1:1650").Copy
            wkbCrntWorkBook.Sheets("Data").Range("A1").PasteSpecial xlPasteFormats
            wkbCrntWorkBook.Sheets("Data").Range("A1").PasteSpecial xlPasteValues
            wkbSourceBook.Close False

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Jvollmer33]

I am now getting two errors. Please see code below??

Sub ImportDatanew()
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
     
    Dim rngSourceRange As Range
    Dim rngDestination As Range
     
    Set wkbCrntWorkBook = ActiveWorkbook
     
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2002-03", "*.xls", 1
        .Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2
        .AllowMultiSelect = False
        .Show
    Sheets("Data").Range("a1:l650").ClearContents **************Error
    
        If .SelectedItems.Count > 0 Then
           Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
           Set wkbSourceBook = ActiveWorkbook
           ActiveSheet.Range("A1:1650").Copy **********ERROR
            wkbCrntWorkBook.Sheets("Data").Range("A1").PasteSpecial xlPasteFormats
            wkbCrntWorkBook.Sheets("Data").Range("A1").PasteSpecial xlPasteValues
            wkbSourceBook.Close False
        End If
    End With
End Sub

[JBeaucaire]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


I corrected your post #1, now you must do the post above.
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Jvollmer33]

I am now getting two errors. Please see code below??

Sub ImportDatanew()
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
     
    Dim rngSourceRange As Range
    Dim rngDestination As Range
     
    Set wkbCrntWorkBook = ActiveWorkbook
     
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2002-03", "*.xls", 1
        .Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2
        .AllowMultiSelect = False
        .Show
    Sheets("Data").Range("a1:l650").ClearContents **************Error
    
        If .SelectedItems.Count > 0 Then
           Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
           Set wkbSourceBook = ActiveWorkbook
           ActiveSheet.Range("A1:1650").Copy **********ERROR
            wkbCrntWorkBook.Sheets("Data").Range("A1").PasteSpecial xlPasteFormats
            wkbCrntWorkBook.Sheets("Data").Range("A1").PasteSpecial xlPasteValues
            wkbSourceBook.Close False
        End If
    End With
End Sub

[JBeaucaire]

I didn't correct any of your RANGE code, A1:1650 is wrong. Perhaps it should be: A1:A1650 or A1:Z1650 ?