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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,402
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
18,402
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,089,622
Messages
5,409,381
Members
403,260
Latest member
ssauk

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top