Open workbook and set worksheet

[drop05]

Hi, im trying to set a workbook and worksheet in vba so i dont have to keep referencing the sheet name in my code
here is my code
is there a way to make it where i dont have to keep typing "sheet1"

Sub Get_data()

'allow the user to select an excel file

Dim FileToOpen As Variant
Dim FileToPaste As Variant
Dim copy_wb As Workbook
Dim paste_wb As Workbook
Dim Total As Double
Dim Yes As String
Dim Blank As String

Application.ScreenUpdating = False

Blank = "Blank"
Yes = "Yes"
No = "No"


'open workbook
FileToOpen = Application.GetOpenFilename(Title:="Browse for File To Copy Data", FileFilter:="All Files(*.*),*.*")
FileToPaste = Application.GetOpenFilename(Title:="Browse for Paste File", FileFilter:="All Files(*.*),*.*")

'Open the files from client to copy from then the admn tool file to paste to

If FileToOpen <> False Then
Set copy_wb = Application.Workbooks.Open(FileToOpen)
Set paste_wb = Application.Workbooks.Open(FileToPaste)

copy_wb.Worksheets("Sheet1 (Pg 2)").Range("D13:D14").Copy
paste_wb.Worksheets("Sheet C (IS)").Range("D5:D6").PasteSpecial Paste:=xlPasteValues

copy_wb.Worksheets("Sheet1 (Pg 2)").Range("D16:D21").Copy
paste_wb.Worksheets("Sheet C (IS)").Range("D8:D13").PasteSpecial Paste:=xlPasteValues

See how i am doing copy_wb.worksheets("Sheet1 (Pg 2)"). . .
is there a way to make it shorter or set it to something then do the .Range()

thank you!

 

[Dermot]

Try this

Dim S
Set S=Sheets("Sheet1 (Pg 2)")
copy_wb.Worksheets(S.Range("D13:D14").Copy

etc