Redefining Ranges

  • Thread starter Thread starter Carl Johnson
  • Start date Start date
C

Carl Johnson

Thanks Geoff and Dave, the code you gave me works great. Just one quick
question.
Dave in the code you gave me there was a line that says

myAddr = Worksheets("Sheet1").Range("Customer").address

Now when I change the name of the worksheet to something other than sheet1 I
get a debug error. Is there a way to correct this. Again Thank you.
 
This is just getting the address of the range named Customer. It doesn't matter
what sheet you use (you did say all sheets were identical????).

So just point at a sheet that has that range name.

If the user can change the names of the worksheets and you want to code around
it, you can use the codename of the worksheet.

the codename is the name that's not in parentheses when you look at the project
explorer in the VBE.

Sheet1 (MySheetName)

(Mysheetname would be visible to the user in Excel.)

then

myAddr = sheet1.range("customer".address

Then the user can rename whatever they want--just don't let them delete it.

(And worse comes to worse, just plop the range's address into that line:

myaddr = "A1:a99"
or
myaddr = "a1:a99,b3:b33,c7:d16"

for example.
 
Thanks Dave that seems to take care of that. Now if I could trouble you with
one more question. I guess by now you have figured out that I am pretty new
at this (the courses I to in school did not teach much coding). In your
first response you said that it would be better to edit/copy this sheet than
to copy/paste I bow to your expertise. Now when I edit /copy I want the new
sheet to clear it's previous contents. What code would do this?
 
You'll have to define the range of cells to get cleared.

Is it still "customer" or is it this and more (or less)?

If it's exactly the same cells, you could do:

Option Explicit
Sub testme03()

Dim wks As Worksheet
Dim newWks As Worksheet
Dim myAddr As String

Set wks = Worksheets("sheet1")

wks.Copy after:=wks
Set newWks = ActiveSheet
With newWks
.Name = "Nice Name Here"
.Range("customer").ClearContents
End With

End Sub

The "wks.copy after:=wks" does the copy of the worksheet. Since you copied the
whole worksheet, the range name came with it. So we can use it in the
..clearcontents line.

(And you'll have to change the .name to a nice unique name--else the second time
you run the code, it'll blow up real good.)
 
Back
Top