Highline Excel Class 15: Excel 2007 Tables 7 Examples

I had bought one today. But I didn't find the disc for Examples. Why?

Hi friends!,

I have a data file, and another file with some pivot tables on different worksheets, each reading from the data file.

I am trying to write some code in the data file that tells the relevant pivot table to refresh anytime data changes in the source table. What I wrote doesn't work. I have pasted a sample of what i came up with, below.



Private Sub Worksheet_Change(ByVal Target As Range)
[file_name.xlsm] worksheet_name!.PivotTables("PivotTable1").RefreshTable
End Sub



Could anyone help me correct this code, or give me a better way to do this?

As always, I am very grateful for any ideas and suggestions.

Hello everyone,

what I am trying to achieve with the final graph is:

1) horizontal axis: the continuous variable of interest with different class intervals
2) vertical axis: the frequency density given by Frequency/Class width

....I have no problem doing the first 2 points...the tricky bit is the following

3) I want to insert the data label in every column, which in this case should be represented by the frequency (the area) given by width*height of every rectangle
Whenever I try to insert data label it comes up with the data of the vertical axis (density frequency) and not frequency...

Hope I've been clear enough...
Thanks

Max

I have been using the following code to hide a label on a worksheet in Excel 2002 and it works fine:

Label1.Visible=False (found the line to be at fault by debugging)

But when I runi it in Excel 2007, it fails with a Run time Error 1004 -Unable to set the Visible property of OLEObject Class?

I tried using Me.Label1.Visible=False but it still fails. Is there any refined code that will work on both Excel 2002 and Excel 2007? Any help would be appreciated.

Hello,

I've created excel sheet contains macros and connected with SQL query.
I am getting below mentioned error.

"Run-time error '1004' Select Method of Worksheet Class failed Options "

Note: After recording the macro i've hide worksheet through VBA code
with password. Before hiding worksheet macros was run fine but after
hiding the worksheet it shows me above error.


I am getting error on line " sheets("Sheet2"). Select. (4th line)


Sheet2 which is hidden by VBA code after macros was recorded.


Below is whole query:


Sub Finaloutput()
'
' Finaloutput Macro
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("Sheet2").Select
Cells.Select
Selection.Copy
Sheets("Sheet6").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array
(1, 2, 3, 4, _
5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15), Replace:=True,
PageBreaks:=False, _
SummaryBelowData:=True
Selection.Copy
Sheets("Sheet8").Select
Range("J13").Select
ActiveWindow.SmallScroll Down:=-15
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Range("C8").Select
Cells.Replace What:=" Total", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Cells.Select
Selection.Copy
Sheets("Sheet7").Select
ActiveWindow.SmallScroll Down:=-24
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K11").Select
Sheets("Sheet5").Select
Range("A7").Select
Sheets("Sheet4").Select
Range("AE5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet5").Select
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A7").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add Key:=Range
("A7"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet5").Sort
.SetRange Range("A7:N162")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("G10").Select
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlNotEqual, _
Formula1:="=0"
Selection.FormatConditions
(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("J15").Select
End Sub

I've created several table queries in Access 2007 that run as expected. In Excel 2007 under the data tab you have the option to "Get External Data" "From Access". In most cases I would select the database and see a list of tables to link to the worksheet. I've created 4 today that I can see in Access that don't show up in my options for Excel. The list of tables doesn't give an option to refresh and I've tried exiting both programs and starting over. Any ideas?

-goodluck

Hi. I just noticed that in the new office package (2007) the lines from Excel remain when i copy tables into Word. Is there a fix for this?

Thanks,

/Mads

In the past, when I would add a column next to a table it would automatically include that in the table. Now when I do it, it doesn't include it in the table. I searched around for options to see if settings got changed since something else was changed that I had to fix. However, I can't find where to make it to where if you add something next to a table it automatically expands its range.

Hi,

I have the following table with the first rows merged to indicate the years. When I insert tables in excel 2007, Excel seems to add the word "Column" in empty cells and I cant seems to delete the word "Column".

Here is sample how the table looks before inserting the table and after the table is inserted.

Is there any way I can delete the word "Column" ?

Sheet3

* B C D E F G H I J K 2 Before Inserting Table * * * * * * * * * 3 Year 2006 2007 2008 4 Dept M1 M2 M3 M1 M2 M3 M1 M2 M3 5 Staff 200 200 200 200 200 200 200 200 200 6 Dependents 380 380 380 380 380 380 380 380 380 7 * * * * * * * * * * 8 * * * * * * * * * * 9 After Inserting Table * * * * * * * * * 10 * * * * * * * * * * 11 Year 2006 Column1 Column2 2007 Column3 Column4 2008 Column5 Column6 12 Dept M1 M2 M3 M1 M2 M3 M1 M2 M3 13 Staff 200 200 200 200 200 200 200 200 200 14 Dependents 380 380 380 380 380 380 380 380 380

Excel tables to the web >> Excel Jeanie HTML 4


Appreciate assistance. Thanks.

I'm working through the Power Excel 2007 with MrExcel LiveLessons and had a question about Lesson 14 (Tables and Table Formulas). In the lesson, MrExcel says that if you go adjacent to the table then that column will automatically become part of the table. Also, if you insert a formula in the first row (of the new column) it will automatically copy down to all of the rows below. However, when I tried that in my excel it did not work. Is there a reason for this error? Is there something I need to turn on to make the specific table formats work as they do in the LiveLessons? Thanks in advance for any help you can provide.