Create Excel Worksheet Variables And Use To Move Cell Values Between Sheets
The worksheet variable is useful to allow copying values from one worksheet to another without the necessity of switching between each worksheet to copy values.
Sample Program Code
Option Explicit
Public Sub FormatCodes()
Dim shtOriginal As Worksheet
Dim shtResults As Worksheet
Dim lngLastRow As Long
Dim intResultsRow As Long
Dim i As Long
Dim j As Long
' ***************************************************
' Set A Worksheet Variable
' ***************************************************
Set shtOriginal = ActiveSheet
' ***************************************************
' Add A Worksheet After The Very Last Worksheet
' ***************************************************
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Results"
' ***************************************************
' Set Worksheet Variable for New Worksheet
' ***************************************************
Set shtResults = Sheets("Results")
' ***************************************************
' Initialize New Worksheet Headings
' ***************************************************
shtResults.Cells(1, 1).Value = "EE Code"
shtResults.Cells(1, 2).Value = "EE Name"
shtResults.Cells(1, 3).Value = "Code"
shtResults.Cells(1, 4).Value = "Value"
' ***************************************************
' Find Last Row in Original Worksheet
' ***************************************************
lngLastRow = shtOriginal.Cells(Rows.Count, "A").End(xlUp).Row
intResultsRow = 1
' ***************************************************
' Move Values Between Sheets
' ***************************************************
For i = 2 To lngLastRow
For j = 3 To 6
If shtOriginal.Cells(i, j).Value <> "" Then
intResultsRow = intResultsRow + 1
shtResults.Cells(intResultsRow, 1).Value = shtOriginal.Cells(i, 1).Value
shtResults.Cells(intResultsRow, 2).Value = shtOriginal.Cells(i, 2).Value
Select Case j
Case 3
shtResults.Cells(intResultsRow, 3).Value = "Code_1"
Case 4
shtResults.Cells(intResultsRow, 3).Value = "Code_2"
Case 5
shtResults.Cells(intResultsRow, 3).Value = "Code_3"
Case 6
shtResults.Cells(intResultsRow, 3).Value = "Code_4"
End Select
shtResults.Cells(intResultsRow, 4).Value = shtOriginal.Cells(i, j).Value
End If
Next j
Next i
End Sub
