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


"Batman2002" <(E-Mail Removed)> wrote in message
news:Wqtjb.6149715$(E-Mail Removed)...
> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:Wqtjb.6149715$(E-Mail Removed)...
> 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
>
>

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



"Arvi Laanemets" <(E-Mail Removed)> wrote in message
news:%23kuWby%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:Wqtjb.6149715$(E-Mail Removed)...
> > 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,

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


"Batman2002" <(E-Mail Removed)> wrote in message
newsSxjb.6158564$(E-Mail Removed)...
> 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
>
>
>
> "Arvi Laanemets" <(E-Mail Removed)> wrote in message
> news:%23kuWby%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:Wqtjb.6149715$(E-Mail Removed)...
> > > 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
> > >
> > >
> >
> >
>
>