For Next Loop Function for Creating New Spread sheets
 

Dear All,
I am having a Master Workbook from which I've to create a new book having
"n" number of sheets.
Let me explain clearly, In my master workbook ,there are 2 sheets , one is
a Spread sheet which is a standard format. I am also having one more sheet in
which totally 5 columns are there. My users will enter the data in those 5
columns (A1:A30, B1:B30.....E1:E30).
I'm creating a command button in the program.
Now I'm creating a VB Coding for the button in such a way that it should
copy the Standard format sheet five times. Data in First column of sheet has
to be copied/pasted to First copy of standard format sheet , Second column
data sheets should be copied/ pasted to second copy of Sandard format sheet
and it will be repaeted upto 5 sheets.
I enterd coding as follow,
For I = 1 to 5
Worksheets("Std Format").Activate
Cells("A1").value = Worksheets("Tech sheets").Cells(1,(I+2))
Cells("A2").value = Worksheets("Tech.sheets").Cells(2,(I+2))
.....
....
....
Cells("A30").value = Worksheets("Tech.sheets").Cells(30,(I+2))
Activesheet.Cells.Select
Selection.Copy
Windows (newfile).Activate
Worksheets.add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Windows(myfile).Activate
Next I

Is it the correct way of coding for the above procedure I mentioned?
Please correct me if I am wrong. Also Please suggest me if there is any
other better options for sdoing the above activity.
Hope it is clear.

For Next Loop Function for Creating New Spread sheets
 

Try this where your data is in sheet1 of the active workbook.

Sub makefivesheetsfromfivecols()
For i = 1 To 5
Sheets.Add after:=ActiveSheet
ActiveSheet.Columns(i).Value = _
Sheets("sheet1").Columns(i).Value
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Premanand Sethuraman" wrote
in message ...
Dear All,
I am having a Master Workbook from which I've to create a new book having
"n" number of sheets.
Let me explain clearly, In my master workbook ,there are 2 sheets , one
is
a Spread sheet which is a standard format. I am also having one more sheet
in
which totally 5 columns are there. My users will enter the data in those 5
columns (A1:A30, B1:B30.....E1:E30).
I'm creating a command button in the program.
Now I'm creating a VB Coding for the button in such a way that it should
copy the Standard format sheet five times. Data in First column of sheet
has
to be copied/pasted to First copy of standard format sheet , Second
column
data sheets should be copied/ pasted to second copy of Sandard format
sheet
and it will be repaeted upto 5 sheets.
I enterd coding as follow,
For I = 1 to 5
Worksheets("Std Format").Activate
Cells("A1").value = Worksheets("Tech sheets").Cells(1,(I+2))
Cells("A2").value = Worksheets("Tech.sheets").Cells(2,(I+2))
....
...
...
Cells("A30").value = Worksheets("Tech.sheets").Cells(30,(I+2))
Activesheet.Cells.Select
Selection.Copy
Windows (newfile).Activate
Worksheets.add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Windows(myfile).Activate
Next I

Is it the correct way of coding for the above procedure I mentioned?
Please correct me if I am wrong. Also Please suggest me if there is any
other better options for sdoing the above activity.
Hope it is clear.

For Next Loop Function for Creating New Spread sheets
 

Dear Guillett,
Thanks for your suggestions. This is the one I really wanted.
Simple Coding.
Thanks
Prem.
"Don Guillett" wrote:

Try this where your data is in sheet1 of the active workbook.

Sub makefivesheetsfromfivecols()
For i = 1 To 5
Sheets.Add after:=ActiveSheet
ActiveSheet.Columns(i).Value = _
Sheets("sheet1").Columns(i).Value
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Premanand Sethuraman" wrote
in message ...
Dear All,
I am having a Master Workbook from which I've to create a new book having
"n" number of sheets.
Let me explain clearly, In my master workbook ,there are 2 sheets , one
is
a Spread sheet which is a standard format. I am also having one more sheet
in
which totally 5 columns are there. My users will enter the data in those 5
columns (A1:A30, B1:B30.....E1:E30).
I'm creating a command button in the program.
Now I'm creating a VB Coding for the button in such a way that it should
copy the Standard format sheet five times. Data in First column of sheet
has
to be copied/pasted to First copy of standard format sheet , Second
column
data sheets should be copied/ pasted to second copy of Sandard format
sheet
and it will be repaeted upto 5 sheets.
I enterd coding as follow,
For I = 1 to 5
Worksheets("Std Format").Activate
Cells("A1").value = Worksheets("Tech sheets").Cells(1,(I+2))
Cells("A2").value = Worksheets("Tech.sheets").Cells(2,(I+2))
....
...
...
Cells("A30").value = Worksheets("Tech.sheets").Cells(30,(I+2))
Activesheet.Cells.Select
Selection.Copy
Windows (newfile).Activate
Worksheets.add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Windows(myfile).Activate
Next I

Is it the correct way of coding for the above procedure I mentioned?
Please correct me if I am wrong. Also Please suggest me if there is any
other better options for sdoing the above activity.
Hope it is clear.