Create individual worksheets for selected rows in a table

  • Thread starter Thread starter Batman2002
  • Start date Start date
B

Batman2002

Hi guys,

I hope somebody may be able to help.

I want to be able to automate the following procedure

Create individual worksheets for selected rows in a table.


Many thanks
- Batman2002
 
Batman,

If you really want the entirerow, then use this - if you just want the
selection, then remove the .EntireRow of the last line:

Sub CopySelectioToNewWorksheet()
Dim mySheet1 As Worksheet
Dim mySheet2 As Worksheet
Set mySheet1 = ActiveSheet
Set mySheet2 = Sheets.Add(Type:="Worksheet")
mySheet1.Activate
Selection.EntireRow.Copy mySheet2.Range("A1")
End Sub

HTH,
Bernie
 
Hi


Do you really need to split your table into a bunch of worksheets, or is it
p.e. for printing/reporting only? When last is the case, then create a
report sheet, where you can select some key (or row number) value from
source table, and all data from apropriate row are automatically displayed
on report sheet.

An example:
You have table on sheet Source (1st row are headers)
Field1, Field2, Field3, Field4, ...

On report sheet, you enter into A1 the text "Row:", and into B1 some number
Into cell on report sheet, where you want value from matching row in column
A, enter the formula:
=OFFSET(Source!$A$1,$B$1,0)
Into cell on report sheet, where you want value from matching row in column
B, enter the formula:
=OFFSET(Source!$A$1,$B$1,1)
Into cell on report sheet, where you want value from matching row in column
C, enter the formula:
=OFFSET(Source!$A$1,$B$1,2)
etc.

When you have some key value to search for, use MATCH($B$1,Source!Datarange)
function to estimate the index of searched row in your datarange, and in
formulas above replace $B$1 with it.


Arvi Laanemets
 
Thanks guys, for your replies.

What I have is 2 worksheets: one is called Data, this sheet contains a table
of figures i.e. house number, purchase price, sale price, gross profit, etc.

The other worksheet is called Report. When a house number is inputted, using
VLOOKUP it fills in the blanks.

What I would like to do is perform a VLOOKUP for each house number and
creating a worksheet for each set of results, also each work sheet needs to
be named as the corresponding house number.


Is there a way this can be done, thank you in advance.

- Batman2002
 
Batman,

Let's say your houses are all listed in a named range "Houses", your
VLookup formulas are keyed to cell A1 of worksheet "Report" (and the
keying is based on the values in range "Houses"), and your report is
rows 2:20, then this macro will step through all the houses and create
separate reports for each.

Sub CopyReportToNewWorksheet()
Dim mySheet1 As Worksheet
Dim mySheet2 As Worksheet
Dim myCell As Range

Set mySheet1 = Worksheets("Report")

For Each myCell In Range("Houses")
Set mySheet2 = Sheets.Add(Type:="Worksheet")
mySheet1.Activate
mySheet1.Range("A1").Value = myCell.Value
Application.CalculateFull
Range("A2:A20").EntireRow.Copy mySheet2.Range("A1")
mySheet2.Name = mySheet1.Range("A1").Value
Next myCell
End Sub

HTH,
Bernie
 
Back
Top