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
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