|
Simplify this macro
Afternoon,
Can anybody help to simplify this macro. Instead of all the sheet names, possible to either group them or name range them? Application.Run "Template.xls!CreateSheets.CreateSheets" Sheets("Template").Select Cells.Select Selection.Copy Sheets("Buda, S (LD002)").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets(Array("Buda, S (LD002)", "Chakela, P (LD075)", "Chauke, H (LD011)", _ "Dube, T (D838)", "Kasoka, T (D837)", "Ketsiwe, L (LD097)", "Koatsane, G (LD105)", _ "Lebusa, T (LD103)", "Lekgowane, H (VD9027)", "Lombard, P (LD096)", _ "Mabilo, S (LD045A)", "Mahlangu, B (LD067)", "Mahlangu, M (LD021)", _ "Mahloko, J (LD098)", "Makhongoana, J (LD077)", "Makurumidze, C (LD108)", _ "Malatji, D (LD100)", "Malaza, D (LD085)", "Malindi, A (LD039)", _ "Mangena, P (LD051)", "Mankwane, N (LD102)", "Maphanga, J (LD070)", _ "Maphanga, M (LD005)", "Maseko, D (D448)", "Mashaba, M (LD033)")).Select Sheets("Buda, S (LD002)").Activate Sheets(Array("Masondo, J (LD069)", "Mathe, N (LD049)", "Mathebula, V (LD054)", _ "Mathumba, J (LD053)", "Mbongo, L (LD095)", "Mhlongo, D (LD072)", _ "Mkhondo, F (LD086)", "Mkhwanazi, S (D832)", "Mlawuza, F (D821)", _ "Mohlala, J (LD015)", "Mohlala, N (LD104)", "Moloi, M (D681)", "Moloi, M (D714)", _ "Myeni, P (LD109)", "Ndala, J (LD112)", "Ndhlovu, M (D831)", "Ndlovu, R (LD084)", _ "Ngakane, P (LD030)", "Ngodela, I (LD107)", "Ngoma, G (LD065)", _ "Nhlapo, M (LD080)", "Nkosi, B (LD079)", "Nonyane, O (LD111)", "Phiri, O (LD087)", _ "Phora, P (LD106)")).Select Replace:=False Sheets(Array("Radebe, R (LD094)", "Sangweni, Z (LD060)", "Sebata, M (LD058)", _ "Sebolai, S (LD082)", "Segonyela, W (LD025)", "Serobedi, M (LD071)", _ "Sithole, S (LD083)", "Skhosana, B (VD9056)", "Thabasine, G (LD008)", _ "Tsotetsi, W (LD068)", "Vubu, T (D843)", "Woite, G (LD089)", "Zakwe, Z (VD9025)", _ "Zulu, N (LD055)", "Gidinabokao, T (LD116)", "Lukhele, L (LD115)", _ "Mabena, C (LD113)", "Malebe, C (LD119)", "Maluka, V (LD121)", _ "Malumane, C (LD123)", "Maposa, M (LD122)", "Masango, M (LD118)", _ "Masia, L (LD120)", "Mathenjwa, S (LD117)", "Mogokane, M (LD114)")).Select _ Replace:=False Cells.Select ActiveSheet.Paste ActiveSheet.Paste ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Parameters").Select Application.CutCopyMode = False End Sub And also there is no guarantee that Sheet Buda, S (LD002) will always be the first one selected. Thanks |
Simplify this macro
|
Simplify this macro
Hi
You could avoid naming all the sheets as your array if you used code like the following. This assumes that you want the data copied to all sheets in the workbook, other than the sheet Template Sub test() Dim ws As Worksheet For Each ws In Worksheets If ws.Name < "Template" Then Sheets("Sheet1").Cells.Copy _ ws.Range("A1") End If Next End Sub If there are other sheets to be excluded, then add them to the IF test -- Regards Roger Govier "Sunnyskies" wrote in message ... Morning Don, Do I need to list each of the sheets in command line: ma = Array("sheet2", "sheet4") ?? Thanks "Don Guillett" wrote: You may like this idea that does NOT involve any selections. Sub copytosheets() ma = Array("sheet2", "sheet4") For Each sh In ma Sheets("sheet1").Range("a2:b4").Copy _ Sheets(sh).Range("h1") Next sh End Sub -- Don Guillett SalesAid Software "Sunnyskies" wrote in message ... Afternoon, Can anybody help to simplify this macro. Instead of all the sheet names, possible to either group them or name range them? Application.Run "Template.xls!CreateSheets.CreateSheets" Sheets("Template").Select Cells.Select Selection.Copy Sheets("Buda, S (LD002)").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets(Array("Buda, S (LD002)", "Chakela, P (LD075)", "Chauke, H (LD011)", _ "Dube, T (D838)", "Kasoka, T (D837)", "Ketsiwe, L (LD097)", "Koatsane, G (LD105)", _ "Lebusa, T (LD103)", "Lekgowane, H (VD9027)", "Lombard, P (LD096)", _ "Mabilo, S (LD045A)", "Mahlangu, B (LD067)", "Mahlangu, M (LD021)", _ "Mahloko, J (LD098)", "Makhongoana, J (LD077)", "Makurumidze, C (LD108)", _ "Malatji, D (LD100)", "Malaza, D (LD085)", "Malindi, A (LD039)", _ "Mangena, P (LD051)", "Mankwane, N (LD102)", "Maphanga, J (LD070)", _ "Maphanga, M (LD005)", "Maseko, D (D448)", "Mashaba, M (LD033)")).Select Sheets("Buda, S (LD002)").Activate Sheets(Array("Masondo, J (LD069)", "Mathe, N (LD049)", "Mathebula, V (LD054)", _ "Mathumba, J (LD053)", "Mbongo, L (LD095)", "Mhlongo, D (LD072)", _ "Mkhondo, F (LD086)", "Mkhwanazi, S (D832)", "Mlawuza, F (D821)", _ "Mohlala, J (LD015)", "Mohlala, N (LD104)", "Moloi, M (D681)", "Moloi, M (D714)", _ "Myeni, P (LD109)", "Ndala, J (LD112)", "Ndhlovu, M (D831)", "Ndlovu, R (LD084)", _ "Ngakane, P (LD030)", "Ngodela, I (LD107)", "Ngoma, G (LD065)", _ "Nhlapo, M (LD080)", "Nkosi, B (LD079)", "Nonyane, O (LD111)", "Phiri, O (LD087)", _ "Phora, P (LD106)")).Select Replace:=False Sheets(Array("Radebe, R (LD094)", "Sangweni, Z (LD060)", "Sebata, M (LD058)", _ "Sebolai, S (LD082)", "Segonyela, W (LD025)", "Serobedi, M (LD071)", _ "Sithole, S (LD083)", "Skhosana, B (VD9056)", "Thabasine, G (LD008)", _ "Tsotetsi, W (LD068)", "Vubu, T (D843)", "Woite, G (LD089)", "Zakwe, Z (VD9025)", _ "Zulu, N (LD055)", "Gidinabokao, T (LD116)", "Lukhele, L (LD115)", _ "Mabena, C (LD113)", "Malebe, C (LD119)", "Maluka, V (LD121)", _ "Malumane, C (LD123)", "Maposa, M (LD122)", "Masango, M (LD118)", _ "Masia, L (LD120)", "Mathenjwa, S (LD117)", "Mogokane, M (LD114)")).Select _ Replace:=False Cells.Select ActiveSheet.Paste ActiveSheet.Paste ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Parameters").Select Application.CutCopyMode = False End Sub And also there is no guarantee that Sheet Buda, S (LD002) will always be the first one selected. Thanks |
Simplify this macro
Afternoon Roger,
Looking at your macro test, how would you add this to the macro I have at the bottom of this message. Take into account that I need to copy sheet 'Template' to the new sheets created. Sheet 'Template' has got formulas linking to another sheet. Thanks "Roger Govier" wrote: Hi You could avoid naming all the sheets as your array if you used code like the following. This assumes that you want the data copied to all sheets in the workbook, other than the sheet Template Sub test() Dim ws As Worksheet For Each ws In Worksheets If ws.Name < "Template" Then Sheets("Sheet1").Cells.Copy _ ws.Range("A1") End If Next End Sub If there are other sheets to be excluded, then add them to the IF test -- Regards Roger Govier "Sunnyskies" wrote in message ... Morning Don, Do I need to list each of the sheets in command line: ma = Array("sheet2", "sheet4") ?? Thanks "Don Guillett" wrote: You may like this idea that does NOT involve any selections. Sub copytosheets() ma = Array("sheet2", "sheet4") For Each sh In ma Sheets("sheet1").Range("a2:b4").Copy _ Sheets(sh).Range("h1") Next sh End Sub -- Don Guillett SalesAid Software "Sunnyskies" wrote in message ... Afternoon, Can anybody help to simplify this macro. Instead of all the sheet names, possible to either group them or name range them? Application.Run "Template.xls!CreateSheets.CreateSheets" Sheets("Template").Select Cells.Select Selection.Copy Sheets("Buda, S (LD002)").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets(Array("Buda, S (LD002)", "Chakela, P (LD075)", "Chauke, H (LD011)", _ "Dube, T (D838)", "Kasoka, T (D837)", "Ketsiwe, L (LD097)", "Koatsane, G (LD105)", _ "Lebusa, T (LD103)", "Lekgowane, H (VD9027)", "Lombard, P (LD096)", _ "Mabilo, S (LD045A)", "Mahlangu, B (LD067)", "Mahlangu, M (LD021)", _ "Mahloko, J (LD098)", "Makhongoana, J (LD077)", "Makurumidze, C (LD108)", _ "Malatji, D (LD100)", "Malaza, D (LD085)", "Malindi, A (LD039)", _ "Mangena, P (LD051)", "Mankwane, N (LD102)", "Maphanga, J (LD070)", _ "Maphanga, M (LD005)", "Maseko, D (D448)", "Mashaba, M (LD033)")).Select Sheets("Buda, S (LD002)").Activate Sheets(Array("Masondo, J (LD069)", "Mathe, N (LD049)", "Mathebula, V (LD054)", _ "Mathumba, J (LD053)", "Mbongo, L (LD095)", "Mhlongo, D (LD072)", _ "Mkhondo, F (LD086)", "Mkhwanazi, S (D832)", "Mlawuza, F (D821)", _ "Mohlala, J (LD015)", "Mohlala, N (LD104)", "Moloi, M (D681)", "Moloi, M (D714)", _ "Myeni, P (LD109)", "Ndala, J (LD112)", "Ndhlovu, M (D831)", "Ndlovu, R (LD084)", _ "Ngakane, P (LD030)", "Ngodela, I (LD107)", "Ngoma, G (LD065)", _ "Nhlapo, M (LD080)", "Nkosi, B (LD079)", "Nonyane, O (LD111)", "Phiri, O (LD087)", _ "Phora, P (LD106)")).Select Replace:=False Sheets(Array("Radebe, R (LD094)", "Sangweni, Z (LD060)", "Sebata, M (LD058)", _ "Sebolai, S (LD082)", "Segonyela, W (LD025)", "Serobedi, M (LD071)", _ "Sithole, S (LD083)", "Skhosana, B (VD9056)", "Thabasine, G (LD008)", _ "Tsotetsi, W (LD068)", "Vubu, T (D843)", "Woite, G (LD089)", "Zakwe, Z (VD9025)", _ "Zulu, N (LD055)", "Gidinabokao, T (LD116)", "Lukhele, L (LD115)", _ "Mabena, C (LD113)", "Malebe, C (LD119)", "Maluka, V (LD121)", _ "Malumane, C (LD123)", "Maposa, M (LD122)", "Masango, M (LD118)", _ "Masia, L (LD120)", "Mathenjwa, S (LD117)", "Mogokane, M (LD114)")).Select _ Replace:=False Cells.Select ActiveSheet.Paste ActiveSheet.Paste ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Parameters").Select Application.CutCopyMode = False End Sub And also there is no guarantee that Sheet Buda, S (LD002) will always be the first one selected. Thanks |
| All times are GMT +1. The time now is 05:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com