none
Copy From variable file location and variable range using VBA RRS feed

  • Question

  • Hi

    I am trying to write a code to copy cells from a variable location and sheet and size to my "Data" worksheet, I have taken the code shown below from another post however I need for the code to copy the entire range not just the row. I am new to VBA and struggling to adjust the range accordingly

    Is anyone able to help me with this?

    Private Sub CommandButton3_Click()
        Dim fileDialog As fileDialog
        Dim strPathFile As String
        Dim strFileName As String
        Dim strPath As String
        Dim dialogTitle As String
        Dim wbSource As Workbook
        Dim rngToCopy As Range
        Dim rngRow As Range
        Dim rngDestin As Range
        Dim lngRowsCopied As Long
        
        
        dialogTitle = "Navigate to and select required file."
        Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
        With fileDialog
            .InitialFileName = "C:\Users\User\Documents"
            '.InitialFileName = ThisWorkbook.Path & "\" 'Alternative to previous line
           .AllowMultiSelect = False
            .Filters.Clear
            .Title = dialogTitle
            
            
            
            If .Show = False Then
                MsgBox "File not selected to import. Process Terminated"
                Exit Sub
            End If
            strPathFile = .SelectedItems(1)
        End With
         
        Set wbSource = Workbooks.Open(filename:=strPathFile)
        Dim myRange As Range
    
        Set myRange = Application.InputBox(prompt:="Please select the cell you want to copy", Type:=8)
        Dim targetSheet As Worksheet
        Set targetSheet = wbSource.ActiveSheet
      
        'get the row of user select
       Set myRange = targetSheet.Range(targetSheet.Cells(myRange.Row, 1), targetSheet.Cells(myRange.Row, targetSheet.Columns.Count).End(xlToLeft))
        
        'copy data when there is an not empty cell in the range
        If WorksheetFunction.CountA(myRange) <> 0 Then
            Set rngDestin = ThisWorkbook.Sheets("Sheet1").Cells(1, "A")
                  
            myRange.SpecialCells(xlCellTypeVisible).Copy Destination:=rngDestin
        End If
    
        wbSource.Close SaveChanges:=False
        
        Set fileDialog = Nothing
        Set rngRow = Nothing
        Set rngToCopy = Nothing
        Set wbSource = Nothing
        Set rngDestin = Nothing
        
        'MsgBox "The data is copied"
    
    End Sub
    Friday, November 6, 2015 1:27 AM

Answers

  • Hi PatD985,

    Base on this code:

    Set myRange = Application.InputBox(prompt:="Please select the cell you want to copy", Type:=8)
        Dim targetSheet As Worksheet
        Set targetSheet = wbSource.ActiveSheet
      
        'get the row of user select
       Set myRange = targetSheet.Range(targetSheet.Cells(myRange.Row, 1), targetSheet.Cells(myRange.Row, targetSheet.Columns.Count).End(xlToLeft))
        
    

    The myRange value changed when call targetSheet.Range() method, that why it copies the row. Please define a new variable to store the returned value of targetSheet.Range().

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 12, 2015 2:37 AM
    Moderator

All replies

  • Why do you ask this again? You already asked exactly the same question in Copy From variable file location using VBA and got a reply there...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 6, 2015 10:31 AM
  • Hi PatD985,

    Base on this code:

    Set myRange = Application.InputBox(prompt:="Please select the cell you want to copy", Type:=8)
        Dim targetSheet As Worksheet
        Set targetSheet = wbSource.ActiveSheet
      
        'get the row of user select
       Set myRange = targetSheet.Range(targetSheet.Cells(myRange.Row, 1), targetSheet.Cells(myRange.Row, targetSheet.Columns.Count).End(xlToLeft))
        
    

    The myRange value changed when call targetSheet.Range() method, that why it copies the row. Please define a new variable to store the returned value of targetSheet.Range().

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 12, 2015 2:37 AM
    Moderator