Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Creating an age or service spread chart | Charts and Charting in Excel | |||
I'm creating a simple spread sheet | Excel Discussion (Misc queries) | |||
creating a excel spread sheet using ffinancial functions | Excel Worksheet Functions | |||
Creating a linked spread sheet | Excel Worksheet Functions |