I used the macro record to get the basics of what I am trying to do. Using Automation, from ACCESS I need to run a process in Excel that creates a blank worksheet called "UploadDollarsPasted". Then I need to copy all the data from the existing worksheet called "UploadDollars" and paste special values into the "UploadDollarsPasted" worksheet and then create a named range. I can create the new worksheet, but when I go back to the UploadDollars worksheet, I can't seem to figure out how to select the entire worksheet so I can paste/special/values into the UploadDollarsPasted worksheet.
Public Sub UploadDollars()
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "UploadDollarsPasted"
Sheets("UploadDollars").Select -CODE DOES NOT SELECT THE ENTIRE WORKSHEET
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="UploadToDBDollarsWS", RefersToR1C1:= _