Using a count in a range name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a macro that changes values in wks2 in range b1:b100. Every time the
data changes I want to copy the values in wks1, always beginning on row 3 but
the column number changes with a count. I can't figure out how to modify the
following code:

col=5

Worksheet("wks2").Range("b1:b100").Select
Selection.Copy
Sheets("wks1").Select

****on the next line I want to use "col"****
Range("D3").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
One way to do it: create a named range called Col and store the value
5 to it, then add this code to your routine

Range("a3").Select 'use this instead of Range("D3").Select
ActiveCell.Offset(0, Mid(Names("Col").Value, 2,
Len(Names("Col").Value))).Select
Names("Col").Value = Evaluate(Names("Col").Value) +1

This starts at A3, offsets by the value of Col, then augments Col by
one for the next time it runs. The "ActiveCell.Offset" line contains
the MID function to strip the = sign out of Names("Col").Value.
 
Back
Top