VB Command Button Macro Help (Posting Months)

Posted on 2012-08-14
Last Modified: 2012-08-14

I'm trying to create a Macro that goes to the "Raw Data" sheet and pastes the formula into all of the W:W column and am running into some trouble. I want the formula to go down to the bottom most entry of the last data record entered.

Would someone mind looking at my formula...?

Please and thanks.
Question by:nomios
    LVL 23

    Expert Comment


    If I understand you correctly you need to determine the last cell at runtime to correctly copy down the formula.

    To do this use something like

    Dim lastrow as long
    lastrow = Range("S" & Rows.Count).End(XLup).Row
    Range("W2").AutoFill Destination:=Range("W2:W" & lastrow)

    Open in new window


    Author Comment

    I still can't get it to work....I think I'm doing the code wrong.

        Sub PostMonth()
        Dim lastrow As Long
        lastrow = Range("S" & Rows.Count).End(xlUp).Row
        Range("W2").AutoFill Destination:=Range("V2:V" & lastrow)
        Worksheets("Raw Data").Range("V2:V").Formula = "=Month(C2:C)"
         SendKeys "{esc}"
        End Sub

    Open in new window

    LVL 23

    Accepted Solution


    This script will copy down the formula =MONTH(C2) from cell W2 to last used cell in column C

    Sub PostMonth()
       Dim lastrow As Long
       With Worksheets("Raw Data")
          .Range("W2").Formula = "=MONTH(C2)"
          lastrow = .Range("C" & Rows.Count).End(xlUp).Row
          .Range("W2").AutoFill Destination:=.Range("W2:W" & lastrow)
       End With
    End Sub

    Open in new window


    Author Closing Comment

    Worked wonderfully. Thank you.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now