How to have a macro print output to individual tabs in a new workbook?

Hi all,

I am trying to print out 95 different rate tables by county. I currently have a macro in place that changes each county name and then prints out the rate page. See following code.

Sub CountyPrint2()
Dim x As Integer

For x = 3 To 97
Range("BE" & Trim(Str(x))).Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1:V52").Select
Selection.PrintOut Copies:=1, Collate:=True

Next

Range("A2").Select

End Sub

What I would like to do now instead of printing out each rate page is to have a new workbook open with 95 tabs. Each tab would show a separate county. I have attached a spreadsheet to show what I am looking at. On each tab I would like to show what I have displayed in cells A2:V50 only. My friend suggested the following macro to get me started, but I have zero VBA Experience and do not know how to adapt it to my problem.

sub test()
dim x as integer
dim NewBook as Excel.workbook
Dim HomeBook as Excel.workbook

set homebook = thisworkbook 'this is your data workbook
set newbook = excel.workbooks.add 'this is your new one with tabs

For x = 3 To 97 'loop 95 times
Range("BE" & Trim(Str(x))).Select 'looks in column BE with x as row
Selection.Copy
newbook.sheets.add after:=newbook.sheets(newbook.sheets.count) 'add tab
newbook.activesheet.cells(1,1).select 'select first cell
newbook.activesheet.paste 'paste data
newbook.activesheet.name = "county name" 'change the tab name to whatever county
Application.CutCopyMode = False 'clears the clipboard to save memory
homebook.activate 'go back to your data
Next
newbook.saveas("xxxxx")
newbook.close

end sub

Thanks for any suggestions.

I now have most of my problem figured out. I still need some help with keeping my formating, especially my printing range. I would hate to set up a print range on 95 tabs.
Please see code below.

Sub Test()
Dim NewBook As Excel.Workbook 'Workbook object for new tabs
Dim HomeBook As Excel.Workbook 'workbook object for source data
Dim MyCell As Excel.Range 'range object to grab all counties

Application.DisplayAlerts = False 'when pasting tabs, get alert to update values, this disables those

Set HomeBook = ThisWorkbook 'set homebook to the data
Set NewBook = Excel.Workbooks.Add 'add a new book to paste tabs into

For Each MyCell In HomeBook.ActiveSheet.Range(HomeBook.ActiveSheet.Range("BE3"), HomeBook.ActiveSheet.Range("BE97")) 'loops through BE3 to BE97 to grab counties names
HomeBook.Activate 'see the data book
HomeBook.ActiveSheet.Cells(2, 1).Value = MyCell 'set list to current county to get right data
NewBook.Activate 'look as new book
NewBook.Sheets.Add after:=NewBook.Sheets(NewBook.Sheets.Count) 'add a tab to the end
NewBook.ActiveSheet.Name = MyCell 'name the tab to the county
HomeBook.Activate 'look at the data
HomeBook.ActiveSheet.Range("A2:V50").Select 'select A2 to V50 of the data
Selection.Copy 'copy
NewBook.Activate 'look at the new book
NewBook.ActiveSheet.Cells(1, 1).Select 'select the first cell
Selection.PasteSpecial Paste:=xlPasteValues 'paste values since data is linked, normal paste would result in 0's
Next

MsgBox ("Complete")
End Sub