MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

dynamic macros and named ranges questions

  Asked By: Addie    Date: Aug 18    Category: MS Office    Views: 2353

I think this is supposed to be a simple one, but I've looked through a lot of
vba examples
and must be overlooking the obvious. I've been lurking in this group for a while
hoping to
find an answer or clue.

I have a workbook with multiple worksheets that I update daily. I have oodles of
ranges and macros making this task less time-consuming and keeping me from
making too
many mistakes. I have quite a few macros that are as simple as this:

Sub PasteRMRegFunc()
Selection.Formula = "=VLOOKUP(D1448,RMRegArray0710,8,FALSE)"
End Sub

If my selected cell is on row 2038, then I manually type 2038 in place of the
1448. Then I
manually replace 0710 to yesterday's date of 0718.

How can I modify my macro to do the above for me so the macro sets the formula
of the
selected cell to one that is good to go?

I'd like a macro that would create a named range for me based on the selected
cells. The
first part of the named range is always the same, the second part is yesterday's
date (minus
the yyyy). Is this possible with VBA without user input or action (Other than
selecting a cell/
range and activating the macro, of course)?

Is it possible to create a new workbook that lists the name of every named range
in the
active workbook?

Last question. Can I use VBA to copy an entire named range to a different
worksheet or
workbook with the destination being the active cell?



7 Answers Found

Answer #1    Answered By: Constance Reid     Answered On: Aug 18

No, it's not a common question, it's fairly unusual to do this sort of

Your formula  is a simple  string. You can shape it any way you want to. For
this, you're looking at simple string manipulation using simple VBA.

Yesterday's date is found by subtracting one  from Date(). If you need it in
a specific format, use the Format() function.

The actual row/column name of the active cell  is retrieved by

Answer #2    Answered By: Este Ferrrari     Answered On: Aug 18

Sub PasteRMRegFunc()
mydate = Format(Month(Date - 1), "00") & Format(Day(Date - 1), "00")
Selection.Formula = "=VLOOKUP(D" & ActiveCell.Row & ",RMRegArray" &
mydate & ",8,FALSE)"
End Sub

Answer #3    Answered By: Channarong Boonliang     Answered On: Aug 18

These are absolute gems. I greatly appreciate your very fast help. I've already
begun to
change some of my macros  at work today. Almost thought I'd lost my file today
due to a
power outage. The IT guy told me I'd have to start from scratch, which meant I'd
lose ALL
my macros and named  ranges. Oh my! it was almost a melt down moment since I'm
the only
person in my office trying to use VB to make most of my Excel reporting more
efficient. Even
very simple  macros makes some tedious tasks tolerable.

Answer #4    Answered By: Abagail Cohen     Answered On: Aug 18

Here's a quick macro which, on a new sheet, will list all the ranged names in
the active workbook  (you can do this just as easily without VBA by selecting
Insert >> Name >> Paste >> Paste List).

Sub ListNames()
'On a new sheet, list all range names in active workbook.
Sheets.Add After:=Sheets(Sheets.Count)
End Sub

Answer #5    Answered By: Daisy Phillips     Answered On: Aug 18

Would this work?

Dim NewRow
Dim NewDate
NewRow = ActiveCell.Row
NewDate = Now()
NewDate = Format(NewDate, "MMdd")
Selection.Formula = "=VLOOKUP(D" & NewRow & ",RMRegArray" & NewDate &

Answer #6    Answered By: Keiko Mori     Answered On: Aug 18

The code I sent previously did not include the logic for yesterday's

Dim NewRow
Dim NewDate

NewRow = ActiveCell.Row
NewDate = Now()

Select Case Weekday(NewDate)
'Sunday = 1, Saturday = 7
'Monday looking for Friday
Case 2
NewDate = Now() - 3
'otherwise just yesterdays date
Case Else
NewDate = Now() - 1
End Select

NewDate = Format(NewDate, "MMdd")
Selection.Formula = "=VLOOKUP(D" & NewRow & ",RMRegArray" & NewDate
& ",8,FALSE)"

Answer #7    Answered By: Code Guru     Answered On: Aug 18

That's interesting. I can see ways to modify that for Mondays - I have to
do the
worksheet for Friday, Saturday and Sunday. That was my initial incentive for
making as
many macros  as possible. The reporting is difficult enough to get through daily
as there are
dozens of steps that used to take me hours. But I always dreaded having to
repeat the task
three times on Mondays just because data is building up while I'm having a much
weekend off. When I get an extended weekend ... well, I don't like to think
about it.

Didn't find what you were looking for? Find more on dynamic macros and named ranges questions Or get search suggestion and latest updates.