Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

open excel file and copy data to another file

4 views
Skip to first unread message

Norvascom

unread,
Jan 7, 2010, 12:14:02 PM1/7/10
to
I would like to create a macro that would do the following:

1- Open a closed Excel file located on my drive named "A" and select
the sheet named "Sheet1"
2- Copy all data from this sheet starting on cell A1 to the already
opened master Excel file named "B" on sheet named "Sheet2" cell A1.

Thanks in advance for your help.

Gord Dibben

unread,
Jan 7, 2010, 12:32:39 PM1/7/10
to
Try the macro recorder.


Gord Dibben MS Excel MVP

On Thu, 7 Jan 2010 09:14:02 -0800 (PST), Norvascom <norv...@gmail.com>
wrote:

Norvascom

unread,
Jan 7, 2010, 12:50:08 PM1/7/10
to
On Jan 7, 12:32 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Try the macro recorder.
>
> Gord Dibben  MS Excel MVP
>
> On Thu, 7 Jan 2010 09:14:02 -0800 (PST), Norvascom <norvas...@gmail.com>

> wrote:
>
>
>
> >I would like to create a macro that would do the following:
>
> >1- Open a closed Excel file located on my drive named "A" and select
> >the sheet named "Sheet1"
> >2- Copy all data from this sheet starting on cell A1 to the already
> >opened master Excel file named "B" on sheet named "Sheet2" cell A1.
>
> >Thanks in advance for your help.- Hide quoted text -
>
> - Show quoted text -

I does not work because the file to be opened is not always located in
the same place on the drive.

Ryan H

unread,
Jan 7, 2010, 3:21:01 PM1/7/10
to
This macro will prompt the user to select where the file is, open it, copy
the data on Sheet1 to the master workbook Sheet2. I assume you will want to
call this sub from a command button in your master excel sheet. Hope this
helps! If so, let me know, click "YES" below.

Sub ImportSheet()

Dim wbkData As Variant

' select file with data to be opened
wbkData = Application.GetOpenFilename

' open the workbook
If wbkData = False Then
MsgBox "You didn't select a workbook.", vbInformation
Else
Workbooks.Open wbkData
Set wbkData = ActiveWorkbook
End If

' copy data to master workbook
ThisWorkbook.Sheets("Sheet2").Cells = wbkData.Sheets("Sheet1").Cells

' close the workbook without saving
wbkData.Close

End Sub
--
Cheers,
Ryan


"Norvascom" wrote:

> .
>

Gord Dibben

unread,
Jan 7, 2010, 5:55:02 PM1/7/10
to
Would have been handy if you mentioned that first time around.

But I see Ryan has posted something for you.


Gord

On Thu, 7 Jan 2010 09:50:08 -0800 (PST), Norvascom <norv...@gmail.com>
wrote:

>I does not work because the file to be opened is not always located in

0 new messages