[color=green]'Assumption: your new records are on Sheet2.[/color]
[color=green]' Change the word "Sheet2" to whatever name you[/color]
[color=green]' give the tab for your input sheet.[/color]
[color=green]'Assumption: your column titles (headers,fieldnames,whatever) are on row1[/color]
[color=green]'Assumption: your first record on the input sheet begins in row2[/color]
[color=green]'Copy this code and put it into the module for your input sheet[/color]
[color=green]'(Sheet2 or whatever you call it)[/color]
[color=darkblue]Sub[/color] Update()
[color=darkblue]Dim[/color] FirstRow, LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
[color=green]'Select your input sheet[/color]
Sheets("Sheet2").Select
[color=green]'Find the last record that was input[/color]
[color=darkblue]With[/color] ActiveSheet
.Range("A65536").Select
Selection.End(xlUp).Select
LastRow = Selection.Row
[color=green]'pick the last 7 records that were input[/color]
[color=green]'if 7 haven't been input yet, take what's there[/color]
[color=green]'including blank rows until you have 7 rows[/color]
[color=darkblue]If[/color] LastRow < 8 [color=darkblue]Then[/color]
FirstRow = 2
LastRow = 8 [color=green]'lastrow is record#7, need previous 6[/color]
[color=darkblue]Else[/color]
FirstRow = LastRow - 6 [color=green]'lastrow is record#7, need previous 6[/color]
[color=darkblue]End[/color] [color=darkblue]If[/color]
[color=green]'Assuming you are using up to 8 (A-H) columns on your input sheet[/color]
[color=green]'If not, you can leave as is because it won't hurt anything unless you need[/color]
[color=green]'more than 8 columns, then change H to whatever (L or N, etc)[/color]
[color=green]'Also, if you don't use column A and begin on column B, change the A to B[/color]
[color=green]'Change the H to the last column you are using[/color]
[color=green]'Now, pick the column titles[/color]
.Range("A1:H1").Select
Selection.Copy
[color=darkblue]End[/color] [color=darkblue]With[/color]
[color=green]'move to sheet1[/color]
Sheets("Sheet1").Select
[color=green]'If you changed A to B, change it here too...[/color]
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
[color=green]'back to Sheet2[/color]
Sheets("Sheet2").Select
[color=green]'If you changed A to B, change it here too...[/color]
[color=green]'If you changed H to something, change it here too...[/color]
[color=green]'Now, copy last 7 records[/color]
ActiveSheet.Range("A" & [color=darkblue]CStr[/color](FirstRow) & ":H" & [color=darkblue]CStr[/color](LastRow)).Select
Selection.Copy
[color=green]'move to sheet1[/color]
Sheets("Sheet1").Select
[color=green]'If you changed A to B, change it here too...[/color]
[color=green]'If you changed H to something, change it here too...[/color]
[color=green]'Now, paste the last 7 rows of data from sheet2[/color]
[color=darkblue]With[/color] ActiveSheet
.Range("A2").Select
.Paste
[color=green]'Now, set the printarea on sheet1 for[/color]
[color=green]'Titles + 7 records[/color]
.Range("A1:H8").Select
.PageSetup.PrintArea = "$A$1:$H$8"
[color=green]'this is just to unselect A1-H8 so you don't delete by accident[/color]
.Range("A9").Select
[color=green]'this puts you into print preview mode[/color]
.PrintPreview
[color=green]'you can change preview to .PrintOut Copies:=1 to simply print[/color]
[color=darkblue]End[/color] [color=darkblue]With[/color]
[color=green]'back to sheet2[/color]
Sheets("Sheet2").Select
[color=green]'this is just to unselect so you don't delete by accident[/color]
ActiveSheet.Range("A" & Cstr(LastRow+1)).Select
[color=darkblue]End[/color] [color=darkblue]Sub[/color]