For Next Loop Function for Creating New Spread sheets

[Premanand Sethuraman]

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.

 

[Don Guillett]

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

 

[Premanand Sethuraman]

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