rajeshd@live.in
01-20-2012, 09:38 AM
Hi,
I have some employee data column wise in "master" sheet having "employee id" as respective column header.This workbook also contains 20 sheets naming each sheet a different employee id."master" sheet may contain 1 or more columns of same employee which can be identified by column id (which is a employee id). I want to copy each employees details given in the column ( may be one or more) into respective sheet.
I have created vba macro which runs perfectly if "master" sheet contains only one column per each employee. But, when i tried to tweak it to copy more than one column if employee id is available as header for more than one column, its not working. when i run the code it is neither throwing an error nor pasting anything.
I request you guys to go thru this code and suggest me any modifications to achieve desired result.
Sub separate()
Dim i As Long
Dim z As Long
Dim ziel As String
Dim statusAs String
Dim sheetname As String
statu= "no"
sheetname = "nothing"
Sheets("master").Select
Application.ScreenUpdating = False
'i copy the values into the new sheets
For i = 2 To Application.CountA(Sheets("master").Rows("1:1")) - 1
ziel = Sheets("master").Cells(1, i).Value
For n = 1 To Worksheets.Count
If Worksheets(n).name = ziel Then
status= "yes"
ssheet = ActiveSheet.name
End If
Next n
If status = "yes" Then
Sheets("master").Columns(i).Copy Sheets(sheetname).Range("a1").End.xlRight.Offset(0, 1).Paste
Sheets("master").Columns(1).Copy Sheets(ziel).Range("a1")
End If
If status = "no" Then
Sheets.Add
ActiveSheet.name = Sheets("master").Cells(1, z).Value
Sheets("master").Columns(i).Copy Sheets(ziel).Range("b1").Paste
Sheets("master").Columns(1).Copy Sheets(ziel).Range("a1")
End If
name = "no"
name = "nothing"
Next i
'Back to sheets master
Sheets("master").Select
Range("A1").Select
Application.ScreenUpdating = True
End Sub
I have some employee data column wise in "master" sheet having "employee id" as respective column header.This workbook also contains 20 sheets naming each sheet a different employee id."master" sheet may contain 1 or more columns of same employee which can be identified by column id (which is a employee id). I want to copy each employees details given in the column ( may be one or more) into respective sheet.
I have created vba macro which runs perfectly if "master" sheet contains only one column per each employee. But, when i tried to tweak it to copy more than one column if employee id is available as header for more than one column, its not working. when i run the code it is neither throwing an error nor pasting anything.
I request you guys to go thru this code and suggest me any modifications to achieve desired result.
Sub separate()
Dim i As Long
Dim z As Long
Dim ziel As String
Dim statusAs String
Dim sheetname As String
statu= "no"
sheetname = "nothing"
Sheets("master").Select
Application.ScreenUpdating = False
'i copy the values into the new sheets
For i = 2 To Application.CountA(Sheets("master").Rows("1:1")) - 1
ziel = Sheets("master").Cells(1, i).Value
For n = 1 To Worksheets.Count
If Worksheets(n).name = ziel Then
status= "yes"
ssheet = ActiveSheet.name
End If
Next n
If status = "yes" Then
Sheets("master").Columns(i).Copy Sheets(sheetname).Range("a1").End.xlRight.Offset(0, 1).Paste
Sheets("master").Columns(1).Copy Sheets(ziel).Range("a1")
End If
If status = "no" Then
Sheets.Add
ActiveSheet.name = Sheets("master").Cells(1, z).Value
Sheets("master").Columns(i).Copy Sheets(ziel).Range("b1").Paste
Sheets("master").Columns(1).Copy Sheets(ziel).Range("a1")
End If
name = "no"
name = "nothing"
Next i
'Back to sheets master
Sheets("master").Select
Range("A1").Select
Application.ScreenUpdating = True
End Sub