Results 1 to 2 of 2

Thread: Splittting Data Into Different Worksheets Column Wise In VBA

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    2
    Rep Power
    0

    Splittting Data Into Different Worksheets Column Wise In VBA

    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
    Last edited by Excel Fox; 01-22-2012 at 05:39 PM. Reason: Code Tag

  2. #2
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14
    Please attach a sample workbook........

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-30-2023 at 03:03 PM.

Similar Threads

  1. Replies: 34
    Last Post: 03-13-2015, 02:26 PM
  2. Replies: 17
    Last Post: 05-22-2013, 11:58 PM
  3. Replies: 2
    Last Post: 04-26-2013, 04:59 PM
  4. Replies: 9
    Last Post: 12-04-2012, 09:45 PM
  5. Splittting Data Into Different Worksheets Column Wise In VBA
    By rajeshd@live.in in forum Den Of The Fox
    Replies: 1
    Last Post: 01-21-2012, 04:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •