insert a sheet and rename it as that of the variable

prasadyanz

New Member
Joined
Oct 21, 2013
Messages
17
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,977
Office Version
2013
Platform
Windows
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
 

prasadyanz

New Member
Joined
Oct 21, 2013
Messages
17
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,977
Office Version
2013
Platform
Windows
Nope....I think you need to explain exactly what you want, so we can give you the best possible result.
 

prasadyanz

New Member
Joined
Oct 21, 2013
Messages
17
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.
 

Forum statistics

Threads
1,078,520
Messages
5,340,931
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top