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,972
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,972
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,337
Messages
5,339,632
Members
399,316
Latest member
LeroyWork

Some videos you may like

This Week's Hot Topics

Top