VBA Import Data Question

[Glasgowsmile]

Hello!

Currently my spreadsheet uses the following code, which I just found off another site. It's great but I need to advance it a bit and don't know how.

I would like to be able to select the document but have it automatically import a specific range (which always remains the same) within the spreadsheet and then place it in a specific spot on my current spreadsheet.

Where do I start? lol

Sub ImportDatafromotherworksheet()    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 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        .Show
        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", Type:=8)
            wkbCrntWorkBook.Activate
            Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8)
            rngSourceRange.Copy rngDestination
            rngDestination.CurrentRegion.EntireColumn.AutoFit
            wkbSourceBook.Close False
        End If
    End With
End Sub

 

[Glasgowsmile]

Sorry, what I meant was... I don't know where the V = Sheets part goes in the code as a whole.

Does it go at the top like this? I assume not because this doesn't work now.

Sub Hotel1()   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Dim Rng As Range
   Dim Cols As Long
   v = Sheets("Glance").Range("Q11").Value / 100
   
   Set wkbCrntWorkBook = ActiveWorkbook
   
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Cols = Application.CountA(wkbCrntWorkBook.Sheets("HotelData").Range("C3:N3")) + 3
         wkbCrntWorkBook.Sheets("HotelData").Cells(3, Cols).Value = v
         wkbCrntWorkBook.Sheets("HotelData").Cells(3, Cols).CurrentRegion.EntireColumn.AutoFit
         Cols = Application.CountA(wkbCrntWorkBook.Sheets("HotelData").Range("R3:AC3")) + 18
         Sheets("Glance").Range("L11").Copy wkbCrntWorkBook.Sheets("HotelData").Cells(3, Cols)
         wkbCrntWorkBook.Sheets("HotelData").Cells(3, Cols).CurrentRegion.EntireColumn.AutoFit
         Cols = Application.CountA(wkbCrntWorkBook.Sheets("HotelData").Range("AG3:AR3")) + 33
         Sheets("Glance").Range("G11").Copy wkbCrntWorkBook.Sheets("HotelData").Cells(3, Cols)
         wkbCrntWorkBook.Sheets("HotelData").Cells(3, Cols).CurrentRegion.EntireColumn.AutoFit
         wkbSourceBook.Close False
      End If
    End With
End Sub

 

[Glasgowsmile]

Disregard - I put it here and it works now.

 If .SelectedItems.Count > 0 Then         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         v = Sheets("Glance").Range("Q11").Value / 100

Thanks again