We help IT Professionals succeed at work.

Copy a range of Excel values from one workbook and paste in the current workbook

Hi,
         I have a Excel sheet which has a button function.On clicking this button an Excel file can be selected.
          The selected file has different set of worksheets. I would like to select a range of worksheet from this Excel file and paste in the current sheet which has the button function.

I tried with the code below, but I get error and the values are not pasted.
Can anyone help to do this..
Private Sub btnOpenExcel_Click()
OpenExcel
End Sub

Sub OpenExcel()
   ' Variable declaration
    Dim intCounter As Integer
    Dim varFileToOpen As Variant
   
    ' Select files to open
  
       varFileToOpen = Application.GetOpenFilename(Title:="Please select the  file from which data should be copied. File #", FileFilter:="Excel Files *.xls (*.xls),")       
        If varFileToOpen = False Then
            MsgBox "No file specified.", vbCritical, "ERROR!"
            Exit Sub             
      
  Else               
            ActiveSheet.Range(DATE_COLUMN & (DATE_ROW)).Value = varFileToOpen        
       CopyPaste
        End If                  
End Sub

Sub CopyPaste() 
 Dim strFirstFile As String
 Dim strSecondFile  As String
 Dim wbk As Workbook 
 strFirstFile = "xxx"

Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("AAA")
    Range("D14:D54").Copy
End With

Set wbk = ActiveWorkbook
With wbk.Sheets("BBB").Select
    Range("E16:E56").PasteSpecial
End With
End Sub

Open in new window


Thanks in advance
Comment
Watch Question

Analyst Assistant
Commented:
It's not 100% clear what you want to do.

This code will allow the user to choose a workbook

It will then open that workbook, copy values from worksheet AAA and paste them into worksheet BBB in the original workbook.
Option Explicit

Private Sub btnOpenExcel_Click()
OpenExcel
End Sub

Sub OpenExcel()

' Variable declaration
Dim intCounter As Integer
Dim varFileToOpen As Variant
Dim strFirstFile As String
Dim strSecondFile As String
Dim wbk As Workbook
    ' Select files to open

    varFileToOpen = Application.GetOpenFilename(Title:="Please select the  file from which data should be copied. File #", FileFilter:="Excel Files *.xls (*.xls),")
    If varFileToOpen = False Then
        MsgBox "No file specified.", vbCritical, "ERROR!"
        Exit Sub

    Else
        'ActiveSheet.Range(DATE_COLUMN & (DATE_ROW)).Value = varFileToOpen
        Set wbk = Workbooks.Open(varFileToOpen)
        With wbk.Sheets("AAA")
            .Range("D14:D54").Copy
        End With

        With ThisWorkbook.Sheets("BBB")
            .Range("E16:E56").PasteSpecial
        End With

        wbk.Close False
    End If
End Sub

Open in new window

Top Expert 2011

Commented:
Could you please specify if you need to copy all data from one workbook, from all sheets to another workbook in a single sheet OR copy all sheets from one workbook as separate sheets to another workbook?