Results 1 to 7 of 7

Thread: insert a sheet and rename it as that of the variable
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2013
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default insert a sheet and rename it as that of the variable

    i am copy pasting a table , with a variable , i want rename the new worksheet in the name of the variable

    for example


    Sub Tp6s()


    Sheets("Sheet1").Select
    Dim i As Range
    For Each i In Range("d5:d1000")
    If i.Value = Sheets("Pre-processing").Range("N10") Then
    i.Select
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("i").Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial


    End If

    Next i
    End Sub


    if i run this , im getting a error, im not able to solve this issue. help me please
    thanks

  2. #2
    MrExcel MVP Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: insert a sheet and rename it as that of the variable

    Like this?

    Code:
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = i
    Microsoft MVP - Excel

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,886
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: insert a sheet and rename it as that of the variable

    I'm not sure what you are actually doing with the code, but my guess is something like
    Code:
    Sub Tp6s()
    Dim i As Long
        For i = 5 To 1000
            On Error Resume Next
            If Cells(i, 4).Value = Sheets("Pre-processing").Cells(10, 14) Then
                Rows(i).Copy
                Sheets.Add After:=Sheets(Sheets.Count)
                With ActiveSheet
                    .Name = Sheets("Sheet1").Cells(i, 4).Value
                    .Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial
                End With
            End If
        Next i
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    New Member
    Join Date
    Oct 2013
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: insert a sheet and rename it as that of the variable

    Michael,
    Thank you very much, but If i run this, only the first row is getting copied, there are other rows with the same value
    hope you understand me now

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,886
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: insert a sheet and rename it as that of the variable

    Nope....I think you need to explain exactly what you want, so we can give you the best possible result.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    New Member
    Join Date
    Oct 2013
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: insert a sheet and rename it as that of the variable

    thanks Mr Andrew , But is there any way to do it without the sheet count, because im planning to use it multiple time (im new to VBA, so not good with loops ). every time the sheet count will change.

  7. #7
    MrExcel MVP Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: insert a sheet and rename it as that of the variable

    Microsoft MVP - Excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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