Results 1 to 11 of 11

Copy Destination

Threaded View

  1. #1
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Copy Destination

    Hi all,
    I am really only just getting started with VBA and have finally moved from the Macro recorder in some situations, but still my code is very messy. The code below works fine, but I am having trouble cleaning it up. I want to put in "Copy :=Destination" but I am not sure how to go about it. I was trying to use "Dim" so I could rename the worksheets to make things less confusing, as I can't get my head around how to use "Copy :=Destination" when changing sheets.

    I also am a bit stumped on the changing the font.

    Any help would be appreciated.

    Cheers

    Sub Name_Change()
    '
    ' Name_Change Macro
    '
       FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
       For i = 2 To FinalRow
       Dim strName As String
    '
        Sheets("労働者名簿").Select
        Sheets("労働者名簿").Copy After:=Sheets(2)
        Worksheets("職員").Cells(i, 3).Copy Worksheets("労働者名簿 (2)").Range("C6:G6")
        Worksheets("職員").Cells(i, 2).Copy Worksheets("労働者名簿 (2)").Range("C7:G9")
        Worksheets("職員").Cells(i, 4).Copy Worksheets("労働者名簿 (2)").Range("H7:H9")
        Worksheets("職員").Cells(i, 5).Copy Worksheets("労働者名簿 (2)").Range("C10:H10")
        Worksheets("職員").Cells(i, 8).Copy Worksheets("労働者名簿 (2)").Range("C11:H11")
        Worksheets("職員").Cells(i, 6).Copy Worksheets("労働者名簿 (2)").Range("C12:J12")
        Worksheets("職員").Cells(i, 7).Copy Worksheets("労働者名簿 (2)").Range("C13:J14")
        Worksheets("職員").Cells(i, 11).Copy Worksheets("労働者名簿 (2)").Range("C15:J15")
        Worksheets("職員").Cells(i, 16).Copy Worksheets("労働者名簿 (2)").Range("C16:D16")
        Worksheets("職員").Cells(i, 14).Copy Worksheets("労働者名簿 (2)").Range("H16:J16")
        Worksheets("職員").Cells(i, 13).Copy Worksheets("労働者名簿 (2)").Range("C17:J17")
        Worksheets("職員").Select
        strName = Cells(i, 19).Value
        Sheets("労働者名簿 (2)").Select
        Sheets("労働者名簿 (2)").Name = strName
        Sheets("辞令").Select
        Sheets("辞令").Copy After:=Sheets(2)
        Worksheets("職員").Cells(i, 2).Copy Worksheets("辞令 (2)").Range("C4")
        Worksheets("職員").Cells(i, 5).Copy Worksheets("辞令 (2)").Range("C5")
        Worksheets("職員").Cells(i, 13).Copy Worksheets("辞令 (2)").Range("C3")
        Worksheets("職員").Cells(i, 11).Copy Worksheets("辞令 (2)").Range("D12:F12")
        Range("C3:C5").Select
        With Selection.Font
            .Name = "HGP岸本楷書体"
            .Size = 20
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        Range("D12:F12").Select
        With Selection.Font
            .Name = "HGP岸本楷書体"
            .Size = 20
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        Worksheets("職員").Select
        strName = Cells(i, 2).Value
        Sheets("辞令 (2)").Select
        Sheets("辞令 (2)").Name = strName
        Worksheets("職員").Select
        Next i
    End Sub
    Last edited by JapanDave; 08-11-2011 at 06:27 AM.

Thread Information

Users Browsing this Thread

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

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