Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Macro Code for creating and populating a blank worksheet

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello all. I was wondering if anyone would be kind enough to direct me to the Macro coding I need to simplify my quarterly reporting.

    I have a large excel worksheet that is updated twice a month and lists organizations, the location of these organizations, and amounts given to each. To simplify my reporting I want to create a macro that would copy the organizations funded for a year I specify into a newly created worksheet with the amounts awarded them and separated by their locations, all within the same file. So in essence, the macro would create a blank worksheet that lists organizations, Sorted by location for a year I specify. This would allow me to alter only one worksheet and gaurantee the integrity of my report.

    Any help would be appreciated. Thanks all!

  2. #2
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    This should get you started. If you need more help feel free to followup.

    Lets say your files are on sheets("Sheet1") and the dates are in Col A and if the date is the year you specify you want to copy that row to a new workbook. Modify as needed.
    Option Explicit

    Sub UpdateIt()

    Dim x as Integer
    Dim NewWorkbookName as String
    Dim LastRow as Integer
    Dim SelectYear as Integer
    'Change "As Integer" to "As Long" if there are more than 32000 rows of data

    Workbboks.Add
    LastRow = Thisworkbook.Sheets("Sheet1").range("A65536").end(xlup).row
    SelectYear = Inputbox("What year do you want to get data for?")

    For x = 1 to LastRow

    If year(Thisworkbook.Sheets("Sheet1").range("A" & x).value) = SelectYear Then
    Activeworkbook.sheets(1).rows(activeworkbook.sheets(1).range("A65536").end(xlup).offset(1,0).row).FormulaR1C1 = Thisworkbook.Sheets("Sheet1").rows(x).value
    Else
    End If

    End Sub

    I didnt have time to check this code yet so there may be a typo bug, but this should get you started in the right direction.

    HTH

    DRJ

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey....thanks for the assist. I was actually able to find some coding and alter it with the coding you provided. My only question is if the date is in mm/dd/yyyy format and I format the cells to read yyyy, how should THIS VALUE look? Currently, my macro only works if the date is yyyy and not formatted, since my THIS VALUE ="2001" .... Thanks for the assistance!

    Charles

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops... left out the code that I used!
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Public Sub CopyRows()
    Sheets("Total Geographic area").Select
    ' Find the last row of data
    FinalRow = Range("A65536").End(xlUp).Row
    ' Loop through each row
    For x = 2 To FinalRow
    ' Decide if to copy based on column H
    ThisValue = Range("A" & x).Value
    If ThisValue = "2001" Then
    Range("A" & x & ":E" & x).Copy
    Sheets("Sheet2").Select
    NextRow = Range("A65536").End(xlUp).Row + 1
    Range("A" & NextRow).Select
    ActiveSheet.Paste
    Sheets("Total Geographic area").Select

    End If
    Next x
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •