| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long VBA Code - Can it be reduced
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
| Code takes to long | Excel Discussion (Misc queries) | |||
| Long Code | Excel Discussion (Misc queries) | |||
| my code too long? | Excel Worksheet Functions | |||
| Long-winded code | Excel Programming | |||
Threaded Mode
