Solved: Excel Macro: Run-time error '1004'

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

tennisfrk159

Thread Starter
Joined
Jun 21, 2012
Messages
8
Hi All,

I included the macro that was developed last week for this "AIR Log." However, the creator is on leave and I only know how to read and interpret...thus giving me an error that I cannot fix:


I would also like to do the following...
  • In Column O have a formula... =CONCATENATE(Lxx,Mxx,Nxx)
  • Hide columns L-N
  • Remove Table Style
  • Set font to Arial; size to 9; Make sure all text is Middle & Center Aligned.
  • Row 1 has a fill color: Blue, Accent 1, Darker 25%


Option Explicit

Sub oooFormatAIRLog()

ActiveWindow.ActivateNext
Sheets(1).Name = "AIRLog"
Columns("P:Q").Delete Shift:=xlToLeft
Range("Table_AIRLog").Select
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Clear
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Risk/Issue/Action Item?]") _
, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Issue,Risk,Action Item", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Criticality]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, CustomOrder:="High,Medium,Low", _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Due Date]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Workstream]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog" _
).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:A").ColumnWidth = 5
Columns("B:B").ColumnWidth = 15
Columns("C:C").ColumnWidth = 12.14
Columns("D:D").ColumnWidth = 30
Columns("E:E").ColumnWidth = 10
Columns("F:F").ColumnWidth = 10
Columns("G:G").ColumnWidth = 10
Columns("H:H").ColumnWidth = 35
Columns("I:I").ColumnWidth = 10
Columns("J:J").ColumnWidth = 10
Columns("K:K").ColumnWidth = 10
Columns("O:O").ColumnWidth = 30.14
Columns("P:p").ColumnWidth = 10
Columns("Q:Q").ColumnWidth = 15.14
Columns("R:R").ColumnWidth = 10
Columns("S:S").ColumnWidth = 30
Columns("T:T").ColumnWidth = 10
Cells.EntireRow.AutoFit
ActiveSheet.ListObjects("Table_AIRLog").Range.AutoFilter Field:=4, _
Criteria1:=Array("In Process", "Not Started", "Resolved"), Operator:= _
xlFilterValues
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
Columns("P:p").Select
Range(Selection, Selection.End(xlToRight)).Delete Shift:=xlToLeft
ActiveSheet.PageSetup.PrintArea = "Table_AIRLog"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Cells.Select
End Sub


Thank you in advance for any help that you guys can provide!

Regards
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
With the sheet and the charts it's a needle in a haystack.
The error messag is that you're adressing a range or sheet which is either misspelt or hidden or non existant.
You will have to go through the macro step by step.
Open the VBA editor, loop for the macro select it and press F8 so the code will be executed line by line and it will stop when the error occurs.
Check that line and see if what it addresses exists.
Trouble shooting takes time an patience.

Good luck :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top