Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
spreadsheet has outgrown the macro!
This macro has out grown it's self and I AM AGAIN STRUGGLING!
Firstly I have columns up to BN but when I go and insert another column between columns I get an error message - any ideas also This macro which has been copied to create many reports from the data, which works great, but I can't get it to copy some of the end columns. I can't see any reason why. I will paste a copy of one macro below. Sub MAB_Rollout_Schedule() Dim wbPRP As Workbook Dim wbNew As Workbook Dim wsCopy As Worksheet Dim wsPaste As Worksheet Set wbPRP = Workbooks("PRP Rollout Schedule.xls") Set wsCopy = wbPRP.Worksheets("House List") Set wbNew = Workbooks.Add Set wsPaste = wbNew.Worksheets(1) wbPRP.Save With wsCopy .AutoFilterMode = False .Range("B6").CurrentRegion.Copy _ Destination:=wsPaste.Range("B6") End With wsPaste.Range("F:I,Q:S,V:W,Y:Z,AG:AH,AQ:AP,AW:BQ,B Y:BZ,CE:CZ").Delete ActiveWorkbook.SaveAs Filename:= _ "C:\MAB_SCHEDULE FOLDER\MAB SCHEDULE.xls", FileFormat _ :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False Windows("PRP Rollout Schedule.xls").Activate Range("A1:N5").Select Selection.Copy Windows("MAB SCHEDULE.xls").Activate Range("B1").Select ActiveSheet.Paste Range("K2:K3").Select Application.CutCopyMode = False Selection.Cut Destination:=Range("F2:F3") Range("J5").Select ActiveWorkbook.Save wbPRP.Activate With wsCopy .Activate .Range("B6").Select .Range("B6").AutoFilter End With Windows("MAB SCHEDULE.xls").Activate ActiveWindow.DisplayGridlines = False Range("B7:AF1927").Select Selection.AutoFilter Selection.AutoFilter Field:=4, Criteria1:="Active" Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Range("A1").Select ActiveWorkbook.Save End Sub If you have any idea please let me know. I am also going to post this as a new question incase you are not available to help Sean... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Value in an External Spreadsheet | Excel Discussion (Misc queries) | |||
Macro doesn't insert image when spreadsheet is protected | Excel Worksheet Functions | |||
Writing a macro that will exit a spreadsheet | New Users to Excel | |||
Spreadsheet print including macro buttons? | Excel Discussion (Misc queries) | |||
Sample spreadsheet of using macro | Excel Worksheet Functions |