I would like to have a cell in Book1 that I can input a file nam
into...Cell A1.

When the specific file name (C:\Book2.xls) is placed in A1, the macr
should be activated by a command button, open 'Book2', copy cell
A1:Z100, and paste them into Book 1 starting at cell B1.


That's a mouth full.

Originally, I wanted to record a macro that clicks the open file butto
and lets me select the file to open, and then have the macro continu
with the rest of its recorded copy and paste. But, the mix betwee
macro automation and user input does not seem viable.

Thank you

--
Message posted from http://www.ExcelForum.com

I would like to be able to specify a file name because it will always b
a different file being copied.

What about avoiding the Visual Basic approach to opening the file an
instead...

Assign each cell in Book1 a specific equation that pulls the data ou
of Book2. So, B1= Book2(A1)....B2=Book2(A2)


The only issue is how to change the name of Book2 to ensure that th
data is pulled from the correct file.

Anyone know how to make a macro so I can store the root file path i
the macro and simply type the file name ("Book2") into cell C1 an
click a Command Button that changes the equations in Book1. Th
equations would be changed to reference the appropriate file

--
Message posted from http://www.ExcelForum.com

I have been reading pages on the internet to figure this out.

What about having a message box that opens with Workbook1 and asks th
user what the name of "Workbook2" will be. When the user inputs th
name and clicks "OK", the macro will update the source in each equatio
of Book1-Sheet1.

I have found this code, but how could I manipulate it to do what
want?

Sub Auto_Open()
YesNo = MsgBox
Select Case YesNo
Case vbYes
'Insert your code here if Yes is clicked
Case vbNo
'Insert your code here if No is clicked
End Selec

--
Message posted from http://www.ExcelForum.com

Set oWB = Activeworkbook
Workbooks(Range("A1").Open
Worksheets(1).Range("A1:Z100").Copy
Destination:=oWB.ACtivesheet.Range("B1")
Activeworkbook.Close
oWB.ACtivate

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"sowetoddid " wrote in message
...
I would like to have a cell in Book1 that I can input a file name
into...Cell A1.

When the specific file name (C:\Book2.xls) is placed in A1, the macro
should be activated by a command button, open 'Book2', copy cells
A1:Z100, and paste them into Book 1 starting at cell B1.


That's a mouth full.

Originally, I wanted to record a macro that clicks the open file button
and lets me select the file to open, and then have the macro continue
with the rest of its recorded copy and paste. But, the mix between
macro automation and user input does not seem viable.

Thank you.


---
Message posted from http://www.ExcelForum.com/

I wouldn't really put the name of the file in a cell -- raises the risk
of a typo...

Use something like:

Option Explicit

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub

Note that you did not specify which worksheet within the respective
workbooks were the source/destination. I made some assumptions. <g

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , sowetoddid
says...
I would like to have a cell in Book1 that I can input a file name
into...Cell A1.

When the specific file name (C:\Book2.xls) is placed in A1, the macro
should be activated by a command button, open 'Book2', copy cells
A1:Z100, and paste them into Book 1 starting at cell B1.


That's a mouth full.

Originally, I wanted to record a macro that clicks the open file button
and lets me select the file to open, and then have the macro continue
with the rest of its recorded copy and paste. But, the mix between
macro automation and user input does not seem viable.

Thank you.


---
Message posted from http://www.ExcelForum.com/

To understand what this code is doing, I am testing it out...this wa
pasted into a VB module...

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub

...nothing happened


I am using the following naming conventions...

Workbook 1 = "Master File"
Sheet 1 = "Music"

Workbook 2 = "121 3-4" (this will change to "060 3-4", "040 3-5", etc
But is will always maintain the same format)

Sheet 1 = "Output

--
Message posted from http://www.ExcelForum.com

I am a beginner at using VB. The parts I don't quite understand are...

1. How is this code determining the name of the Workbook I want t
copy data from?

2. How is this code finding the workbook? Doesn't a path need to b
specified?



Thanks

--
Message posted from http://www.ExcelForum.com

Trying to learn this,
I should input my specific names as...

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("music").Range("b1").PasteSpecia l _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub


And in the other example...

Set oWB = Activeworkbook
"Master File"((Range("A1")).Open
Music.Range("A1:Z100").Copy
Destination:=oWB.ACtivesheet.Range("B1")
Activeworkbook.Close
oWB.ACtivate


Please correct me. Also. Any suggestions for a good source o
learning this? I have seen some downloadable help a
Add-ins.com.....Any experience with that?

Many thanks

--
Message posted from http://www.ExcelForum.com

"sowetoddid " wrote in message
...
I am a beginner at using VB. The parts I don't quite understand are...

1. How is this code determining the name of the Workbook I want to
copy data from?


The GetOp[enFilename is used to select a file, and this is then set to the
variable OpenedWB when it is opened. It is data copied frfom OpenedWB so
that is how it knows.


2. How is this code finding the workbook? Doesn't a path need to be
specified?


No because it opens a file browser for you to go and get it. This returns
the path and filename.

Wow! Bob, that is incredible. I saw it start working, but it caught
problem toward the end.

This error came up....

"Runtime error '1004'"
"PasteSpecial method of range class failed"


What exactly does that mean

--
Message posted from http://www.ExcelForum.com

In article , sowetoddid
says...

..nothing happened

What does that mean?

Also, please format any code for readability.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , sowetoddid
says...
To understand what this code is doing, I am testing it out...this was
pasted into a VB module...

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub

..nothing happened


I am using the following naming conventions...

Workbook 1 = "Master File"
Sheet 1 = "Music"

Workbook 2 = "121 3-4" (this will change to "060 3-4", "040 3-5", etc.
But is will always maintain the same format)

Sheet 1 = "Output"


---
Message posted from http://www.ExcelForum.com/