Open Chosen Workbook & Copy Range

mundo7 · Apr 21st 2008

Hi

I've got a workbook which i want a macro on it, which needs to do the following:

1. Open a specific file (with a dialog box to choose the file if possible)
2. Refresh all of the data in the newly opened workbook (there are 90+ sheets with external data on them)
3. Go to the master sheet in this workbook and select a range and copy it.
4. Return to the original workbook and paste the data.
5. Close the workbook that we opened and save the changes.

The workbook i have is too big to post here, however if someone could show me the basics of the VBA that would be created from this, i think i could edit it to fir what i want to do.

Thanks

James

Robert B · Apr 22nd 2008

Re: Macro To Open Workbook & Copy Data

Hi

this might get you started

Code

Dim myFile As String, Wb1 As Workbook, myRange As Range


myFile = Application.GetOpenFilename
Workbooks.Open Filename:=myFile
Set Wb1 = ActiveWorkbook
Sheets("Index").Select ' Index being the sheet identifier, either name or number
Set myRange = Range("An", "Zi")  ' enter your range 


myRange.Copy Destination:=Range("Bn") enter your destination
Wb1.Save
Wb1.Close

Display More

you will probably have to define other variables, when you open your workbook the external links will probably refresh automatically, unless you have that option inhibited.

Robert

Open Chosen Workbook & Copy Range

Participate now!

Share