PDA

View Full Version : Updating Range



lawrenb
08-03-2006, 05:39 PM
I have a routine that looks for new customers and adds them to a customer table. After the names are added, I want to update the customer table "Range Name" to include the new customers.

The routine deletes the Range Name, Then Starts with the left corner cell (C1), ends right, ends down........to find the new customer table range, Then is is to Name the range.

I cannot get the routine to work, can someone take a look.
Note I am writing the routine in Sheet "Billings" but trying to update the range in Sheet "Reference" WHY am I have trouble with the code ???

With Worksheets("CustomerBillings").Range("M2")
RCount = Range(.Offset(0, 0), .End(xlDown)).Count
End With

ActiveWorkbook.Names("Range1").Delete

With Worksheets("Reference").Range("c1")
Range(.Offset(0, 0), .Offset(0, 3).End(xlDown)).Name = "Range1"
End With

With Worksheets("Reference").Range("Range1").Select
Selection.Sort Key1:=Range("c1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End With

With Worksheets("Reference").Range("A1").Select
End With

With Worksheets("CustomerBillings").Range("N2")
.Formula = "=VLOOKUP(M2,Range1,2)"
.Copy
Range(.Offset(0, 0), .Offset(RCount - 1, 0)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
End With

End Sub

Edited 04-Aug-06 by geekgirlau. Reason: insert vba tags

lucas
08-03-2006, 07:20 PM
Just a suggestion, could you use a sheet change event to make the range "Range1" dynamic. Anything added to or removed changes the range.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim n As Long

n = Sheets("Lists").Range("A65536").End(xlUp).Row
ThisWorkbook.Names.Add Name:="Range1", RefersTo:=Sheets("Lists").Range("A1:A" & n)

End Sub

geekgirlau
08-03-2006, 08:04 PM
Don't forget to use the VBA tags when posting code - it makes it much easier to read (highlight the code text, then click on the "VBA" button).

Bob Phillips
08-04-2006, 02:56 AM
See if this works for you



Dim rng As Range
With Worksheets("Reference").Range("c1")
Set rng = .Cells(1, 1).Resize(.Offset(0, 3).End(xlDown).Row, 3)
rng.Name = "Range1"
End With