create dynamic vba script for new sheets for each row from main sheet with name

ganu learner

New Member
Joined
Dec 31, 2019
Messages
47
Office Version
  1. 2013
Platform
  1. Windows
res sir I record a micro for 3 new sheets create from main sheet is below.

now I want new sheets create dynamically acording to main sheet. in which row 1:1 is header so in every sheet first row is common after that new sheet for every row according to main sheet and also with name according column A .

recorded micro is below here thanks

Sub Macro1()
Range("A1").Select
Sheets.Add After:=ActiveSheet
Sheets("NiftyBank MW").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet1").Select
Rows("1:1").Select
ActiveSheet.Paste
Sheets("NiftyBank MW").Select
Sheets("NiftyBank MW").Name = "NiftyBank MW"
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Sheet1"
Rows("2:2").Select
ActiveSheet.Paste
Sheets.Add After:=ActiveSheet
Sheets("NiftyBank MW").Select
Sheets("NiftyBank MW").Name = "NiftyBank MW"
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Rows("1:1").Select
ActiveSheet.Paste
ActiveSheet.Paste
Sheets("NiftyBank MW").Select
Rows("3:3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
ActiveSheet.Paste
Sheets.Add After:=ActiveSheet
Sheets("Sheet3").Name = "Sheet3"
Sheets("NiftyBank MW").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Rows("1:1").Select
ActiveSheet.Paste
Sheets("NiftyBank MW").Select
Rows("4:4").Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Rows("2:2").Select
ActiveSheet.Paste
Sheets("NiftyBank MW").Select
Range("A1").Select
End Sub

 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
How about
VBA Code:
Sub ganulearner()
   Dim Hdr As Variant
   Dim Cl As Range
   
   With Sheets("NiftyBank MW")
      Hdr = .Range("1:1").Value
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Sheets.Add(, Sheets(.Index)).Name = Cl.Value
         Range("1:1").Value = Hdr
         Cl.EntireRow.Copy Range("A2")
      Next Cl
   End With
End Sub
This assumes that the values in col A are unique & are valid sheet names
 
Upvote 0
How about
VBA Code:
Sub ganulearner()
   Dim Hdr As Variant
   Dim Cl As Range
  
   With Sheets("NiftyBank MW")
      Hdr = .Range("1:1").Value
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Sheets.Add(, Sheets(.Index)).Name = Cl.Value
         Range("1:1").Value = Hdr
         Cl.EntireRow.Copy Range("A2")
      Next Cl
   End With
End Sub
This assumes that the values in col A are unique & are valid sheet names

Wow working fine
Res Sir Great thanks to you.
 
Upvote 0
Wow working fine
Res Sir Great thanks to you.
Res Sir, please merge above script with one more vba script which is below

I want that this below vba script run for all new sheet which is created from main sheet
Private Sub Worksheet_Calculate()
Dim capturerow As Long, currow As Long, col As String
On Error GoTo handerror

Application.EnableEvents = False
capturerow = 2
currow = Range("A65536").End(xlUp).Row
If currow < 5 Then currow = 5

Cells(currow + 1, 1) = Cells(capturerow, 1)
Cells(currow + 1, 2) = Cells(capturerow, 2)
Cells(currow + 1, 3) = Cells(capturerow, 3)
Cells(currow + 1, 4) = Cells(capturerow, 4)
If currow > 5 Then
If Cells(currow, "B") > Cells(currow + 1, "B") Then
col = "E"
ElseIf Cells(currow, "B") < Cells(currow + 1, "B") Then
col = "F"
Else
col = "G"
End If
Cells(currow, col) = Cells(currow + 1, "C") - Cells(currow, "C")
End If
Range("E4").Value = WorksheetFunction.Sum(Range("E5:E" & currow))
Range("F4").Value = WorksheetFunction.Sum(Range("F5:F" & currow))
Range("G4").Value = WorksheetFunction.Sum(Range("G5:G" & currow))
handerror:
Application.EnableEvents = True
End Sub
 
Upvote 0
As this is a different question, it needs a new thread. Thanks
 
Upvote 0
@ganu learner
For future reference
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

cross posted
 
Upvote 0
@ganu learner
For future reference
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

cross posted
Ok sir sorry for this I will never try to do so
 
Upvote 0
res sir I tried above programme with my dynamic sheet .
its not working with this sheet.
but if I run this code with module or micro its run fine . please see why not its working with dynamic sheet I send my file to you with vba script thanks


Cell Formulas
RangeFormula
B2B2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "Last")
C2C2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "BidSize")
D2D2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "Bid")
E2E2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "Ask")
F2F2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "AskSize")
G2G2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "LTQ")
H2H2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "Open")
I2I2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "High")
J2J2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "Low")
K2K2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "PrevClose")
L2L2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "Volume")
M2M2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "OpenInterest")
N2N2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "AverageTradePrice")
O2O2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "TotalBidQty")
P2P2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "TotalAskQty")
Q2Q2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "Exchange")
R2R2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "lastTradeTime")
S2S2=RTD("pi.rtdserver", ,"NSE_BPCL-EQ", "lastUpdateTime")
B3B3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "Last")
C3C3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "BidSize")
D3D3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "Bid")
E3E3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "Ask")
F3F3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "AskSize")
G3G3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "LTQ")
H3H3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "Open")
I3I3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "High")
J3J3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "Low")
K3K3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "PrevClose")
L3L3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "Volume")
M3M3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "OpenInterest")
N3N3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "AverageTradePrice")
O3O3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "TotalBidQty")
P3P3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "TotalAskQty")
Q3Q3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "Exchange")
R3R3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "lastTradeTime")
S3S3=RTD("pi.rtdserver", ,"NSE_HINDPETRO-EQ", "lastUpdateTime")
B4B4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "Last")
C4C4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "BidSize")
D4D4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "Bid")
E4E4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "Ask")
F4F4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "AskSize")
G4G4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "LTQ")
H4H4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "Open")
I4I4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "High")
J4J4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "Low")
K4K4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "PrevClose")
L4L4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "Volume")
M4M4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "OpenInterest")
N4N4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "AverageTradePrice")
O4O4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "TotalBidQty")
P4P4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "TotalAskQty")
Q4Q4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "Exchange")
R4R4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "lastTradeTime")
S4S4=RTD("pi.rtdserver", ,"NSE_IOC-EQ", "lastUpdateTime")
B5B5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "Last")
C5C5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "BidSize")
D5D5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "Bid")
E5E5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "Ask")
F5F5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "AskSize")
G5G5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "LTQ")
H5H5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "Open")
I5I5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "High")
J5J5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "Low")
K5K5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "PrevClose")
L5L5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "Volume")
M5M5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "OpenInterest")
N5N5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "AverageTradePrice")
O5O5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "TotalBidQty")
P5P5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "TotalAskQty")
Q5Q5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "Exchange")
R5R5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "lastTradeTime")
S5S5=RTD("pi.rtdserver", ,"NSE_NTPC-EQ", "lastUpdateTime")
B6B6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "Last")
C6C6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "BidSize")
D6D6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "Bid")
E6E6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "Ask")
F6F6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "AskSize")
G6G6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "LTQ")
H6H6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "Open")
I6I6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "High")
J6J6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "Low")
K6K6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "PrevClose")
L6L6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "Volume")
M6M6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "OpenInterest")
N6N6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "AverageTradePrice")
O6O6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "TotalBidQty")
P6P6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "TotalAskQty")
Q6Q6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "Exchange")
R6R6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "lastTradeTime")
S6S6=RTD("pi.rtdserver", ,"NSE_ONGC-EQ", "lastUpdateTime")
B7B7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "Last")
C7C7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "BidSize")
D7D7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "Bid")
E7E7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "Ask")
F7F7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "AskSize")
G7G7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "LTQ")
H7H7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "Open")
I7I7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "High")
J7J7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "Low")
K7K7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "PrevClose")
L7L7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "Volume")
M7M7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "OpenInterest")
N7N7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "AverageTradePrice")
O7O7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "TotalBidQty")
P7P7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "TotalAskQty")
Q7Q7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "Exchange")
R7R7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "lastTradeTime")
S7S7=RTD("pi.rtdserver", ,"NSE_POWERGRID-EQ", "lastUpdateTime")
B8B8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "Last")
C8C8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "BidSize")
D8D8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "Bid")
E8E8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "Ask")
F8F8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "AskSize")
G8G8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "LTQ")
H8H8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "Open")
I8I8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "High")
J8J8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "Low")
K8K8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "PrevClose")
L8L8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "Volume")
M8M8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "OpenInterest")
N8N8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "AverageTradePrice")
O8O8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "TotalBidQty")
P8P8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "TotalAskQty")
Q8Q8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "Exchange")
R8R8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "lastTradeTime")
S8S8=RTD("pi.rtdserver", ,"NSE_RELIANCE-EQ", "lastUpdateTime")
B9B9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "Last")
C9C9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "BidSize")
D9D9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "Bid")
E9E9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "Ask")
F9F9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "AskSize")
G9G9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "LTQ")
H9H9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "Open")
I9I9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "High")
J9J9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "Low")
K9K9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "PrevClose")
L9L9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "Volume")
M9M9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "OpenInterest")
N9N9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "AverageTradePrice")
O9O9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "TotalBidQty")
P9P9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "TotalAskQty")
Q9Q9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "Exchange")
R9R9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "lastTradeTime")
S9S9=RTD("pi.rtdserver", ,"NSE_TATAPOWER-EQ", "lastUpdateTime")
 
Upvote 0

Forum statistics

Threads
1,214,833
Messages
6,121,869
Members
449,054
Latest member
juliecooper255

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