Hi I am trying to import specific data in one spreadsheet to different tabs
in the same workbook (e.g) the masster spreadsheet has all the details
imported from a different program and I need to extract specific information
and import that info onto a several worksheets of the same work book.

hope this makes sense.

keith
--

Keith,

Yes, your question makes sense, but a better way is to use data filters to just show the information
of interest. Apply the filtering to the master sheet.

It is often (if not usually or even always) a bad idea to make copies of data on separate sheets.

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
Hi I am trying to import specific data in one spreadsheet to different tabs
in the same workbook (e.g) the masster spreadsheet has all the details
imported from a different program and I need to extract specific information
and import that info onto a several worksheets of the same work book.

hope this makes sense.

keith
--

Thanks Bernie,

The master file will change every month but I still want to import data that
meet a certain criteria to a specific worksheet within the workbook. (e.g) If
data on column A of the master sheet shows the following severals rows
withe value mk5 then several rows showing pk5. I want to import all data
referring to mk5 and pk5 to different worksheets named mk5 and pk5.

Keith
--


"Bernie Deitrick" wrote:

Keith,

Yes, your question makes sense, but a better way is to use data filters to just show the information
of interest. Apply the filtering to the master sheet.

It is often (if not usually or even always) a bad idea to make copies of data on separate sheets.

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
Hi I am trying to import specific data in one spreadsheet to different tabs
in the same workbook (e.g) the masster spreadsheet has all the details
imported from a different program and I need to extract specific information
and import that info onto a several worksheets of the same work book.

hope this makes sense.

keith
--

Keith,

Then try the macro below, with the master sheet active. When prompted, indicate which column within
the database has the pk5 and mk5 values.

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub

"KR" wrote in message
...
Thanks Bernie,

The master file will change every month but I still want to import data that
meet a certain criteria to a specific worksheet within the workbook. (e.g) If
data on column A of the master sheet shows the following severals rows
withe value mk5 then several rows showing pk5. I want to import all data
referring to mk5 and pk5 to different worksheets named mk5 and pk5.

Keith
--


"Bernie Deitrick" wrote:

Keith,

Yes, your question makes sense, but a better way is to use data filters to just show the
information
of interest. Apply the filtering to the master sheet.

It is often (if not usually or even always) a bad idea to make copies of data on separate sheets.

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
Hi I am trying to import specific data in one spreadsheet to different tabs
in the same workbook (e.g) the masster spreadsheet has all the details
imported from a different program and I need to extract specific information
and import that info onto a several worksheets of the same work book.

hope this makes sense.

keith
--

Hi Bernie,

Thanks for that, I am still having a slight problem as whern I run the macro
I keep getting an error message stating "type mismatch". I have checked the
type for both the master spreadsheet and the destination worksheets to make
sure the columns are formated the same.

Any idea ?

Keith
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

Keith,

Then try the macro below, with the master sheet active. When prompted, indicate which column within
the database has the pk5 and mk5 values.

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub

"KR" wrote in message
...
Thanks Bernie,

The master file will change every month but I still want to import data that
meet a certain criteria to a specific worksheet within the workbook. (e.g) If
data on column A of the master sheet shows the following severals rows
withe value mk5 then several rows showing pk5. I want to import all data
referring to mk5 and pk5 to different worksheets named mk5 and pk5.

Keith
--


"Bernie Deitrick" wrote:

Keith,

Yes, your question makes sense, but a better way is to use data filters to just show the
information
of interest. Apply the filtering to the master sheet.

It is often (if not usually or even always) a bad idea to make copies of data on separate sheets.

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
Hi I am trying to import specific data in one spreadsheet to different tabs
in the same workbook (e.g) the masster spreadsheet has all the details
imported from a different program and I need to extract specific information
and import that info onto a several worksheets of the same work book.

hope this makes sense.

keith
--

Which line does it break on? Is the data that you are trying to split on the mk5 / pk5?

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
Hi Bernie,

Thanks for that, I am still having a slight problem as whern I run the macro
I keep getting an error message stating "type mismatch". I have checked the
type for both the master spreadsheet and the destination worksheets to make
sure the columns are formated the same.

Any idea ?

Keith
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

Keith,

Then try the macro below, with the master sheet active. When prompted, indicate which column
within
the database has the pk5 and mk5 values.

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub

"KR" wrote in message
...
Thanks Bernie,

The master file will change every month but I still want to import data that
meet a certain criteria to a specific worksheet within the workbook. (e.g) If
data on column A of the master sheet shows the following severals rows
withe value mk5 then several rows showing pk5. I want to import all data
referring to mk5 and pk5 to different worksheets named mk5 and pk5.

Keith
--


"Bernie Deitrick" wrote:

Keith,

Yes, your question makes sense, but a better way is to use data filters to just show the
information
of interest. Apply the filtering to the master sheet.

It is often (if not usually or even always) a bad idea to make copies of data on separate
sheets.

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
Hi I am trying to import specific data in one spreadsheet to different tabs
in the same workbook (e.g) the masster spreadsheet has all the details
imported from a different program and I need to extract specific information
and import that info onto a several worksheets of the same work book.

hope this makes sense.

keith
--