select the data and copy in another sheet

D

diaExcel

Hi,

I have this workbook where I have a sheet with this information:

company name-Memory
sheeft 1 TM/CS Type salary 1 salary 2 salary 3 salary 4 def salary

Employ 1 Default - - - - 2500
Employ 2 Default - - - - 3500

Employ 1 TM Special 2600 2700 2800 2900 -
Employ 2 TM Special 2650 2750 2850 2950 -

Employ 1 Promo 3500 - 3500 3500 -
Employ 2 Promo 3600 - 3600 3600 -

Employ 1 Special 4500 - 4500 4500 -
Employ 2 Special 4600 - 4600 4600 -

Employ 1 CS Special 3870 3870 3870 3870 -
Employ 2 CS Special 4570 4570 4570 4570 -

Employ 1 Special 4370 4070 4570 3870 -
Employ 2 Special 5070 4770 5070 4570 -
sheeft2 TM/CS Type salary 1 salary 2 salary 3 salary 4 def salary

Fix Default - - - - 2550
Employ 1 Default - - - - 3550
Employ 2 Default - - - - 4500

Fix TM Special 1870 1570 1370 1370 -
Employ 1 TM Special 1470 1170 970 970 -
Employ 2 TM Special 3200 2970 2770 2770 -

Fix Promo 1170 - 1170 1170 -
Employ 1 Promo 770 770 770 770 -
Employ 2 Promo 3370 - 3370 3370 -

Fix Special 1370 - 1370 1370 -
Employ 1 Special 970 970 970 970 -
Employ 2 Special 4170 - 4170 4170 -

Fix CS Special 1370 1370 1370 1570 -
Employ 1 CS Special 970 970 970 1170 -
Employ 2 CS Special 2770 2770 2770 2970 -

Fix Special 1870 1570 1370 1370 -
Employ 1 Special 1470 1170 970 970 -
Employ 2 Special 3270 2970 4170 2770 -
company name-Mobil
sheeft 1 TM/CS Type salary 1 salary 2 salary 3 salary 4 def salary

Employ 1 Default - - - - 2500
Employ 2 Default - - - - 3500

Employ 1 TM Special 2600 2700 2800 2900 -
Employ 2 TM Special 2650 2750 2850 2950 -

Employ 1 Promo 3500 - 3500 3500 -
Employ 2 Promo 3600 - 3600 3600 -

Employ 1 Special 4500 - 4500 4500 -
Employ 2 Special 4600 - 4600 4600 -

Employ 1 CS Special 3870 3870 3870 3870 -
Employ 2 CS Special 4570 4570 4570 4570 -

Employ 1 Special 4370 4070 4570 3870 -
Employ 2 Special 5070 4770 5070 4570 -
sheeft2 TM/CS Type salary 1 salary 2 salary 3 salary 4 def salary

Fix Default - - - - 2550
Employ 1 Default - - - - 3550
Employ 2 Default - - - - 4500

Fix TM Special 1870 1570 1370 1370 -
Employ 1 TM Special 1470 1170 970 970 -
Employ 2 TM Special 3200 2970 2770 2770 -

Fix Promo 1170 - 1170 1170 -
Employ 1 Promo 770 770 770 770 -
Employ 2 Promo 3370 - 3370 3370 -

Fix Special 1370 - 1370 1370 -
Employ 1 Special 970 970 970 970 -
Employ 2 Special 4170 - 4170 4170 -

Fix CS Special 1370 1370 1370 1570 -
Employ 1 CS Special 970 970 970 1170 -
Employ 2 CS Special 2770 2770 2770 2970 -

Fix Special 1870 1570 1370 1370 -
Employ 1 Special 1470 1170 970 970 -
Employ 2 Special 3270 2970 4170 2770 -


and I want to put every company in a new sheet with name like company name,
and all rows that contain "Default" from the colomn "Type" and from the
column "TM/CS" the rows with TM and CS I want to copy in those sheets, I mean
i want to looks like this sheet for the first company:

sheeft 1 TM/CS Type salary 1 salary 2 salary 3 salary 4 def salary
sheeft 1
sheeft 1 Employ 1 Default - - - - 2500
sheeft 1 Employ 2 Default - - - - 3500
sheeft 1 Employ 1 TM Special 2600 2700 2800 2900 -
sheeft 1 Employ 2 TM Special 2650 2750 2850 2950 -
sheeft 1 Employ 1 CS Special 3870 3870 3870 3870 -
sheeft 1 Employ 2 CS Special 4570 4570 4570 4570 -
sheeft2 Fix Default - - - - 2550
sheeft2 Employ 1 Default - - - - 3550
sheeft2 Employ 2 Default - - - - 4500
sheeft2 Fix TM Special 1870 1570 1370 1370 -
sheeft2 Employ 1 TM Special 1470 1170 970 970 -
sheeft2 Employ 2 TM Special 3200 2970 2770 2770 -
sheeft2 Fix CS Special 1370 1370 1370 1570 -
sheeft2 Employ 1 CS Special 970 970 970 1170 -
sheeft2 Employ 2 CS Special 2770 2770 2770 2970 -


so, is possible ? and how can I can I do that?

thank you.
 
J

Joel

Can you give a list of what data is in which columns. I need to know where
to get the Company Name, the Sheet name, and how to determine where one hseet
or workbook aend and the next starts.
 
D

diaExcel

ok, the data is in a workbook on sheet1, and I want for the first company
"Memory" to create a new sheet with the name "Memory" and in this sheet
"Memory" to put the data from the sheet1 from the first company, the fist
column(A) to be with # of sheeft (sheeft1), column B=Employ 1 or 2 or fix,
column C="TM" or "CS" column D="Type", column E="salary 1" column F="salary
2" column G="salary 3" column H="salary 4" and column I="def salary"
as you can see, I have on sheet1 "sheeft1" just in the first row and I
wanted to be on every row until begin sheeft 2.
I mean I wanted to look like this:



'***************************************************************************
this will be the next sheet with the name 'Memory"
A B C D E
F G H I

nr_sheeft name_employ team type salary 1 salary 2 salary 3
salary 4 def_salary
sheeft 1 Employ 1 Default - - - - 2500
sheeft 1 Employ 2 Default - - - - 3500
sheeft 1 Employ 1 TM Special 2600 2700 2800 2900 -
sheeft 1 Employ 2 TM Special 2650 2750 2850 2950 -
sheeft 1 Employ 1 CS Special 3870 3870 3870 3870 -
sheeft 1 Employ 2 CS Special 4570 4570 4570 4570 -
sheeft2 Fix Default - - - - 2550
sheeft2 Employ 1 Default - - - - 3550
sheeft2 Employ 2 Default - - - - 4500
sheeft2 Fix TM Special 1870 1570 1370 1370 -
sheeft2 Employ 1 TM Special 1470 1170 970 970 -
sheeft2 Employ 2 TM Special 3200 2970 2770 2770 -
sheeft2 Fix CS Special 1370 1370 1370 1570 -
sheeft2 Employ 1 CS Special 970 970 970 1170 -
sheeft2 Employ 2 CS Special 2770 2770 2770 2970 -


and this is the sheet1 with all companies and all data:
'*********************************************************************
A B C D
E F G H I
company name-Memory
sheeft 1 team type salary 1 salary 2 salary 3 salary 4 def salary

Employ 1 Default - - - - 2500
Employ 2 Default - - - - 3500

Employ 1 TM Special 2600 2700 2800 2900 -
Employ 2 TM Special 2650 2750 2850 2950 -

Employ 1 Promo 3500 - 3500 3500 -
Employ 2 Promo 3600 - 3600 3600 -

Employ 1 Special 4500 - 4500 4500 -
Employ 2 Special 4600 - 4600 4600 -

Employ 1 CS Special 3870 3870 3870 3870 -
Employ 2 CS Special 4570 4570 4570 4570 -

Employ 1 Special 4370 4070 4570 3870 -
Employ 2 Special 5070 4770 5070 4570 -
sheeft2 team type salary 1 salary 2 salary 3 salary 4 def salary

Fix Default - - - - 2550
Employ 1 Default - - - - 3550
Employ 2 Default - - - - 4500

Fix TM Special 1870 1570 1370 1370 -
Employ 1 TM Special 1470 1170 970 970 -
Employ 2 TM Special 3200 2970 2770 2770 -

Fix Promo 1170 - 1170 1170 -
Employ 1 Promo 770 770 770 770 -
Employ 2 Promo 3370 - 3370 3370 -

Fix Special 1370 - 1370 1370 -
Employ 1 Special 970 970 970 970 -
Employ 2 Special 4170 - 4170 4170 -

Fix CS Special 1370 1370 1370 1570 -
Employ 1 CS Special 970 970 970 1170 -
Employ 2 CS Special 2770 2770 2770 2970 -

Fix Special 1870 1570 1370 1370 -
Employ 1 Special 1470 1170 970 970 -
Employ 2 Special 3270 2970 4170 2770 -
company name-Mobil
sheeft 1 TM/CS Type salary 1 salary 2 salary 3 salary 4 def salary

Employ 1 Default - - - - 2500
Employ 2 Default - - - - 3500

Employ 1 TM Special 2600 2700 2800 2900 -
Employ 2 TM Special 2650 2750 2850 2950 -

Employ 1 Promo 3500 - 3500 3500 -
Employ 2 Promo 3600 - 3600 3600 -

Employ 1 Special 4500 - 4500 4500 -
Employ 2 Special 4600 - 4600 4600 -

Employ 1 CS Special 3870 3870 3870 3870 -
Employ 2 CS Special 4570 4570 4570 4570 -

Employ 1 Special 4370 4070 4570 3870 -
Employ 2 Special 5070 4770 5070 4570 -
sheeft2 TM/CS Type salary 1 salary 2 salary 3 salary 4 def salary

Fix Default - - - - 2550
Employ 1 Default - - - - 3550
Employ 2 Default - - - - 4500

Fix TM Special 1870 1570 1370 1370 -
Employ 1 TM Special 1470 1170 970 970 -
Employ 2 TM Special 3200 2970 2770 2770 -

Fix Promo 1170 - 1170 1170 -
Employ 1 Promo 770 770 770 770 -
Employ 2 Promo 3370 - 3370 3370 -

Fix Special 1370 - 1370 1370 -
Employ 1 Special 970 970 970 970 -
Employ 2 Special 4170 - 4170 4170 -

Fix CS Special 1370 1370 1370 1570 -
Employ 1 CS Special 970 970 970 1170 -
Employ 2 CS Special 2770 2770 2770 2970 -

Fix Special 1870 1570 1370 1370 -
Employ 1 Special 1470 1170 970 970 -
Employ 2 Special 3270 2970 4170 2770 -

and the next company "Mobil" is has to be on the next sheet with the name
"Mobil" and has to look like the sheet "Memory" but with the data from the
"Mobil" company.

Please, please help me!!!
If isn't so clear please let me know.

Thanks .
 
J

Joel

Sub test()

Const Folder = "c:\temp\test\"
Const Prefix = "company name-"
Application.EnableEvents = False
First = True
With ThisWorkbook.Sheets("Memory")
Lastrow = .Range("B" & Rows.Count).End(xlUp).Row
For MemRowCount = 1 To Lastrow
ColAData = .Range("A" & MemRowCount)
If Left(ColAData, Len(Prefix)) = Prefix Then
If First = True Then
First = False
Else
newbk.Close SaveChanges:=True
End If
Workbooks.Add
Set newbk = ActiveWorkbook
CompanyName = Mid(ColAData, InStr(ColAData, "-") + 1)
newbk.SaveAs FileName:=Folder & CompanyName & ".xls"


Else
If ColAData <> "" Then
'this must be new worksheet
'check if sheet exists
Found = False
For Each sh In newbk.Sheets
If sh.Name = ColAData Then
Found = True
Exit For
End If
Next sh
If Found = False Then
newbk.Worksheets.Add after:= _
newbk.Sheets(newbk.Sheets.Count)
ActiveSheet.Name = ColAData
Range("A1") = "nr_sheeft"
Range("B1") = "name_employ"
.Range("C" & MemRowCount & ":I" & MemRowCount).Copy _
Range("C1")
NewRowCount = 2
End If
Else
'must be employee data
Range("A" & NewRowCount) = ActiveSheet.Name
.Range("B" & MemRowCount & ":I" & MemRowCount).Copy _
Range("B" & NewRowCount)
NewRowCount = NewRowCount + 1
End If
End If

Next MemRowCount
End With

newbk.Close SaveChanges:=True

Application.EnableEvents = True

End Sub
 
J

Joel

The name of the sheet with al the company names doesn't match the code. You
need to changge the statement below to match the sheet name where you have
all the companies data.

from
With ThisWorkbook.Sheets("Memory")
to
With ThisWorkbook.Sheets("XXXXXX")

where XXXX is the sheet name where you have all the company data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Bug in excel 2003 - trendline equation? 5
Outlook connector crashes outlook 0
Outlook crashes with Outlook connector 2002 0

Top