AutoFill Destination but use cell format from cells above

I'm trying to write a macro that will autofill the formula to the left. I also want the values that are autofilled to fit the format of the cells above them. Here is my code:

Range("D5").Select
Selection.AutoFill Destination:=Range("D5:D6"), Type:=xlFillSeries
Range("D5:D6").Select
Range("F6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
Range("F6").Select
Selection.AutoFill Destination:=Range("F6:W6"), Type:=xlFillDefault

The problem is the initial cell is formatted as a percent. When I use the autofill all the values are formatted as a percent. How can I make the code do what its doing now and then format the cell the similar to the cell above it?

You can copy paste number formats.

Please Login or Register  to view this content.

Hi excel8031
You might try this

Please Login or Register  to view this content.

Hi excel8031
I have a difficult time visualizing what you're trying to accomplish. From the code you presented, see if this does as you require.

Please Login or Register  to view this content.

Hi excel8031

You're welcome...glad I could be of help. If that satisfies your need, I'd appreciate it if you'll please mark your thread as "Solved".

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save