Destination:=Range -- want it to refer to the current cell

[lmcc007]

Cell A4 has a date.

I recorded a macro to go to the cell to the right, which is A4. Grab the handle and bring it down to cell A5 to give me the next date.

Is there a way to change =Range("A4:A5") to reference the current cell?

Selection.AutoFill Destination:=Range("A4:A5"), Type:=xlFillDefault

And, is there a way to excludes Sundays from all future dates. Something like if Sunday, go to next day.

 

[Joe4]

Try:

Selection.AutoFill Destination:=Range(Selection, Selection.Offset(1, 0)), Type:=xlFillDefault

 

[lmcc007]

I change it to:

   Range(Selection, Selection.Offset(-1, 0)).Select
    Selection.AutoFill Destination:=Range(ActiveCell, Selection.Offset(1, 0)), Type:=xlFillDefault

It is adding a blank cell. Meaning,

1. A1 = 1/7/2017
2. My cursor is at A2, which A2 is blank
3. I run the macro
4. The macro selects A1--now A1 and A2 is highlighted
5. Then the macro puts the next date in cell A3 instead of A2, leaving me with a blank cell (A2)

I can't figure out what I am doing wrong.

 

[Joe4]

I thought you were starting in the cell you want to copy (that is how I read your original question), but here it sounds like you are starting in the blank cell you want to copy to:

1. A1 = 1/7/2017
2. My cursor is at A2, which A2 is blank

If that is the case, where you want to autofill the current cell from the cell above, here is one way:

    Selection.Offset(-1, 0).Select
    Selection.AutoFill Destination:=Range(ActiveCell, Selection.Offset(1, 0)), Type:=xlFillDefault

 

[lmcc007]

It works great! Thank you!

But, can I exclude all Sundays? Am I asking too much? Whenever there is a Sunday date, I delete it manually.

 

[Joe4]

But, can I exclude all Sundays? Am I asking too much? Whenever there is a Sunday date, I delete it manually.

So, is this just incrementing a date (adding one to the date above it)?
When you say "exclude Sundays", so you mean the the row that you are copying FROM is a Sunday date, or the incremented date you are creating will be a Sunday date?
What exactly do you want to happen to the previous row and current row in the event of a Sunday date (clear cell, delete whole row, skip date, etc)?

 

[lmcc007]

So, is this just incrementing a date (adding one to the date above it)?
When you say "exclude Sundays", so you mean the the row that you are copying FROM is a Sunday date, or the incremented date you are creating will be a Sunday date?
What exactly do you want to happen to the previous row and current row in the event of a Sunday date (clear cell, delete whole row, skip date, etc)?

All Sunday dates I have been deleting them. I am only using Mondays through Saturdays. I will not increment a Sunday date.

In the event of a Sunday date, I have been manually deleting them from the cell when I use autofill. But, if I enter the dates manually, I look at the calendar to make sure it is not a Sunday date.

In the event of a Sunday date, the previous row and current row stays--untouched. I just clear the current cell of the Sunday date.

 

[Joe4]

So I am still not clear - what more exactly do you want to happen here with this code we are coming up with?
Maybe walk us through an actual example...

 

[lmcc007]

Well, I don't know if Excel is capable of recognizing dates that fall on a Sunday.

All I want added is to exclude Sundays and go till Monday.

9/1/18, Saturday
9/3/18, Monday

I'm thinking as I am typing that maybe I could minus every 7th day.

I really don't know VBA, functions, and formula, so maybe I am not saying it correctly.

 

[Joe4]

I don't know if Excel is capable of recognizing dates that fall on a Sunday.

It sure can!

Change your code to work like this, and it will skip Sundays.

    If Weekday(ActiveCell.Offset(-1, 0)) = 7 Then
        ActiveCell = ActiveCell.Offset(-1, 0) + 2
    Else
        ActiveCell = ActiveCell.Offset(-1, 0) + 1
    End If