CiderDrinker
New Member
- Joined
- Apr 26, 2013
- Messages
- 16
I have posted a question in the past, but, as my requirements are now considerably simplified, it is easier to start again from scratch.
I am working from the code below as a base. Its purpose is to select and copy the current row, insert 11 rows below it and paste in the contents of the previously copied row, then, in the fourth column, paste in the months January to December (this is for an attendance register).
What I need to do next is to duplicate the newly created content onto a number of other worksheets (for different venues) at the SAME POSITION on each new worksheet. This is crucial. The code above works only on a Master worksheet. Each new student is inserted at their relative position according to the alphabetical position of their name.
What I would like to do is amend the code to do as follows:
The last worksheet in the workbook, entitled CALCS, does not need any data inserted or pasted, so can be ignored for the purposes of the macro (presumably by an IF statement inside a FOR EACH loop).
Previous attempts to do this have hit the stumbling block of there needing to be only 11 rows inserted (below the current one), on the Master worksheet but 12 on each of the subsequent ones. I could get over this by inserting 12 in all and prompting for the data, then continue the macro but, for one I don't know how to do this and anyway it is probably an unnecessary complication.
To give an illustrative example, say I inserted a new student, lets call him Smith at row 100, the macro would take the details I filled in about Mr Smith in row 100, insert 11 new rows below this and paste it down into rows 101 to 111, then go to all the other worksheets (except CALCS), go to position 100 and insert 12 rows, pasting in the contents of rows 100 to 111 from the Master worksheet into each. Once it has done this in each worksheet (except CALCS) it should then return to row 100 on the Master sheet.
Row 100 is not a fixed position, and will vary for each student, but given above only to illustrate what I am seeking to achieve.
Your suggestions and input are welcome. Thank you for your attention.
I am working from the code below as a base. Its purpose is to select and copy the current row, insert 11 rows below it and paste in the contents of the previously copied row, then, in the fourth column, paste in the months January to December (this is for an attendance register).
Code:
Sub CopyDownStudent()
'
For i = 1 To 11
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
Next
For i = 0 To 11
ActiveCell.Offset(i, 3).Formula = "=DATE(YEAR(NOW())," & i + 1 & ",1)"
Next
End Sub
</pre>What I need to do next is to duplicate the newly created content onto a number of other worksheets (for different venues) at the SAME POSITION on each new worksheet. This is crucial. The code above works only on a Master worksheet. Each new student is inserted at their relative position according to the alphabetical position of their name.
What I would like to do is amend the code to do as follows:
- Select the current row and copy it
- Insert 11 rows below it
- Paste in the contents of the first row into each of the 11 new rows
- Insert the months January-December into all 12 new rows as per the current macro
- - Start a new loop at this point
- For each worksheet, except one entitled CALCS, duplicate the 12 newly created rows from the Master worksheet, inserting them at the same position in each of the other worksheets
The last worksheet in the workbook, entitled CALCS, does not need any data inserted or pasted, so can be ignored for the purposes of the macro (presumably by an IF statement inside a FOR EACH loop).
Previous attempts to do this have hit the stumbling block of there needing to be only 11 rows inserted (below the current one), on the Master worksheet but 12 on each of the subsequent ones. I could get over this by inserting 12 in all and prompting for the data, then continue the macro but, for one I don't know how to do this and anyway it is probably an unnecessary complication.
To give an illustrative example, say I inserted a new student, lets call him Smith at row 100, the macro would take the details I filled in about Mr Smith in row 100, insert 11 new rows below this and paste it down into rows 101 to 111, then go to all the other worksheets (except CALCS), go to position 100 and insert 12 rows, pasting in the contents of rows 100 to 111 from the Master worksheet into each. Once it has done this in each worksheet (except CALCS) it should then return to row 100 on the Master sheet.
Row 100 is not a fixed position, and will vary for each student, but given above only to illustrate what I am seeking to achieve.
Your suggestions and input are welcome. Thank you for your attention.
You, Sir, are a genius, a guru and a Godsend. That does exactly what I wanted to do and was provided so quickly too. Thank you very much. The Resize is not something I have come across before, so it has been educational as well. I am very grateful. Question resolved.
