+ Reply to Thread
Results 1 to 3 of 3

VBA loop copy/paste

Hybrid View

  1. #1
    Registered User
    Join Date
    05-15-2008
    Location
    Mt. Vernon, WA
    Posts
    7

    VBA loop copy/paste

    I've spent some time looking for answers online and can't seem to find a resolution to a particular problem.

    I have a spreadsheet and macro that takes info. from a master schedule and creates new tabs for each participant with their specific schedule listed. I execute this by having a "Template" tab which contains a generic form letter than can be individualized for each participant on the master schedule tab (which is already formatted for printing). If you input the participants name in M3, the form will populate with their schedule info.

    Currently, the code is looped to create a new tab for the participant in the top row of the master schedule, copy/paste the template form in the new sheet, and then generate the schedule for that student. Unfortunately, the formatting won't copy over:

    Do
    
    Dim newsht As Worksheet, ws As Worksheet
    Dim ivalue As String
    
    ivalue = Sheets("SUN Student Class Schedule").Range("C5").Value
    For Each ws In Worksheets
        If ws.Name = ivalue Then
            MsgBox ("There is already a sheet with the name " & "*" & ivalue & "*")
            Exit Sub
        End If
    Next ws
    
    Set newsht = Worksheets.Add
    With newsht
        .Move After:=Sheets(Sheets.Count)
        .Name = ivalue
    End With
    
    Sheets("Template").UsedRange.Copy Destination:=newsht.Range("A1")
    newsht.Range("M3").Value = ivalue
    Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Sheets("Template").UsedRange.Copy
    
    Sheets("SUN Student Class Schedule").Select
    Rows("5:5").Select
    Selection.Delete Shift:=xlUp
    
    Loop Until IsEmpty(ivalue) = True
    I believe what would work is to: create the newsht; generate the individual schedule on "Template"; copy/paste the schedule to newsht (so that formatting carries over); then copy/paste special (values) the individual schedule on newsht to get rid of the links to the master schedule; erase the name in "Template"; delete row 5 and start with the next row. Unfortunately, I don't know how to write that into the code!

    I appreciate any help and insight into this issue.
    Attached Files Attached Files
    Last edited by barrec; 09-06-2012 at 03:53 PM.

  2. #2
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: VBA loop copy/paste

    Here is some code you can add to your Module1 just below the End With statement:

    With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
        ActiveSheet.PageSetup.PrintArea = "$A$1:$K$65"
        With ActiveSheet.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .LeftMargin = Application.InchesToPoints(0.75)
            .RightMargin = Application.InchesToPoints(0.75)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.49)
            .HeaderMargin = Application.InchesToPoints(0.5)
            .FooterMargin = Application.InchesToPoints(0.5)
            .PrintQuality = 300
            .AlignMarginsHeaderFooter = False
        End With
    Click on the * icon if this post has been helpful.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,953

    Re: VBA loop copy/paste

    Sub StudentScheduleGenerator()
        
        Dim StudentAddList  As Range, _
            Student         As Range, _
            Schedule        As Worksheet
            
        Set Schedule = Sheets("sun student class schedule")
        
        With Schedule
            Set StudentAddList = .Range("c5:c" & .Cells(Rows.Count, "a").End(xlUp).Row)
        End With
        
        For Each Student In StudentAddList
            Sheets("Template").Copy After:=Sheets(Sheets.Count)
            With ActiveSheet
                .Name = Student
                .Range("M3").Value = Student
            End With
        Next Student
    End Sub
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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