Easily extracting data from one spreadsheet to another

[Silent_Bob]

Hi everyone, i'm hoping someone can help me out...

I have a spreadsheet with the data I want, what i need to do is extract the
data from each row, or in one or two cases range of rows, to a seperate sheet
for each one. Is there a simple way for me to do this without having to copy
and paste each one manualy?

 

[Per Jessen]

Hi

You could use a macro to do it.

Further info is required if you need help writing the macro.

Regards,
Per

 

[Silent_Bob]

What info do you need?

I forgot to mention I'm using Excel 2003

Thanks for helping out

Rick

 

[Per Jessen]

Hi

Is it specific rows to be copied or will they change. How do I determin if
only one multiple rows is to be copied.

Shall the rows be copied into existing worksheets, an which.

//Per

 

[Silent_Bob]

Ok, what i have is a spreadsheet containing the circuit info for our sites,
there are several cases where there are multiple entries on seperate rows for
one site, which are grouped together with the same site name in the first
column, with info for each router and network link at the site.

What I need to do is create indiviual sheets for each site, containing all
the info about the network links at that site, that can be sent out and left
at each location for easy reference of engineers who visit.

the info can either be copied into the same spreadsheet on a new sheet or
info a new spreadsheet, whichever will work.

let me know if there's any more info needed,

and thanks again for helping out

 

[Per Jessen]

Hi

I think this is what you need. ALT +F11 to open the macro editor > Insert >
Module > Copy the code below to the codepage.
TargetRange is set to A2 as I assume you have headings in row 1 and sites is
in column A.
Do you want headings to be copied to the new sheets ?

Dim TargetRange As Range
Dim MainSh As Worksheet
Dim NewSh As Worksheet

Sub SplitSits()
Set MainSh = Worksheets("Sheet1") ' <=== Change to suit
Lastrow = Rows(Rows.Count).End(xlUp).Row
Set TargetRange = Range("A2") ' <=== Change to suit

off = 0
Do
Do
off = off + 1
Loop Until TargetRange.Value <> TargetRange.Offset(off, 0).Value
Range(TargetRange, TargetRange.Offset(off - 1, 0)).EntireRow.Select
Set NewSh = Worksheets.Add(after:=Worksheets(Sheets.Count))
ShName = TargetRange.Value
NewSh.Name = TargetRange
MainSh.Range(TargetRange, TargetRange.Offset(off - 1, 0)).EntireRow.Copy
_
Destination:=NewSh.Range("A2")
MainSh.Activate
Set TargetRange = TargetRange.Offset(off, 0)
off = 0
Loop Until TargetRange = ""
End Sub

Regards,
Per