open unknown excel workbook, copy data and paste back in original workbook

[omono84]

I know that this should be rather simple but i seem to be missing a step to get it to work. and have been unable to find a solution on the net.

The aim is that I click on the open button to find and open an unknown workbook that contains the data that I need to imput into my current workbook, once the unknown workbook is opened it should automatically select my range (number of columns known, but number of rows unknown- the number of rows needed to be copied will change each time the unknown workbook is opened). this range will then be copied and pasted into the original workbook (book1.xls) starting in cellA17.

the below code opens the unknown workbook fine but it will not select the range properly. I have tried the range select in the unknown workbook by itself and it works like a charm but in the below code it seems to get very very confused as to which workbook it should be looking in and i don’t know how to make it point correctly so that it will work.



my code:

Sub cmdopen_Click()

'find and open file

Dim vaFiles As Variant
Dim i As Long

vaFiles = Application.GetOpenFilename _
(FileFilter:="Excel Filer (*.xls),*.xls", _
Title:="Open File(s)", MultiSelect:=True)

If Not IsArray(vaFiles) Then Exit Sub

With Application
.ScreenUpdating = False
For i = 1 To UBound(vaFiles)
Workbooks.Open vaFiles(i)
Next i
.ScreenUpdating = True
End With

'so far code works great but from here i start having problems.....

' select woksheet2 and select range of cells from A2 (to E2 and down unknow number of rows)

Worksheets("Sheet2").Range("a2").Select

Range(ActiveCell, Range(ActiveCell.Address).End _
(xlToRight).End(xlDown)).Select.Copy

' this seems to work perfect if i run these three lines in the new workbook, but when running it in conjunction with the code above it it seems to select the data in cell A2 from the original workbook and paste it in the active cell in the newly opened workbook, the active cell is not A2 in new workbook

I would then like the copied data pasted back into original workbook ("book1.xls) in cell A17

any help would be greatly appreciated.

[MikeTheBike]

Hi

Taking you code (and moding it - for instance only one file is opened- and closed!)

Sub cmdopen_Click()
    Dim strFile As String
    Dim i As Long
    Dim TargetSht As Worksheet
    Dim LastRow As Integer
 
    'SET (REMEMBER) CURRENT SHEET AS TARGET SHEET
    Set TargetSht = ActiveSheet
    strFile = Application.GetOpenFilename _
    (FileFilter:="Excel Filer (*.xls),*.xls", _
    Title:="Open File(s)", MultiSelect:=False)
 
    If strFile = "False" Then Exit Sub
 
    Application.ScreenUpdating = False
 
    Workbooks.Open strFile
 
    'THE LAST OPENED WORKBOOK IS AUTOMATICALLY THE ACTIVE WORKBOOK
    Range("A2").Select
    ActiveCell.SpecialCells(xlLastCell).Select
 
    LastRow = ActiveCell.Row
 
    'COPY FROM CURRENT WORKSHEET AND PASTE INTO TARGET SHEET
    Range("A2:E" & LastRow).Copy TargetSht.Range("a17")
 
    ActiveWorkbook.Close False
 
    Application.ScreenUpdating = True
End Sub

HTH


MTB

[omono84]

Many thanks for your help MTB, I'm still quite a novie when it comes to using macros.

I have used the code you supplied and unfortunatly i'm experencing a run time error "Application-defined or object-defined error" on the line:

Range("A2").Select

To try to get around this I changed it to:

Worksheets("Sheet2").Range("A2") = Activecell

The macro ran to the end without error, but it copied the data from the original workbook, not the newly opened workbook????

[MikeTheBike]

Hi

Before we go any further, can you confirm that the 'cmdopen_Click'
subroutine code is located in a Code Module (or even ThisWorkbook Module - not recommended) and NOT the Sheet1 Module?


MTB

[MikeTheBike]

Hi again

After reflecting on my last post (and looking at the code more closely!) I think I have can answer my own question.

My conclusion is this

You have a button on the sheet and the code previously posted is run in the button click event (being as it is called 'cmdopen_Click()' !!).

In this case change the code in the Button Click event in the Sheet Module to

Option Explicit
 
Private Sub cmdopen_Click()
    CopyData
End Sub

and then insert a CODE MODULE (in the left pain Right Click the Microsoft Excel Objects -> Insert -> Module) and paste this code into it

Option Explicit
 
Sub CopyData()
    Dim strFile As String
    Dim i As Long
    Dim TargetSht As Worksheet
    Dim LastRow As Integer
 
    'SET (REMEMBER) CURRENT SHEET AS TARGET SHEET
    Set TargetSht = ActiveSheet
    strFile = Application.GetOpenFilename _
    (FileFilter:="Excel Filer (*.xls),*.xls", _
    Title:="Open File(s)", MultiSelect:=False)
 
    If strFile = "False" Then Exit Sub
 
    Application.ScreenUpdating = False
 
    Workbooks.Open strFile
 
    'THE LAST OPENED WORKBOOK IS AUTOMATICALLY THE ACTIVE WORKBOOK
    Range("A2").Select
    ActiveCell.SpecialCells(xlLastCell).Select
 
    LastRow = ActiveCell.Row
 
    'COPY FROM CURRENT WORKSHEET AND PASTE INTO TARGET SHEET
    Range("A2:E" & LastRow).Copy TargetSht.Range("a17")
 
    ActiveWorkbook.Close False
 
    Application.ScreenUpdating = True
End Sub

and see what happens.

Of course I could be wrong!!


MTB