Long VBA Code - Can it be reduced
Have you thought of using an array ?
"Nm" wrote:
Hi
I have created the following VBA code, all it does is to check if data
is present in a column (either A, B, C) and then copy it in a separate
sheet in the same file.
I am sure there must be a way to do it so that code is shorter than
what I have.
Please make suggestions as I cant comeup with anything right now.
Thanks,
Naeem
-------------------------------------------------
If Sheet1.Range("A1") = "YES" Then
Range("Data_1").Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = Range("Sheet_Name_1")
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Columns.AutoFit
Sheet1.Select
If Sheet1.Range("B1") = "YES" Then
Range("Data_2").Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = Range("Sheet_Name_2")
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Columns.AutoFit
Sheet1.Select
If Sheet1.Range("C1") = "YES" Then
Range("Data_3").Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = Range("Sheet_Name_3")
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Columns.AutoFit
Sheets("Sheet1").Select
Range("H5").Select
Else
If Sheet1.Range("C1") = "YES" Then
Range("Data_3").Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = Range("Sheet_Name_3")
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Columns.AutoFit
Sheets("Sheet1").Select
Range("H5").Select
End If
End If
Else
If Sheet1.Range("B1") = "YES" Then
Range("Data_2").Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = Range("Sheet_Name_2")
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Columns.AutoFit
Sheet1.Select
If Sheet1.Range("C1") = "YES" Then
Range("Data_3").Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = Range("Sheet_Name_3")
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Columns.AutoFit
Sheets("Sheet1").Select
Range("H5").Select
End If
Else
If Sheet1.Range("C1") = "YES" Then
Range("Data_3").Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = Range("Sheet_Name_3")
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Columns.AutoFit
Sheets("Sheet1").Select
Range("H5").Select
End If
End If
End If
End If
End Sub
|