Simplify this macro

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

Morning Don,

Do I need to list each of the sheets in command line:
ma = Array("sheet2", "sheet4")
??

Thanks

 

[Roger Govier]

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

 

[Guest]

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