Create individual worksheets for selected rows in a table

[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

 

[Bernie Deitrick]

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

 

[Arvi Laanemets]

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

 

[Batman2002]

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

 

[Bernie Deitrick]

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