• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8946
  • Last Modified:

How to select an entire worksheet in VBA

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"
    Worksheets("UploadDollars").Activate
    Sheets("UploadDollars").Select -CODE DOES NOT SELECT THE ENTIRE WORKSHEET
    ActiveCell.Cells.Select -
    Selection.Copy
    Sheets("UploadDollarsPasted").Select
    ActiveCell.Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Names.Add Name:="UploadToDBDollarsWS", RefersToR1C1:= _
        "=UploadDollarsPasted!R1C1:R17641C17"
End Sub
0
ssmith94015
Asked:
ssmith94015
  • 4
  • 3
  • 2
2 Solutions
 
Jeroen RosinkCommented:
to select all cells:
Cells.Select

Jeroen
0
 
zorvek (Kevin Jones)ConsultantCommented:
Usually you don't want to select the entire worksheet. As soon as you do something with it you will likely bring Exxcel to it's knees. Use the UsedRange instead:

   Worksheets("Sheet1").UsedRange.Select

Kevin
0
 
Jeroen RosinkCommented:
Looking to your statement:
Sheets("UploadDollars").Select
that statement is intents only to select the sheet, not all the data.
If the sheet is not selected you might try this one:
Sheets("UploadDollars").Activate

If you want the all cells/usedrange being selected in your sheet then it would be this in your macro:
Public Sub UploadDollars()

    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "UploadDollarsPasted"
    Worksheets("UploadDollars").Activate
    Sheets("UploadDollars").Select '-CODE DOES NOT SELECT THE ENTIRE WORKSHEET
    ActiveSheet.UsedRange.Cells.Select '-
    Selection.Copy
    Sheets("UploadDollarsPasted").Select
    [A1].Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Names.Add Name:="UploadToDBDollarsWS", RefersToR1C1:= _
        "=UploadDollarsPasted!R1C1:R17641C17"
End Sub
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ssmith94015Author Commented:
Kevin, for various reasons, I do need to select the entire sheet.  Jeron, will try your suggestion.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Just be careful with that selection. If you try and do much with it other than setting some formatting or clearing it, Excel will likely go into convulsions. There are a lot of cells there to work with.

Kevin
0
 
Jeroen RosinkCommented:
In my last comment use instead of:
ActiveSheet.UsedRange.Cells.Select

Cells.Select
0
 
ssmith94015Author Commented:
Jeron, I only had to copy your code and was the simplest. Kevin, I just realized you basically had the same answer as well.
0
 
ssmith94015Author Commented:
Kevin, thanks for the hint, this worksheet has a tremendous amount of data on it to begin with, but so far copy/pasting the entire worksheet has worked.  All I want is the data and thankfully, nothing else, no formatting, etc.  It is raw data for an upload into ACCESS.  When i try to get all teh data from the origianl source worksheet, ACCESS has convulsions with converting 0 to text, so this was the only way around that issue.

Thank you both and since you each had the basic answer and suggestions, i am going to split the points a little so I hope that is ok and fair.
0
 
Jeroen RosinkCommented:
Thanks for the grade!
Splitting is always fair, certainly in this siutaution were I was led by initial Q tiel, selecting the cells and Kevin gave a sollution I also rather prefer.

Glad helping you.
Jeroen
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now