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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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
 
Upvote 0
Nope....I think you need to explain exactly what you want, so we can give you the best possible result.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top