Excel Macro Help

[Oli Oshiz]

I am trying to create a macro that allows me to open up another excel
file and then specify which tab on the opened work book I want to pull
information from.

Does anyone know how to do this?

Thanks in advance for your help.

 

[Tom Ogilvy]

vVal = Activeworkbooks.Worksheets("Sheet9").Range("B10").Value

 

[Tom Ogilvy]

Whoops, typo:
ActiveWorkbooks should be ActiveWorkbook

 

[Oli Oshiz]

Thanks Tom,

Here is what I end up with but it does not work. What am i doing wrong.

Sub ProjectMacro()
'
' ProjectMacro Macro
' Macro recorded 8/5/2004 by Oli_Oshiz
'

'
Dim sName As String
Dim sh As Worksheet
Dim myFile As String
Set sh = ActiveSheet
myFile = Application.GetOpenFilename("Excel Files, *.xls")
vVal = Activeworkbooks.Worksheets("Sheet9").Range("B10").Value
Workbooks.Open myFile
sName = ActiveWorkbook.Name
sh.Parent.Activate
sh.Activate

Range("A2").Select
ActiveCell.FormulaR1C1 = "='[sName]sh'!R4C4"
Range("B2").Select
ActiveCell.FormulaR1C1 = "='[& sName &_]D0023'!R6C4"
Range("C2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R6C10"
Range("D2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R6C14"
Range("E2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R7C11"
Range("F2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R8C4"
Range("G2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R8C11"
Range("H2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R8C14"
Range("I2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R7C14"
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

 

[Tom Ogilvy]

It looks like you have thrown a bunch of stuff together without
understanding what you are doing.

You have an active sheet, then open another workbook. Do you then want to
put formulas on the original sheet (reference is held in object sh) that
refers to the newly opened workbook? If so, see below, but it is unclear
if you want to dynamically determine a sheet in that workbook or it is
always going to be a sheet named D0023. If not D0023, then is it the first
sheet in that workbook. If not, then how to determine which sheet?


Dim sName As String
Dim sh As Worksheet
Dim myFile As String
Set sh = ActiveSheet
myFile = Application.GetOpenFilename("Excel Files, *.xls")
Workbooks.Open myFile
sName = ActiveWorkbook.Name
sh.Parent.Activate
sh.Activate

Range("A2").FormulaR1C1 = "='[" & sName & "]D0023'!R4C4"

' more of the same

Range(Range("A2"), Range("A2").End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

 

[Oli Oshiz]

Hi Tom,

I do have an active sheet, that opens another workbook. I then want to
put formulas on the original sheet that refers to the newly opened
workbook? If possible, determine the sheet in the opened workbook.

Thanks.

 

[Tom Ogilvy]

If it the opened workbook only has one sheet, then you can determine the
name with

Dim myFile As String
Set sh = ActiveSheet
myFile = Application.GetOpenFilename("Excel Files, *.xls")
Workbooks.Open myFile
sShName = ActiveWorkbook.Worksheets(1).Name
sName = ActiveWorkbook.Name
sh.Parent.Activate
sh.Activate

Range("A2").FormulaR1C1 = "='[" & sName & "]" & sShName & _
"'!R4C4"

' more of the same

Range(Range("A2"), Range("A2").End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

 

[Oli Oshiz]

Hi Tom,

The excel file has multiple sheets with data in multiple cells (however,
the cell locations stay the same for all sheets).

Thanks.