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
Bookmarks