selecting data

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
I have a monthly file will numerous entries on each date. I am trying to create a macro that will create new monthly file instead of doing it by hand. As it is now I have to copy so many rows from the old file into the new file to get it started. I am looking for a code that will copy 74 rows or one entry prior to the beginning of the month, whichever is more. example- if I have 100 entries from 12/1/05 on then I want to select all entries from Dec. plus one row from Nov. If I only have 50 rows starting at 12/1/05 then it will select 74 rows from the most recent back into Nov. (however far it needs to go). I think I have a code to just select the 74 rows but I'm not sure how to modify it how I want. Here is the code that I have-

Code:
Range(Range("A1").End(xlDown), Range("A1").End(xlDown).Offset(-74, 0)).EntireRow.Copy
I hope I made this clear????

thanks
royboy531
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

This assumes that there will be an entry for the last day of the relevant month, and the data

Code:
Sub bbb()
 lastrow = Cells(Rows.Count, "A").End(xlUp).Row
 startrow = WorksheetFunction.Match(Evaluate("=eomonth(" & Cells(lastrow, "A").Address & ",-2)"), Range("a:a"), 0)
 Range(Cells(startrow, "A"), Cells(lastrow, "A")).Select
End Sub

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,118,386
Messages
5,571,829
Members
412,421
Latest member
grace_abar
Top