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
--

hi Bernie,
yes the data I am trying to pull relates to either of the two in respective
cells and it is split on the MK5 ETC.
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

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
--

When are you getting the "type mismatch"? Are you inputting in a column letter rather than the
column number? For example, if your data is in Range M1:P100, and you want to export based on
column N, then you need to enter a 2, not an "N"...

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
hi Bernie,
yes the data I am trying to pull relates to either of the two in respective
cells and it is split on the MK5 ETC.
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

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
--

Hi Bernie,

Again thanks I have solved the last issue but am still experiencing problems
as follows: When I run the macro it (1) creates a new sheet with the title (I
currently have all the sheets with their relevant tiltles so I wouldn't need
a new sheet.) (2) The data is filtered but nothing is pasted into the
relevant sheet.

I have ctried cahnging the 'Set' statement to select the worksheet and the
Goto Statement to Goto SheetsExists: myCell.Value.Name so that it looks at
that sheet. all to no avail.

advice needed please.

Also shouldn't the information simply be copied from the master straight to
the relevant worksheet by using the macro.

thanks

Keith
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

When are you getting the "type mismatch"? Are you inputting in a column letter rather than the
column number? For example, if your data is in Range M1:P100, and you want to export based on
column N, then you need to enter a 2, not an "N"...

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
hi Bernie,
yes the data I am trying to pull relates to either of the two in respective
cells and it is split on the MK5 ETC.
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

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
--

The macro as written assumes that the sheets for the export don't exist. Run the macro from
workbook with only your data sheet, and it will work.

HTH,
Bernie
MS Excel MVP


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

Again thanks I have solved the last issue but am still experiencing problems
as follows: When I run the macro it (1) creates a new sheet with the title (I
currently have all the sheets with their relevant tiltles so I wouldn't need
a new sheet.) (2) The data is filtered but nothing is pasted into the
relevant sheet.

I have ctried cahnging the 'Set' statement to select the worksheet and the
Goto Statement to Goto SheetsExists: myCell.Value.Name so that it looks at
that sheet. all to no avail.

advice needed please.

Also shouldn't the information simply be copied from the master straight to
the relevant worksheet by using the macro.

thanks

Keith
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

When are you getting the "type mismatch"? Are you inputting in a column letter rather than the
column number? For example, if your data is in Range M1:P100, and you want to export based on
column N, then you need to enter a 2, not an "N"...

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
hi Bernie,
yes the data I am trying to pull relates to either of the two in respective
cells and it is split on the MK5 ETC.
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

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
--

Hi Bernie,

I have copied the data & the macro to a new workbook and tried as you have
suggested but I am getting a an error as follows: "Run-time error(438) Object
doesn't support this property or method." It doesn't show me what what error
it's referring to.

Keith
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

The macro as written assumes that the sheets for the export don't exist. Run the macro from
workbook with only your data sheet, and it will work.

HTH,
Bernie
MS Excel MVP


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

Again thanks I have solved the last issue but am still experiencing problems
as follows: When I run the macro it (1) creates a new sheet with the title (I
currently have all the sheets with their relevant tiltles so I wouldn't need
a new sheet.) (2) The data is filtered but nothing is pasted into the
relevant sheet.

I have ctried cahnging the 'Set' statement to select the worksheet and the
Goto Statement to Goto SheetsExists: myCell.Value.Name so that it looks at
that sheet. all to no avail.

advice needed please.

Also shouldn't the information simply be copied from the master straight to
the relevant worksheet by using the macro.

thanks

Keith
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

When are you getting the "type mismatch"? Are you inputting in a column letter rather than the
column number? For example, if your data is in Range M1:P100, and you want to export based on
column N, then you need to enter a 2, not an "N"...

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
hi Bernie,
yes the data I am trying to pull relates to either of the two in respective
cells and it is split on the MK5 ETC.
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

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
--

Keith,

And the column just has mk5 and pk5 in it? Send me your workbook, and I will take a look.

HTH,
Bernie
MS Excel MVP


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

I have copied the data & the macro to a new workbook and tried as you have
suggested but I am getting a an error as follows: "Run-time error(438) Object
doesn't support this property or method." It doesn't show me what what error
it's referring to.

Keith
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

The macro as written assumes that the sheets for the export don't exist. Run the macro from
workbook with only your data sheet, and it will work.

HTH,
Bernie
MS Excel MVP


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

Again thanks I have solved the last issue but am still experiencing problems
as follows: When I run the macro it (1) creates a new sheet with the title (I
currently have all the sheets with their relevant tiltles so I wouldn't need
a new sheet.) (2) The data is filtered but nothing is pasted into the
relevant sheet.

I have ctried cahnging the 'Set' statement to select the worksheet and the
Goto Statement to Goto SheetsExists: myCell.Value.Name so that it looks at
that sheet. all to no avail.

advice needed please.

Also shouldn't the information simply be copied from the master straight to
the relevant worksheet by using the macro.

thanks

Keith
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

When are you getting the "type mismatch"? Are you inputting in a column letter rather than
the
column number? For example, if your data is in Range M1:P100, and you want to export based on
column N, then you need to enter a 2, not an "N"...

HTH,
Bernie
MS Excel MVP


"KR" wrote in message
...
hi Bernie,
yes the data I am trying to pull relates to either of the two in respective
cells and it is split on the MK5 ETC.
--
NEWBIE WILLING TO LEARN


"Bernie Deitrick" wrote:

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
--