J
Jonah
I have tried to get this script to automate themaking of some
worksheets. i got it to work with RowIndex and the entries across the
sheet, but now would like it to be able to read down the sheet in column
A and to put the required texts into a Template sheet.
I can email or send more details if someone has an idea on this.
-----------
The script itself:-
Sub Button2_Click()
'Start at Column 1
ColumnIndex = 5
'Set up variables from Master List
Group = Worksheets("Master List").Cells(ColumnIndex, 4)
Null1 = Worksheets("Master List").Cells(ColumnIndex, 13)
Forename = Worksheets("Master List").Cells(ColumnIndex, 2)
Surname = Worksheets("Master List").Cells(ColumnIndex, 3)
Roll = Worksheets("Master List").Cells(ColumnIndex, 5)
GenderM = Worksheets("Master List").Cells(ColumnIndex, 6)
GenderF = Worksheets("Master List").Cells(ColumnIndex, 7)
Lender = Worksheets("Master List").Cells(ColumnIndex, 8)
G = Worksheets("Master List").Cells(ColumnIndex, 10)
B = Worksheets("Master List").Cells(ColumnIndex, 11)
A = Worksheets("Master List").Cells(ColumnIndex, 12)
Comment = Worksheets("Master List").Cells(ColumnIndex, 13)
'We want to loop down list until we find a blank
'We need all two bits of data to be there
Do While (Group <> "") And (Null1 <> "")
'Combine the two variables, to get a full name
FullName = Group + " " + Null1
'Copy the Sheet and name it as the child
Sheets("Lender Template").Select
Sheets("Lender Template").Copy After:=Sheets(2)
Sheets("Lender Template (2)").Name = FullName
'Put the Data into the sheet at appropriate points
'This is done on a co-ordinate basis
' (1,1) means cell A5
' (1,1) means cell A1 and (1,2) means B1 and (1,3) means C1
etc
' (x,y) means cell D5
Sheets(FullName).Cells(1, 1) = "Borrower: " + FullName
Sheets(FullName).Cells(1, 3) = "Group: " + Group
Sheets(FullName).Cells(1, 4) = "Roll: " + Roll
Sheets(FullName).Cells(1, 5) = "Gender: M: " +
GenderM
Sheets(FullName).Cells(2, 5) = " F: " +
GenderF
Sheets(FullName).Cells(1, 7) = "Lender: " + Lender
Sheets(FullName).Cells(32, 2) = "G: " + G
Sheets(FullName).Cells(3, 6) = "B: " + B
Sheets(FullName).Cells(3, 7) = "A: " + A
Sheets(FullName).Cells(23, 1) = Comment
'Move to next column and get new data
ColumnIndex = ColumnIndex - 1
Group = Worksheets("Master List").Cells(ColumnIndex, 4)
Null1 = Worksheets("Master List").Cells(ColumnIndex, 13)
Forename = Worksheets("Master List").Cells(ColumnIndex, 2)
Surname = Worksheets("Master List").Cells(ColumnIndex, 3)
Roll = Worksheets("Master List").Cells(ColumnIndex, 5)
GenderM = Worksheets("Master List").Cells(ColumnIndex, 6)
GenderF = Worksheets("Master List").Cells(ColumnIndex, 7)
Lender = Worksheets("Master List").Cells(ColumnIndex, 8)
G = Worksheets("Master List").Cells(ColumnIndex, 10)
B = Worksheets("Master List").Cells(ColumnIndex, 11)
A = Worksheets("Master List").Cells(ColumnIndex, 12)
Comment = Worksheets("Master List").Cells(ColumnIndex, 13)
Loop
End Sub
-----------------
Details of the Master Worksheet I use in .csv format:-
Column A
RUN REPORT,Planning Worksheet A01
Forename,Jim,Joseph,Jemma,Jean
Surname,Smithson,Smithson,Smithson,Smithson
Group,8U1,8U1,Monday 8U1,Monday 8U1,Monday
Roll,123,456,789,1023
Gender,Male,M,M
Gender,Female,F,F
Lender,8U1 Monday,7U2 Monday,7U2d Monday,8N3 Monday
Date,160505,160505,160505,170505
G,G1,G2,G3,G4
A,A1,A2,A3,A4
B,B1,B2,B3,B4
Comment,CommentA,CommentB,CommentC,CommentFinal
Null1,.,.,.,.
worksheets. i got it to work with RowIndex and the entries across the
sheet, but now would like it to be able to read down the sheet in column
A and to put the required texts into a Template sheet.
I can email or send more details if someone has an idea on this.
-----------
The script itself:-
Sub Button2_Click()
'Start at Column 1
ColumnIndex = 5
'Set up variables from Master List
Group = Worksheets("Master List").Cells(ColumnIndex, 4)
Null1 = Worksheets("Master List").Cells(ColumnIndex, 13)
Forename = Worksheets("Master List").Cells(ColumnIndex, 2)
Surname = Worksheets("Master List").Cells(ColumnIndex, 3)
Roll = Worksheets("Master List").Cells(ColumnIndex, 5)
GenderM = Worksheets("Master List").Cells(ColumnIndex, 6)
GenderF = Worksheets("Master List").Cells(ColumnIndex, 7)
Lender = Worksheets("Master List").Cells(ColumnIndex, 8)
G = Worksheets("Master List").Cells(ColumnIndex, 10)
B = Worksheets("Master List").Cells(ColumnIndex, 11)
A = Worksheets("Master List").Cells(ColumnIndex, 12)
Comment = Worksheets("Master List").Cells(ColumnIndex, 13)
'We want to loop down list until we find a blank
'We need all two bits of data to be there
Do While (Group <> "") And (Null1 <> "")
'Combine the two variables, to get a full name
FullName = Group + " " + Null1
'Copy the Sheet and name it as the child
Sheets("Lender Template").Select
Sheets("Lender Template").Copy After:=Sheets(2)
Sheets("Lender Template (2)").Name = FullName
'Put the Data into the sheet at appropriate points
'This is done on a co-ordinate basis
' (1,1) means cell A5
' (1,1) means cell A1 and (1,2) means B1 and (1,3) means C1
etc
' (x,y) means cell D5
Sheets(FullName).Cells(1, 1) = "Borrower: " + FullName
Sheets(FullName).Cells(1, 3) = "Group: " + Group
Sheets(FullName).Cells(1, 4) = "Roll: " + Roll
Sheets(FullName).Cells(1, 5) = "Gender: M: " +
GenderM
Sheets(FullName).Cells(2, 5) = " F: " +
GenderF
Sheets(FullName).Cells(1, 7) = "Lender: " + Lender
Sheets(FullName).Cells(32, 2) = "G: " + G
Sheets(FullName).Cells(3, 6) = "B: " + B
Sheets(FullName).Cells(3, 7) = "A: " + A
Sheets(FullName).Cells(23, 1) = Comment
'Move to next column and get new data
ColumnIndex = ColumnIndex - 1
Group = Worksheets("Master List").Cells(ColumnIndex, 4)
Null1 = Worksheets("Master List").Cells(ColumnIndex, 13)
Forename = Worksheets("Master List").Cells(ColumnIndex, 2)
Surname = Worksheets("Master List").Cells(ColumnIndex, 3)
Roll = Worksheets("Master List").Cells(ColumnIndex, 5)
GenderM = Worksheets("Master List").Cells(ColumnIndex, 6)
GenderF = Worksheets("Master List").Cells(ColumnIndex, 7)
Lender = Worksheets("Master List").Cells(ColumnIndex, 8)
G = Worksheets("Master List").Cells(ColumnIndex, 10)
B = Worksheets("Master List").Cells(ColumnIndex, 11)
A = Worksheets("Master List").Cells(ColumnIndex, 12)
Comment = Worksheets("Master List").Cells(ColumnIndex, 13)
Loop
End Sub
-----------------
Details of the Master Worksheet I use in .csv format:-
Column A
RUN REPORT,Planning Worksheet A01
Forename,Jim,Joseph,Jemma,Jean
Surname,Smithson,Smithson,Smithson,Smithson
Group,8U1,8U1,Monday 8U1,Monday 8U1,Monday
Roll,123,456,789,1023
Gender,Male,M,M
Gender,Female,F,F
Lender,8U1 Monday,7U2 Monday,7U2d Monday,8N3 Monday
Date,160505,160505,160505,170505
G,G1,G2,G3,G4
A,A1,A2,A3,A4
B,B1,B2,B3,B4
Comment,CommentA,CommentB,CommentC,CommentFinal
Null1,.,.,.,.