Remove rows less than 10

gbaker · #11 07-17-2014, 08:01 AM

Hi Joli/Ribbons

Still having an issue trying to make the code work for multiple worksheets within one workbook. Here is the code I'm working with:

Code:

Option Explicit
Sub Importeverything()
'
' Importeverything Macro
'
'
    Application.ScreenUpdating = False
    Sheets("Holds").Select
    ChDir "\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\JPM\Data"
    Workbooks.Open FileName:= _
        "\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\JPM\Data\JPM_Weekly_Exceptions_Data.xlsx"
'Copies & Paste Holds information from Exceptions Data from Transaction Group to Master
    Sheets("Holds").Select
    Range("A2:H1076").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Holds").Select
    Range("A2").Select
    ActiveSheet.Paste
    
'Removes rows less than 10
    Call lessthan10
    
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    
'Closes Cut feature
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Range("E25").Select
    
'Copies & Paste Delayed Retirement Plan information from Exceptions Data from Transaction Group to Master
    Sheets("Delayed Retirement Plans").Select
    Workbooks.OpenText FileName:= _
        "\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\JPM\Data\JPM Retirement Plan.xls" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), TrailingMinusNumbers:=True
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").ColumnWidth = 28.43
    Columns("G:G").ColumnWidth = 17.29
    Columns("F:F").ColumnWidth = 19
    Columns("E:E").ColumnWidth = 16.14
    Range("A2:H1076").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Delayed Retirement Plans").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    
 'Removes rows less than 10
    Call lessthan10
    
'Closes JPM Retirement Plan sheet
    Windows("JPM Retirement Plan.xls").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWorkbook.Close
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    
'Copies & Paste Revised Retirement Plan information from Exceptions Data from Transaction Group to Master
    Sheets("Revised Retirement Plan").Select
    Workbooks.OpenText FileName:= _
        "\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\JPM\Data\JPM Revised Retirement Plan.xls" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), TrailingMinusNumbers:=True
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").ColumnWidth = 30.29
    Columns("G:G").ColumnWidth = 26.57
    Columns("F:F").ColumnWidth = 20
    Columns("E:E").ColumnWidth = 12.71
    Range("A2:H1260").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Revised Retirement Plan").Select
    Range("A2").Select
    ActiveSheet.Paste
    
 'Closes JPM Revised Retirement sheet
    Windows("JPM Revised Retirement Plan.xls").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWorkbook.Close
    
 'Removes rows less than 10
    Call lessthan10
    
   
'Opens JPM Weekly Exceptions Data
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    
 'Copies & Paste No Advisable Assets information from Exceptions Data from Transaction Group to Master
    Sheets("No Advisable Assets").Select
    Range("A2:H1426").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("No Advisable Assets").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    
'Closes Cut feature
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    
 'Removes rows less than 10
     Call lessthan10
     
'Copies & Paste Delayed Transactions information from Exceptions Data from Transaction Group to Master
    Sheets("Delayed Transactions").Select
    Columns("G:G").ColumnWidth = 35
    Range("A5:G1202").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Delayed Transactions").Select
    Range("A5").Select
    ActiveSheet.Paste
    Range("A4").Select
    
'Removes rows less than 10
     Call lessthan10
  
'Closes Cut feature
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Application.CutCopyMode = False
    Range("A4").Select
    
'Copies & Paste Extended Default Enroll Deadlin information from Exceptions Data from Transaction Group to Master
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Sheets("Extended Default Enroll Deadlin").Select
    Columns("A:A").EntireColumn.AutoFit
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Extended Default Enroll deadlin").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    
'Closes Cut feature
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    
'Copies & Paste Failed Default Enroll Deadlin information from Exceptions Data from Transaction Group to Master
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Sheets("Failed Default Enroll").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Failed Default Enroll").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    
'Removes rows less than 10
     Call lessthan10
    
'Closes Cut feature
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Application.CutCopyMode = False
    Sheets("Holds").Select
    ActiveWorkbook.Close
    
 Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("UPDATE").Select
    Range("B2").Select
End Sub

Thanks in Advance for all your help on this. I need it.
gbaker

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Grouping table rows to prevent individual rows from breaking across pages	dennist77	Word	1	10-29-2013 11:39 PM
Count rows and add blank rows accordingly	Hoochtheseal	Word VBA	1	01-29-2013 09:23 PM
rows in word?	j2b3	Word Tables	3	07-19-2012 03:59 PM
merging rows and creating sub-rows	gib65	Excel	2	12-09-2011 02:09 PM
How to remove blank rows from a specified range?	Learner7	Excel	1	04-19-2011 02:45 AM