Picture Too Large? What picture!!

[RandyD123]

I am getting this error when trying to close my file. Not sure how to stop it. My VBA code is below. If I get rid of the macro button and use Ctrl+s instead, there is no problem!!

Sub RandyD()
Dim c As Range
Application.ScreenUpdating = False

'Generate Sheet Output "Result", Overwrite Old Sheet
On Error Resume Next
    Sheets("Result").Delete
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Result"
    Sheets("Raw Data").Cells.Copy Range("A1")
On Error GoTo 0

With Sheets("Sheet2")  'Replace Airline With 2 Letters
    For Each c In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
        Range("G:G").Replace What:=c.Value, Replacement:=c.Offset(, 1), LookAt:=xlWhole, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next
End With

n = Range("A" & Rows.Count).End(xlUp).Row
h = 4 'data start at row
For i = n To h Step -1
    j = WorksheetFunction.CountIf(Range("A" & n & ":A" & h), Cells(i, "A")) 'case insensitive
'        Debug.Print Cells(i - j + 1, "A").Resize(j, 11).Address
        Cells(i - j + 1, "A").Resize(j, 11).Sort Key1:=Columns(7), Order1:=xlAscending, Header:=xlNo  'Sort By 2 First Letters
        If i - j + 1 <> h Then
            Rows(i - j + 1).Insert  'Insert Row Between Different Dates
        End If
        i = i - j + 1
Next

'Combine Code & Flight Number
With Range("B4", Range("B" & Rows.Count).End(xlUp))
   .Value = Evaluate(.Columns(6).Address & "&" & .Columns(5).Address)
End With

'Time to Number Format
For Each c In Range("E4", Cells(Rows.Count, "E").End(xlUp))
    c = Replace(Left(c.Text, 6), ":", "")
Next

Range("C:D,F:H,J:J").Delete
Range("A3:E3").Value = Array("Date", "Airline", "Time", "PAX", "PCPAX")
Application.ScreenUpdating = True

End Sub

Thanks for any help with this.....!

 

[jolivanes]

I realize that you're talking about a shape but
If you add this at the start

Dim lr1 As Long, lc1 As Long
lr1 = Sheets("Raw Data").Cells.Find("*", ,xlValues , , xlByRows, xlPrevious).Row
lc1 = Sheets("Raw Data").Cells.Find("*", ,xlValues , , xlByColumns, xlPrevious).Column

and replace this

Sheets("Raw Data").Cells.Copy Range("A1")

with this

Sheets("Raw Data").Cells.(1).Resize(lr1, lc1).Copy Sheets("Result").Cells(1)
'<---- or if you want values only
'<---- Sheets("Result").Cells(1).Resize(lr1, lc1).Value = Sheets("Raw Data") .Cells(1).Resize(lr1, lc1).Value
Application.CutCopyMode = False

does that help at all?

 

[S.Br.]

I am getting this error when trying to close my file. Not sure how to stop it. My VBA code is below. If I get rid of the macro button and use Ctrl+s instead, there is no problem!! View attachment 96446

Sub RandyD()
Dim c As Range
Application.ScreenUpdating = False

'Generate Sheet Output "Result", Overwrite Old Sheet
On Error Resume Next
    Sheets("Result").Delete
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Result"
    Sheets("Raw Data").Cells.Copy Range("A1")
On Error GoTo 0

With Sheets("Sheet2")  'Replace Airline With 2 Letters
    For Each c In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
        Range("G:G").Replace What:=c.Value, Replacement:=c.Offset(, 1), LookAt:=xlWhole, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next
End With

n = Range("A" & Rows.Count).End(xlUp).Row
h = 4 'data start at row
For i = n To h Step -1
    j = WorksheetFunction.CountIf(Range("A" & n & ":A" & h), Cells(i, "A")) 'case insensitive
'        Debug.Print Cells(i - j + 1, "A").Resize(j, 11).Address
        Cells(i - j + 1, "A").Resize(j, 11).Sort Key1:=Columns(7), Order1:=xlAscending, Header:=xlNo  'Sort By 2 First Letters
        If i - j + 1 <> h Then
            Rows(i - j + 1).Insert  'Insert Row Between Different Dates
        End If
        i = i - j + 1
Next

'Combine Code & Flight Number
With Range("B4", Range("B" & Rows.Count).End(xlUp))
   .Value = Evaluate(.Columns(6).Address & "&" & .Columns(5).Address)
End With

'Time to Number Format
For Each c In Range("E4", Cells(Rows.Count, "E").End(xlUp))
    c = Replace(Left(c.Text, 6), ":", "")
Next

Range("C:D,F:H,J:J").Delete
Range("A3:E3").Value = Array("Date", "Airline", "Time", "PAX", "PCPAX")
Application.ScreenUpdating = True

End Sub

Thanks for any help with this.....!

Randy, what version of XL is this? I have XL2007 that shows this same error on SAVE if worksheet has any button (windows XP SP3) and the workbook becomes corrupt.

 

[RandyD123]

I can give you advice on recreating your workbook. How many sheets does it have? What is the functionality of each sheet? Do any of the sheets have VBA code? How many VBA non-sheet modules exist?

So here is a link to the "Raw Data" sheet. I then run the above code on that sheet and get my desired result. If I put a button, I get the error when I close the sheet. If I use Ctrl +x or whatever to run the code, I don't get the error.

 

[RandyD123]

I realize that you're talking about a shape but
If you add this at the start

Dim lr1 As Long, lc1 As Long
lr1 = Sheets("Raw Data").Cells.Find("*", ,xlValues , , xlByRows, xlPrevious).Row
lc1 = Sheets("Raw Data").Cells.Find("*", ,xlValues , , xlByColumns, xlPrevious).Column

and replace this

Sheets("Raw Data").Cells.Copy Range("A1")

with this

Sheets("Raw Data").Cells.(1).Resize(lr1, lc1).Copy Sheets("Result").Cells(1)
'<---- or if you want values only
'<---- Sheets("Result").Cells(1).Resize(lr1, lc1).Value = Sheets("Raw Data") .Cells(1).Resize(lr1, lc1).Value
Application.CutCopyMode = False

does that help at all?

I will try that, just can't do it now. I'll let you know ASAP. Thanks!

 

[RandyD123]

I will try that, just can't do it now. I'll let you know ASAP. Thanks!

Nope, I get a compile error here:

Sheets("Result").Cells(1).Resize(lr1, lc1).Value = Sheets("Raw Data") .Cells(1).Resize(lr1, lc1).Value

 

[RandyD123]

I will try that, just can't do it now. I'll let you know ASAP. Thanks!

Ok I fixed the compile error, just needed to backspace one of the ".cells" after ("Raw Data"). But when I run it, my time column goes to all zeros. The time column has to be in a number format.