• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9912
  • 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
Sandra Smith
Asked:
Sandra Smith
  • 4
  • 3
  • 2
2 Solutions
 
Jeroen RosinkSoftware testing consultantCommented:
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 RosinkSoftware testing consultantCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Sandra SmithRetiredAuthor 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 RosinkSoftware testing consultantCommented:
In my last comment use instead of:
ActiveSheet.UsedRange.Cells.Select

Cells.Select
0
 
Sandra SmithRetiredAuthor 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
 
Sandra SmithRetiredAuthor 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 RosinkSoftware testing consultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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