Excel - Conditional macro / VBA script

 Question:

  I'm trying to automate a report that for a client and I'm a bit stuck with one of the obstacles I need to overcome, I have some ideas, but I'm new to VB programming.

The requirement is to copy a range of cells from one sheet to another, but the target must change depending on the current date. Using a general example, I am trying to achieve the following:

If the date is the first of the month, the target range is B2: F3, if it is the second, then the target range is B4: F5, if the third is B6: F7 ....... if 31 , Then the destination is B62: F63, the source ranges are static.

I thought I could probably accomplish this by writing a huge script that contained an IF statement for each day of the month, but I was hoping to be a little smarter and use variables to assign the row references at the beginning of the script, then just sub them again In select / copy statements.




 Answers:
 

Absolutely you can.

Dim x as Integer
Dim daymonth as Integer
Dim rw as String
daymonth = CInt(Format(date, "d"))
x = daymonth * 2
rw = CStr(x)

Now you can use range like:

Range("D" & rw & ":F" & CStr(x + 1))

Just an example. Then since the number is constant between the two ranges just add that number to x and use it in the range.