Results 1 to 7 of 7

help on microsoft excel

This is a discussion on help on microsoft excel within the Microsoft Excel forums, part of the Courses category; aslam o alaikum sir can any one guide me in solving this problem i have microsoft workbook which have four ...

  1. #1
    iTT Student
    Join Date
    Jul 2010
    Location
    Pakistan
    Age
    31
    Posts
    9

    help on microsoft excel

    aslam o alaikum sir
    can any one guide me in solving this problem
    i have microsoft workbook which have four worksheets being named as log book, private worksheet one, private worksheet two, and private worksheet three(all four worksheets have different kind of data).
    when in the cell B2 of first worksheet being named as log book i write private worksheet one, it should copy worksheet one and make total five worksheets, when i write private worksheet two in B3 of first worksheet being named as log book it should copy private worksheet two and make total six worksheets, when i write worksheet three in B4 of first worksheet being named as log book it should copy worksheet three and make total seven worksheet, and when i write all in B3 of first worksheet being named as log book it should copy all three worksheets and make total ten worksheets

  2. #2
    *I am Banned*
    Join Date
    Apr 2010
    Location
    lahore
    Age
    44
    Posts
    293

    Re: help on microsoft excel

    Open excel, hold down alt key and press F11
    paste the below code in VBA Editor

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo a
    Dim notime, shitno As Integer ' variable to store no of time sheets to be copied.
    If Sheet1.Range("B2").Value = "private worksheet one" Then 'if condition met
    notime = 5 'no of times to be copied
    For numtimes = 1 To notime ' for loop from 0 to n time as defined earlier
    ActiveWorkbook.Sheets(2).Copy _
    after:=ActiveWorkbook.Sheets("private worksheet one") ' sheet(2) need to be copied, after "private worksheet one"
    Next
    ElseIf Sheet1.Range("B3").Value = "private worksheet two" Then
    notime = 6
    For numtimes = 1 To notime
    ActiveWorkbook.Sheets(3).Copy _
    after:=ActiveWorkbook.Sheets("private worksheet two")
    Next
    ElseIf Sheet1.Range("B4").Value = "private worksheet three" Then
    notime = 7
    For numtimes = 1 To notime
    ActiveWorkbook.Sheets(4).Copy _
    after:=ActiveWorkbook.Sheets("private worksheet three")
    Next
    ElseIf Sheet1.Range("B2").Value = "all" Then
    notime = 10
    shitno = ActiveWorkbook.Sheets.Count
    For numtimes = 1 To notime
    ActiveWorkbook.Sheets(shitno).Copy _
    after:=ActiveWorkbook.Sheets("private worksheet three")
    Next
    End If
    Exit Sub
    a:
    End Sub

    save the workbook, try to change the value like the way you want from B2 to B4 and see what happen.
    Attached Files Attached Files

  3. #3
    *I am Banned*
    Join Date
    Apr 2010
    Location
    lahore
    Age
    44
    Posts
    293

    Re: help on microsoft excel

    sorry that code was not tested i was in hurry try this.

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo a
    Dim notime, shitno As Integer ' variable to store no of time sheets to be copied.
    If Sheet1.Range("B2").Value = "private worksheet one" Then 'if condition met
    notime = 4 'no of times to be copied
    sheetnum = 2
    For numtimes = 1 To notime ' for loop from 0 to n time as defined earlier
    ActiveWorkbook.Sheets(2).Copy _
    after:=ActiveWorkbook.Sheets(sheetnum)
    sheetnum = sheetnum + 1 ' sheet(2) need to be copied, after "private worksheet one"
    Next
    ElseIf Sheet1.Range("B3").Value = "private worksheet two" Then
    notime = 6
    sheetnum = 3
    For numtimes = 1 To notime
    ActiveWorkbook.Sheets(3).Copy _
    after:=ActiveWorkbook.Sheets(sheetnum)
    sheetnum = sheetnum + 1
    Next
    ElseIf Sheet1.Range("B4").Value = "private worksheet three" Then
    notime = 7
    sheetnum = 4
    For numtimes = 1 To notime
    ActiveWorkbook.Sheets(4).Copy _
    after:=ActiveWorkbook.Sheets(sheetnum)
    sheetnum = sheetnum + 1
    Next
    ElseIf Sheet1.Range("B2").Value = "all" Then
    notime = 10
    sheetnum = 2
    sheetnums = 2
    aa:
    If sheetnums >= 36 Then
    GoTo a
    Else
    For numtimes = 1 To notime
    ActiveWorkbook.Sheets(sheetnum).Copy _
    after:=ActiveWorkbook.Sheets(sheetnums)
    sheetnums = sheetnums + 1
    Next
    If sheetnums = 12 Then
    sheetnums = 2
    ElseIf sheetnums = 23 Then
    sheetnums = 13
    End If
    sheetnums = sheetnums + 11
    sheetnum = sheetnum + 11
    GoTo aa
    End If
    End If
    Exit Sub
    a:
    End Sub
    Attached Files Attached Files

  4. #4
    iTT Student
    Join Date
    Sep 2010
    Location
    pakistan
    Age
    31
    Posts
    24

    Thumbs up Re: help on microsoft excel

    NICE SHARE

  5. #5
    iTT Captain Rahi's Avatar
    Join Date
    Feb 2009
    Location
    Karachi
    Age
    61
    Posts
    1,198

    Re: help on microsoft excel

    بہت اچھے

  6. #6
    iTT Student
    Join Date
    Feb 2011
    Location
    Pakistan
    Age
    28
    Posts
    77

    Re: help on microsoft excel

    thanks

  7. #7
    iTT Student ztbl's Avatar
    Join Date
    Jun 2010
    Location
    pakistan
    Posts
    82

    Re: help on microsoft excel

    بہت خوب
    محبوب احمد

Similar Threads

  1. Microsoft Excel ( Last Class )
    By Jaan_leva in forum Microsoft Excel By Jaan Leva
    Replies: 33
    Last Post: 1st June 2013, 11:29 PM
  2. Microsoft Excel Class (16)
    By Jaan_leva in forum Microsoft Excel By Jaan Leva
    Replies: 31
    Last Post: 11th September 2012, 08:41 PM
  3. Microsoft Excel Class (15)
    By Jaan_leva in forum Microsoft Excel By Jaan Leva
    Replies: 21
    Last Post: 11th September 2012, 08:40 PM
  4. Microsoft Excel Class (14)
    By Jaan_leva in forum Microsoft Excel By Jaan Leva
    Replies: 14
    Last Post: 11th September 2012, 08:39 PM
  5. Microsoft Excel Class (12)
    By Jaan_leva in forum Microsoft Excel By Jaan Leva
    Replies: 12
    Last Post: 11th September 2012, 08:36 PM

Tags for this Thread

Posting Permissions

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