Vba: Import Data From Another Worksheet (Paste Value)

[muhammad susanto]

hi all.

i found vba code to import data from another , this code below work properly
unfortunally, data that success imported still contains formula .
i want to how to code work with result ..like Paste Value not included formula

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; *.xlsm; *.xls"
        .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:
in "destination sheet" i don't use AutoFit Column....
i want to remove auto fit column
someone help me, great appreciated

.sst

 

[muhammad susanto]

sorry i missing it:
in "destination sheet" i don't use AutoFit Column....
1). i want to remove auto fit column

2) in "destination sheet" show input box "Select destination cell" then i must do select/drag cell needed, how to make more simple...
just type start cell to representative select all range that needed ...e.g. "Type start cell destination.."

 

[Alex Blakenburg]

Try this for the Paste Values & Remove Autofit.
I don't understand your "2)" above since you don't have to select a destination area, the code only requires your to select the start cell.

Sub ImportDatafromotherworksheet_Orig()
    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; *.xlsm; *.xls"
        .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)
            
            '----------- Changes made ------------
            ' XXX Change Copy to Copy Paste Special
            ' rngSourceRange.Copy rngDestination
            rngSourceRange.Copy
            rngDestination.PasteSpecial Paste:=xlPasteValues    ' My preference would be to use xlPasteValuesAndNumberFormats
            ' rngDestination.PasteSpecial Paste:=xlPasteFormats ' Uncomment this if you also want the Format (leave the previous as just values)
            
            ' XXX Comment out autofit as requested
            'rngDestination.CurrentRegion.EntireColumn.AutoFit
            '----------- End of Changes made ------------
            
            wkbSourceBook.Close False
        End If
    End With
End Sub

 

[muhammad susanto]

hi Alex, thank you so much!!

 

[Alex Blakenburg]

Glad I could help. Thanks for the feedback.