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]

:O

Works perfectly. You are the best. Thank you so much for you help

 

[Fluff]

Glad to help & thanks for the feedback

 

[Glasgowsmile]

One last thing - do you know how I would change the font type/size and alignment of the imported data? It doesn't really matter much, but I'm just curious.

 

[Fluff]

Something like

With wkbCrntWorkBook.Sheets("HotelData").Cells(30, Cols)
   .CurrentRegion.EntireColumn.AutoFit
   .Font = "Arial"
   .Font.size = "11"
   .HorizontalAlignment = xlCenter
End With

 

[Glasgowsmile]

Thanks!

In the meantime - I ran into a bit of a problem.

Q11 on the spreadsheet I'm copying shows on the report as 33.51, but when you look at the actual number it's 33.51414427157. The macro shows 33.51 which is fine but I want it displayed as 33.51% and since it pulls the entire number it thinks it is 3351% and I can't remove enough decimals to change it. So how do I copy the data exactly as it is displayed instead of the number in the box behind the scenes?

I hope that makes sense lol

Sub Hotel1()   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Dim Rng As Range
   Dim Cols As Long


   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
         Sheets("Glance").Range("Q11").Copy wkbCrntWorkBook.Sheets("HotelData").Cells(3, Cols)
         wkbCrntWorkBook.Sheets("HotelData").Cells(30, 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(30, 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(30, Cols).CurrentRegion.EntireColumn.AutoFit
         wkbSourceBook.Close False
      End If
   End With
End Sub

 

[Fluff]

33.51 is 3351% if you want it displayed as 33.51 then set the format to general.

 

[Glasgowsmile]

Let me try to explain it better.

I don't want it to pull all those additional numbers.

When it pulls that and shows up at 3000% it ruins another formula in the spreadsheet that references another %.

Basically I have a % in the spreadsheet (and I can't change it) and I'm comparing it against this % being pulled from the spreadsheet, using the following code.

=IF(HotelData!AH4 = "","",HotelData!AH4-AG1819)

If it pulled as 30 or something smaller I could just move the decimals to correct the %, right?

Or is there a better way?

I hope that clears up what I'm attempting to do.

 

[Fluff]

How about like

v = Sheets("Glance").Range("Q11").Value / 100
wkbCrntWorkBook.Sheets("HotelData").Cells(3, Cols).Value = v

 

[Glasgowsmile]

Thanks but where exactly do I put this information?

Sub Hotel1()   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Dim Rng As Range
   Dim Cols As Long


   
   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
         Sheets("Glance").Range("Q11").Copy wkbCrntWorkBook.Sheets("HotelData").Cells(3, Cols)
         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

 

[Fluff]

Replace this line

Sheets("Glance").Range("Q11").Copy wkbCrntWorkBook.Sheets("HotelData").Cells(3, Cols)

with the code in post#18 & do the same for the other lines if needed.