Hi Excel gurus
I made a 2016 workbook for task allocation for team requires the following Macros
First sheet (‘Task Allocation’) will have all the collated tasks. Then I have 6 sheets representing each team and then 40 sheets for individual team members.
My idea is that when my manager selects a team name in Column A in ‘Task Allocation’, the data from Column B to Column G ONLY to be copied and paste it to the individual ‘Team WiP’. Once, the task is allocated to the team, then the team leader should be able to allocate the task to individual team members. I have explained it further below
1) MACRO in the ‘Task Allocation’ sheet
Macro to copy the rows from Column B to Column G and paste it to the individual ‘Team WiP’ sheet as per selection from the drop down list from column A in Sheet-‘Task Allocation’.
The selection of rows starts from row 6 only
The entries in the ‘Task Allocation’ sheet will remain while it will be copied across to the relevant sheets as per selection from column A.
As an example, if I select ‘Team 1’ from the dropdown list, the entire data on that row from column B to G to be copied in to ‘Team1 WiP’ sheet. If there are more allocation to Team 1 from the Task allocation sheet, it would be copied to the next available row in the individual team sheet
2.Textbox macro for individual sheet
In each individual ‘Team WiP’ sheets, I added a text box to create a copy of that work sheet. Requesting a macro to create a new sheet coping only the values (not any formulas) from Column A to S. Is there a way I can avoid the text box being copied to the newly created sheet?
(I was using a macro for the earlier versions which is not working for 2016 excel, that creates and copies the sheet but it is copying the formulas and the textbox into the new sheet as well)
3). Textbox macro for the entire workbook
Requesting a macro to copy the entire workbook (all columns from all sheets) without any formulas.
The attached copy workbook has a different macro which I copied it from other source. However the macro is not working properly. Please have a look at the macros and let me know which one is the best suite and how to fix it
The macros I have are all from previous versions of excel for a similar task. But this is not working in my attached 2016 workbook.
Regards
maliec
I made a 2016 workbook for task allocation for team requires the following Macros
First sheet (‘Task Allocation’) will have all the collated tasks. Then I have 6 sheets representing each team and then 40 sheets for individual team members.
My idea is that when my manager selects a team name in Column A in ‘Task Allocation’, the data from Column B to Column G ONLY to be copied and paste it to the individual ‘Team WiP’. Once, the task is allocated to the team, then the team leader should be able to allocate the task to individual team members. I have explained it further below
1) MACRO in the ‘Task Allocation’ sheet
Macro to copy the rows from Column B to Column G and paste it to the individual ‘Team WiP’ sheet as per selection from the drop down list from column A in Sheet-‘Task Allocation’.
The selection of rows starts from row 6 only
The entries in the ‘Task Allocation’ sheet will remain while it will be copied across to the relevant sheets as per selection from column A.
As an example, if I select ‘Team 1’ from the dropdown list, the entire data on that row from column B to G to be copied in to ‘Team1 WiP’ sheet. If there are more allocation to Team 1 from the Task allocation sheet, it would be copied to the next available row in the individual team sheet
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A6:A" & Rows.Count)) Is Nothing And _
Target = "Team 1" Then
Application.EnableEvents = False
r = Target.Row
Rows(r).Copy Sheets("Team1 WiP").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Rows(r).Delete xlShiftUp
Application.EnableEvents = True
End If
End Sub2.Textbox macro for individual sheet
In each individual ‘Team WiP’ sheets, I added a text box to create a copy of that work sheet. Requesting a macro to create a new sheet coping only the values (not any formulas) from Column A to S. Is there a way I can avoid the text box being copied to the newly created sheet?
(I was using a macro for the earlier versions which is not working for 2016 excel, that creates and copies the sheet but it is copying the formulas and the textbox into the new sheet as well)
Code:
Sub copy_ Team1 WiP()
'
' copy_ Team1 WiP Macro
'
'
Sheets("Team1 WiP").Select
Sheets("Team1 WiP").Copy
End Sub3). Textbox macro for the entire workbook
Requesting a macro to copy the entire workbook (all columns from all sheets) without any formulas.
The attached copy workbook has a different macro which I copied it from other source. However the macro is not working properly. Please have a look at the macros and let me know which one is the best suite and how to fix it
The macros I have are all from previous versions of excel for a similar task. But this is not working in my attached 2016 workbook.
Regards
maliec
