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.
Code: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
Reply With Quote
Bookmarks