open file specified in a Cell location

[Aeneren]

Hello All,

Anyone tell me how to have the coding below open a specified file using the file location specified in a cell.

Sub Macro2()

'          Would like to have the code Target Sheet1 C6 and use that as its Target File
    Workbooks.Open FileName:="C:\Users\Brian\Desktop\Excell test folder\1903\4-29-2012-1903-Brian.xlsm"
    Sheets("Airway Drawer").Select
    Range("E3:M8").Select
    Selection.Copy
    Windows("Inventory Pull Sample.xlsm").Activate
    Sheets("Sheet2").Select
    Range("E3").Select
    ActiveSheet.Paste

    Windows("4-29-2012-1903-Brian.xlsm").Activate
    Sheets("Portable Suction").Select
    Range("E3:M7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Inventory Pull Sample.xlsm").Activate
    Range("E13").Select
    ActiveSheet.Paste

 
    Windows("4-29-2012-1903-Brian.xlsm").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

Thanks
Aeneren

[swoop99]

You'll need the entire path in the cell unless you can get the folder information from another source. So, if C6 = C:\Users\Brian\Desktop\Excell test folder\1903\4-29-2012-1903-Brian.xlsm....

dim path as string
path = range("sheet1!C6").value
workbooks.open (path)

[Aeneren]

Hello,

--swoop99 thanks for your reply,
place your code in and now am getting:

Run-time error '9'
Subscript out of range

Also the code is now copying the Formula from the sheet's instead of the results any idea.

Sub Macro2()

'          Would like to have the code Target Sheet1 C6 and use that as its Target File
'    Workbooks.Open FileName:="C:\Users\Brian\Desktop\Excell test folder\1903\4-29-2012-1903-Brian.xlsm"
    
    Dim path As String
        path = Range("sheet1!C6").Value
        Workbooks.Open (path)
    
  '  Workbooks.Open FileName:="C:\Users\Brian\Desktop\Excell test folder\1903\4-29-2012-1903-Brian.xlsm"
    Sheets("Airway Drawer").Select
    Range("E3:M8").Select
    Selection.Copy
    Windows("Inventory Pull Sample.xlsm").Activate
    Sheets("Sheet2").Select
    Range("E3:M8").Select
    ActiveSheet.Paste

   ' Windows("4-29-2012-1903-Brian.xlsm").Activate
    Sheets("Portable Suction").Select    this line is were it Debugs to 
    Range("E3:M7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Inventory Pull Sample.xlsm").Activate
    Range("E13:M17").Select
    ActiveSheet.Paste

   ' Windows("4-29-2012-1903-Brian.xlsm").Activate
    Sheets("IV Warmer").Select
    Range("E3:M4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Inventory Pull Sample.xlsm").Activate
    Range("E22:M23").Select
    ActiveSheet.Paste

   ' Windows("4-29-2012-1903-Brian.xlsm").Activate
    Sheets("Narc's").Select
    Range("E3:M23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Inventory Pull Sample.xlsm").Activate
    Range("E28:M48").Select
    ActiveSheet.Paste
 
   ' Windows("4-29-2012-1903-Brian.xlsm").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

Thanks
Aeneren

[swoop99]

See this page: http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm for advice on using something other than copy and paste (besides having more control, it's faster to avoid use of the clipboard).

Also, the reason it's bombing out is cos you have rem'd out the line that switches back to the workbook you just opened, therefore when you try to select sheet "Portable Suction" it can't find it. Is the name of the worksheet you open from C6 always the same length? If so, you can use something like:

path = right$(path, 25)
windows(path).activate

If the filename varies in length then you'll have to parse it for slashes to extrapolate the workbook name to switch to.

[Aeneren]

Hello all,

--swoop99
Sorry for the confusion, ive been busy at work, and its taken this long to get back to you.
The below was how i first targeted the file before your Cell Target code.

'  Workbooks.Open FileName:="C:\Users\Brian\Desktop\Excell test folder\1903\4-29-2012-1903-Brian.xlsm"

And this bit was how i activated it between sections, with the copy and paste

' Windows("4-29-2012-1903-Brian.xlsm").Activate

This is the code as it is now that im working with.

Sub Macro2()

    Dim path As String
        path = Range("sheet1!C6").Value
        Workbooks.Open (path)
  
    Sheets("Airway Drawer").Select
    Range("E3:M8").Select
    Selection.Copy
    Windows("Inventory Pull Sample.xlsm").Activate
    Sheets("Sheet2").Select
    Range("E3:M8").Select
    ActiveSheet.Paste

    Sheets("Portable Suction").Select  'debugs here
    Range("E3:M7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Inventory Pull Sample.xlsm").Activate
    Range("E13:M17").Select
    ActiveSheet.Paste

    Sheets("IV Warmer").Select
    Range("E3:M4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Inventory Pull Sample.xlsm").Activate
    Range("E22:M23").Select
    ActiveSheet.Paste

    Sheets("Narc's").Select
    Range("E3:M23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Inventory Pull Sample.xlsm").Activate
    Range("E28:M48").Select
    ActiveSheet.Paste
 
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

And it still Debugs to the begining of the Second section, looked at the link you gave and gonna try some it.
If your up to it please feel free to give me any pointers.

Thanks
Aeneren

[nilem]

Hi Aeneren.Try it

Sub Macro3()
Dim wsh As Worksheet, arr, i&
Set wsh = ThisWorkbook.Sheets("Sheet2")
arr = Array("Airway Drawer", "Portable Suction", "IV Warmer", "Narc's", _
            "E3:M8", "E3:M7", "E3:M4", "E3:M23", _
            "E3", "E13", "E22", "E28")
On Error Resume Next
With GetObject(Range("Sheet1!C6").Value)
'or
'With Workbooks.Open(Range("Sheet1!C6").Value)
    For i = 0 To 3
        .Sheets(arr(i)).Range(arr(i + 4)).Copy wsh.Range(arr(i + 8))
        If Err Then Err.Clear: MsgBox "Not defined sheet " & arr(i), 64
    Next i
    .Close 0
End With
Application.CutCopyMode = False
End Sub

[Aeneren]

Hello Nilem

Thank You Works great in the test books.
One more tweak please, the code is pasting the Formulas instead of the data

Aeneren

[nilem]

You only need the values? Try

Sub Macro4()
Dim wsh As Worksheet, arr, i&
Set wsh = ThisWorkbook.Sheets("Sheet2")
arr = Array("Airway Drawer", "Portable Suction", "IV Warmer", "Narc's", _
            "E3:M8", "E3:M7", "E3:M4", "E3:M23", _
            "E3:M8", "E13:M17", "E22:M23", "E28:M48")
On Error Resume Next
With GetObject(Range("Sheet1!C6").Value)
    For i = 0 To 3
        wsh.Range(arr(i + 8)).Value = .Sheets(arr(i)).Range(arr(i + 4)).Value
        If Err Then Err.Clear: MsgBox "Not defined sheet " & arr(i), 64
    Next i
    .Close 0
End With
End Sub

[Aeneren]

Hello nilem,

Bow, Bow, Bow, Bow, Bow, Bow, Bow

Thank You My Friend now to place this in the full workbook of over 30+ sheets



Aeneren

[Aeneren]

Hello again,

--Nilem (or anyone who can please)
I've been trying to attach your code to the entire WB and have been having trouble trying to understand some of the code, could you possibly go line by line and Define what each line is doing for me.

Its probally something simple but this Novice is still learning

So i could posiblly track down why i have been getting a error of

Not Defined Page

Now i did have to move pages around and have been adding several more pages and fields to copy paste.

Sub Macro4()
Dim wsh As Worksheet, arr, i&
Set wsh = ThisWorkbook.Sheets("Sheet2")
arr = Array("Airway Drawer", "Portable Suction", "IV Warmer", "Narc's", _
            "E3:M8", "E3:M7", "E3:M4", "E3:M23", _
            "E3:M8", "E13:M17", "E22:M23", "E28:M48")
On Error Resume Next
With GetObject(Range("Sheet1!C6").Value)
    For i = 0 To 3
        wsh.Range(arr(i + 8)).Value = .Sheets(arr(i)).Range(arr(i + 4)).Value
        If Err Then Err.Clear: MsgBox "Not defined sheet " & arr(i), 64
    Next i
    .Close 0
End With
End Sub

Above is the code i am using and have started to modify.


Thanks
Aeneren