Re: Excel Macro Help

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 15:19:04 -0400

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

You have an active ***, then open another workbook. Do you then want to
put formulas on the original *** (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 *** in that workbook or it is
always going to be a *** named D0023. If not D0023, then is it the first
*** in that workbook. If not, then how to determine which ***?

Dim sName As String
Dim sh As Work***
Dim myFile As String
Set sh = Active***
    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 

-- 
Regards,
Tom Ogilvy
"Oli Oshiz" <oshiz2000@yahoo.com> wrote in message
news:uqJ7OLWgEHA.2908@TK2MSFTNGP10.phx.gbl...
> 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 Work***
> Dim myFile As String
> Set sh = Active***
>     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
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Quantcast