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
|