VBA Import Data Question

Glasgowsmile

Active Member
Joined
Apr 14, 2018
Messages
280
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 
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.

Code:
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
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Disregard - I put it here and it works now.

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

Thanks again :)
 
Upvote 0

Forum statistics

Threads
1,224,847
Messages
6,181,321
Members
453,032
Latest member
Pauh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top