Copy Data From Another Workbook thats in the same folder as the openwork book

I have a macro written to copy data from a different workbook and paste transpose it to the one i have open.
Is there a way to change the reference to the file i want to copy data from to change based on the name of the file i have open or the working directory im in.

all the files are name by a 5 digit number then GA then 001
so the name would look like this and the # would change based on the project.

#####GA001.xlsx name of file i get the data
#####GG001.xlsm name of file the data is being pasted

i guess to explain it better I want the marco to look at the name of the #####GG001.xlsm file that the marco runs in to place the appropiate numbers in the #'s.

is this possible? the files will be located in the same folder. I dont know if it can pick up the directory im in

here is the code

Sub Copy()
Dim WksFrom As Workbook
Dim WksTo As Workbook
Dim sheetFrom As Worksheet
Dim sheetTo As Worksheet
Set WksFrom = Workbooks.Open("I:\Projects\STA\82927_241_3.64\82927\roadway\spreadsheets\82927GA001.xlsx")
Set WksTo = Workbooks("82927GG001.xlsm")
Set sheetFrom = WksFrom.Worksheets("PAVEMENT_CALCS")
Set sheetTo = WksTo.Worksheets("Blank With Part (2 Columns)")

sheetFrom.Range("K21:AB21").Copy
sheetTo.Range("AJ36").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
sheetFrom.Range("K61:AB61").Copy
sheetTo.Range("AK36").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
sheetFrom.Range("K22:AB22").Copy
sheetTo.Range("AL36").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False

sheetFrom.Range("K64:AB64").Copy
sheetTo.Range("AJ54").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
sheetFrom.Range("K104:AB104").Copy
sheetTo.Range("AK54").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
sheetFrom.Range("K65:AB65").Copy
sheetTo.Range("AL54").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False

sheetFrom.Range("K107:AB107").Copy
sheetTo.Range("AJ72").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
sheetFrom.Range("K147:AB147").Copy
sheetTo.Range("AK72").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
sheetFrom.Range("K108:AB108").Copy
sheetTo.Range("AL72").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False

sheetFrom.Range("K150:AB150").Copy
sheetTo.Range("AJ90").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
sheetFrom.Range("K190:AB190").Copy
sheetTo.Range("AK90").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
sheetFrom.Range("K151:AB151").Copy
sheetTo.Range("AL90").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False

WksFrom.Close

Range("AJ36:AL1000").Select
Set aCell = Range("AJ36")
Selection.Sort key1:=aCell, order1:=xlAscending



End Sub

This part of the Code is what i want to change the two files are located in the same folder the only thing that would change from project to project would be the first 5 digit number of the file name. the location path will change from project to project. I want to add the marco to the template file so for each project I dont have to go into the code and change the path. I think theres a way to pick up the working directory of the file and then search look for a file named like this #####GA001.xlsx.

Set WksFrom = Workbooks.Open("I:\Projects\STA\82927_241_3.64\82927\roadway\spreadsheets\82927GA001.xlsx")
Set WksTo = Workbooks("82927GG001.xlsm")

If you need more explaination i can try to explain it better.

I need this part of the code changed

Set WksFrom = Workbooks.Open("I:\Projects\STA\82927_241_3.64\82927\roadway\spreadsheets\82927GA001.xlsx")
Set WksTo = Workbooks("82927GG001.xlsm")

to make it dynamic so when i go to a different project i dont need to change the marco every time. I have multiple projects and for each project I will have the two files that the code above calls out. But for each project that 5 digit number (82927 from the code above) at the start of the file name will change for both of the files. The path to the 2 files will be the same but that changes for each project.

so I would put the marco in the templete file so when i start a new project i will save that file to its location and I want the code to be able to known where to look based off the location of the file i have open (the #####GG001.xlsm file) that contains this macro.

does that make sense?

this is what i did i used the activeworkbook.fullname and activeworkbook.name to set the path to the open workbook then used the replace command to find the file i want to open.

pretty cool

Dim WksFromFileName As String
Dim WksToFileName As String
Dim WksFrom As Workbook
Dim WksTo As Workbook
Dim sheetFrom As Worksheet
Dim sheetTo As Worksheet

WksFromFileName = Replace(ActiveWorkbook.FullName, "GG001.xlsm", "GA001.xlsx")
WksToFileName = ActiveWorkbook.Name

Set WksFrom = Workbooks.Open(WksFromFileName)
Set WksTo = Workbooks(WksToFileName)
Set sheetFrom = WksFrom.Worksheets("PAVEMENT_CALCS")
Set sheetTo = WksTo.Worksheets("Blank With Part (2 Columns)")