User Profile
JKPieterse
Silver Contributor
Joined 9 years ago
User Widgets
Recent Discussions
Re: Select cells
You could use a small VBA routine: Sub FindaStyle() Dim oSh As Worksheet Dim oCell As Range For Each oSh In ThisWorkbook.Worksheets For Each oCell In oSh.UsedRange.Cells If oCell.Style Like "*demo*" Then Application.GoTo oCell Stop End If Next Next End Sub Taken from my website article on styles: https://jkp-ads.com/articles/styles.aspxRe: over one million rows in excel sheet cannot be deleted
No matter how many rows you delete, Excel will always have the same # of rows. All that matters is how many you use. Perhaps there is a (hidden) object somewhere on your worksheet. If you press the F5 key and then click Special and select Objects and click OK, what happens exactly? If Excel highlights any object, pressing the Tab key will cycle through them. Maybe one is close to row 1 million?Re: Color a cell the same as another cell's fill color
The only way you might pull this off is by using a macro. Note you will have to adjust the cell range "D1:E2" to match yours: Sub ColorRegionFromFormulas() Dim cl As Range Dim sourceCell As Range Application.ScreenUpdating = False For Each cl In Range("D1:E2") cl.Select 'Necessary! Application.Goto Evaluate(cl.Formula2) With cl.Interior .Color = ActiveCell.Interior.Color 'In case it isn't a themed color On Error Resume Next .Pattern = ActiveCell.Interior.Pattern .PatternColorIndex = ActiveCell.Interior.PatternColorIndex .ThemeColor = ActiveCell.Interior.ThemeColor .TintAndShade = ActiveCell.Interior.TintAndShade .PatternTintAndShade = ActiveCell.Interior.PatternTintAndShade End With Next End SubRe: Filter a Pivot Table on another tab
If your current pivot is based off the data model, you could add the data from that other tab as a filter to the existing pivot table. Then you can create a relationship between both tables and filter on the respective field. Does that make sense? See https://support.microsoft.com/en-us/office/work-with-relationships-in-pivottables-f64ea03b-435a-448d-bc35-3b252bd1233a for some starter information.Re: Tables, Multiple Tabs and Sorting
Make sure the employee numbers on the month sheets are inputs, not formulas. Then use the XLOOKUP function to retrieve the other information of an employee in the other A-E columns. Now if you sort the master list, the month sheets are unaffected. You do have to manually add a new employee to all month sheets this way however.
