Excel Macro Help (autofill)

I'm in the process of writng an excel application to update a bunch of charts... I'm in the very beginning of the processs.  where I'm taking searching column A for the first empty cell then inputting by month a new year (12 cells)... where i'm having trouble is the autofill to get the rest of the values filled in.


Private Sub cmdNewYear_Click()
       
Response = MsgBox("Are you wanting to input a new year ?", vbQuestion + vbYesNo, "Question")
     'Message Box Asking if user wants to make a new year
If Response = 6 Then            
     'If Yes then
ThisWorkbook.Worksheets("Data").Activate
     'Activate Worksheet named Data
Dim a As Integer
Dim b As Integer
a = 1
Do While Not IsEmpty(ActiveSheet.Cells(a, 1))
     'checking to see if cells are empty then moving
      to the next cell down col A
a = a + 1
Loop
ActiveSheet.Cells(a, 1).Select
     'activate first empty cell
b = a + 12
a = a + 1
InputYear = InputBox("What year are you entering ?", "Enter Year", "1/1/20xx")
     'input box for new year
ActiveCell.Value = InputYear
     'input value from new year box into first empty cell
Cells(a, 1).AutoFill Cells(a, 1), Cells(b, 1)


any help would be appreciated


End If
End Sub
scottbaughAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lvngstnCommented:
Replace the line:
Cells(a, 1).AutoFill Cells(a, 1), Cells(b, 1)
with:
Cells(a, 1).AutoFill Range(Cells(a, 1), Cells(b, 1))


One thing you might notice is that this does not fill in 12 months, but rather 12 days if the user sends a full date (i.e. "1/1/2003") through the input box.  To remedy this, replace the autofill line with this for-loop:

For i = 1 To 12
  Cells(a + i, 1).Value = DateSerial(Year(inputyear), i, 1)
Next i


Hope this is what you're looking for.
lvngstn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
scottbaughAuthor Commented:
Thanks for the help... I can actually continue my work now.  I had to tweak it a little... ended up adding ActiveSheet. to the beginning of the For...Next statement.  Thanks again for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.