Results 1 to 6 of 6

Copy and Paste sheet into a range of sheets from list

Threaded View

  1. #1
    Registered User
    Join Date
    06-29-2005
    Posts
    12

    Copy and Paste sheet into a range of sheets from list

    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
    Last edited by JBeaucaire; 07-19-2013 at 10:59 AM. Reason: Added CODE tags, as per Forum Rules. Take a moment to read the Forum Rules in the menu bar above. Thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] copy range from multiple sheets and paste to one sheet
    By David_Hiss in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-05-2013, 09:48 PM
  2. Copy range A:7 - Z200 from all sheets and paste into summary sheet
    By cheesiepoof05 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-06-2011, 05:45 PM
  3. Copy and paste range w/o selecting sheets
    By bg18461 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-05-2006, 09:01 PM
  4. Copy Range and Paste to Array of Sheets
    By bobwilson in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-06-2006, 08:23 AM
  5. [SOLVED] copy from one sheet and paste into other sheets
    By TUNGANA KURMA RAJU in forum Excel General
    Replies: 3
    Last Post: 12-08-2005, 11:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1