I have 3 macros where one macro calls two others.
I have pasted the code below but i am struggling with the CopyAndPasteSheet macro.
I have a button which runs the macro CreateDevicelList.
CreateDeviceList in turn calls CreateSheetsFomList.
CreateSheetsFromList creates new worksheets and names the new sheets based on the list on the List Sheet starting at C1.
Finally the data on the sheet Master is to be copied and pasted into each of the new sheets created in CreateSheetsFromList. Then delete rows 1 to 5.
Sub CreateDeviceList()
Application.Run "CreateSheetsFromAList"
Application.Run "CopyAndPasteSheet"
End Sub
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("List").Range("C1")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub
Sub CopyAndPasteSheet()
Sheets("Master").Select
Cells.Select
Selection.Copy
Sheets("List").Range("C1").Select
Cells.Select
ActiveSheet.Paste
Rows("1:5").Select
Range("A5").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
End Sub
Bookmarks