Macro Code for creating and populating a blank worksheet

cchuateco

New Member
Joined
Feb 20, 2002
Messages
8
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top