Macro Code for creating and populating a blank worksheet

[cchuateco]

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!

[DRJ]

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

[cchuateco]

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

[cchuateco]

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