Use a variable based on a cell to open a wookbook.

[hbkid11]

What i'm trying to do is have a macro that refers to a file location that is entered in Cell A2 of a specified sheet and stores that file location as a variable so that i can use the variable to open the file and copy data from the file.

Here's what i'm aiming to do as i currently have it without a variable. (this is not my full code but this short sample should be enough to show what i'm trying to do)

Workbooks.Open Filename:= _
"C:\Users\name\Desktop\Client\LIVE.csv" (want this to be a variable based on a file location that was entered in cell A2 of a seperate workbook)
Range("B10").Select
Selection.Copy
Windows("Book1").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("LIVE.csv").Activate (want this to be based on the variable name)
Range("B11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Roderick_E]

Dim sheettopen as string
sheettopen = range("A2")
[COLOR=#333333]Workbooks.Open Filename:=[/COLOR]sheettopen

Of course this means A2 will need to be the entire pathway and file extension

 

[hbkid11]

Dim sheettopen as string
sheettopen = range("A2")
[COLOR=#333333]Workbooks.Open Filename:=[/COLOR]sheettopen

Of course this means A2 will need to be the entire pathway and file extension

getting an error at the line hightlighted below


Sub test()


Dim sheettopen As String
sheettopen = Workbooks("book1.xlsx").Sheets("sheet1").Range("A2")


Workbooks.Open Filename:=sheettopen
Range("B10").Select
Selection.Copy
Windows("Book2").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(sheettopen).Activate
Range("B11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book2").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


End Sub

 

[Roderick_E]

Sorry, I thought you would adjust this line. Best way to do it would be to dim shortname as string. See all my red text.

Sub test()

[COLOR=#333333]Dim sheettopen As String
[/COLOR][COLOR=#ff0000]Dim shortname as string[/COLOR]
[COLOR=#333333]sheettopen = Workbooks("book1.xlsx").Sheets("sheet1").Range("A2")[/COLOR]


[COLOR=#333333]Workbooks.Open Filename:=sheettopen
[/COLOR][COLOR=#ff0000]shortname = activeworkbook.name[/COLOR]
[COLOR=#333333]Range("B10").Select[/COLOR]
[COLOR=#333333]Selection.Copy[/COLOR]
[COLOR=#333333]Windows("Book2").Activate[/COLOR]
[COLOR=#333333]Range("B2").Select[/COLOR]
[COLOR=#333333]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#333333]:=False, Transpose:=False[/COLOR]
[COLOR=#ff0000]Windows(shortname).Activate[/COLOR]
[COLOR=#333333]Range("B11").Select[/COLOR]
[COLOR=#333333]Application.CutCopyMode = False[/COLOR]
[COLOR=#333333]Selection.Copy[/COLOR]
[COLOR=#333333]Windows("Book2").Activate[/COLOR]
[COLOR=#333333]Range("B3").Select[/COLOR]
[COLOR=#333333]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#333333]:=False, Transpose:=False[/COLOR]


[COLOR=#333333]End Sub[/COLOR]

 

[hbkid11]

Sorry, I thought you would adjust this line. Best way to do it would be to dim shortname as string. See all my red text.

Sub test()

[COLOR=#333333]Dim sheettopen As String
[/COLOR][COLOR=#ff0000]Dim shortname as string[/COLOR]
[COLOR=#333333]sheettopen = Workbooks("book1.xlsx").Sheets("sheet1").Range("A2")[/COLOR]


[COLOR=#333333]Workbooks.Open Filename:=sheettopen
[/COLOR][COLOR=#ff0000]shortname = activeworkbook.name[/COLOR]
[COLOR=#333333]Range("B10").Select[/COLOR]
[COLOR=#333333]Selection.Copy[/COLOR]
[COLOR=#333333]Windows("Book2").Activate[/COLOR]
[COLOR=#333333]Range("B2").Select[/COLOR]
[COLOR=#333333]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#333333]:=False, Transpose:=False[/COLOR]
[COLOR=#ff0000]Windows(shortname).Activate[/COLOR]
[COLOR=#333333]Range("B11").Select[/COLOR]
[COLOR=#333333]Application.CutCopyMode = False[/COLOR]
[COLOR=#333333]Selection.Copy[/COLOR]
[COLOR=#333333]Windows("Book2").Activate[/COLOR]
[COLOR=#333333]Range("B3").Select[/COLOR]
[COLOR=#333333]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#333333]:=False, Transpose:=False[/COLOR]


[COLOR=#333333]End Sub[/COLOR]

this worked great, would you mind explaining what the new lines of code are doing?

 

[Roderick_E]

this worked great, would you mind explaining what the new lines of code are doing?

Glad to hear.
shortname = activeworkbook.name
simply names "shortname" the name of the activeworkbook, without the full pathway. We know it's the same as the workbook you just opened using the pathway in A2.
Hope that helps.

 

[hbkid11]

Glad to hear.
shortname = activeworkbook.name
simply names "shortname" the name of the activeworkbook, without the full pathway. We know it's the same as the workbook you just opened using the pathway in A2.
Hope that helps.

Great, thank you!