Posts by VBA Noob

Re: Copy user selected sheets

Copy them

Hi,

I was wondering how you would write a macro to move a selection of sheets to a workbook called Digi's in the D drive.

Also if there isn't a Workbook called Digi's already it needs to add one.

I've found some code to loop through sheets but nothing to show what sheets the user has selected

Thanks in advance

VBA Noob
:confused:

Re: Comparing a list of words within a Range

Rico,

Not for a betting website. Work in the TV Industry and users entry free text with a venue\location or Country in any order so can't do anything with the data. Just trying to do some analysis on how many times we've being to a location\Veune etc.

Thanks

VBA Noob

Re: Comparing a list of words within a Range

Thanks Rico,

I was after the Venue so would want Old Trafford not Chelsea. Also some cells may only have one location e.g Just the venue name/Area like Cardiff

Thanks

VBA Noob

Re: Comparing a list of words within a Range

Thanks Rico,

There is a long list of Criteria (150 plus) so not sure how useful this would be.

VBA Noob

Re: Comparing a list of words within a Range

Bob,

The word Ireland would be in the list of criteria. The Code needs to look at the string in Column A and extract the word or words that match the criteria list in Column B

Thanks VBA noob

Hi,

I've got a list in "column A" of around 6000 lines which contains key words which I want to extract to "Column b" if It matches but it can match on more than one word .

e.g

List
Six Nations Ireland v England
FA cup Semi Final Chelsea v Liverpool @ Old Trafford

Criteria Sample
Ireland
England
Chelsea
Liverpool

Ideally I would like to extract the first Town/Country as this is where the event is held unless the @ symbol is used then it's the last Toen/Country as in the FA cup example.

Thanks in Advance

Re: Event Handlers for pivot example

Hi all,

Think i've cracked it. My macro is called Fillcolors

Option Explicit
Public mSheet As String
 
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, _
  Cancel As Boolean)
 
  Dim curCell As String, ptname As String, a As Integer
 
Start:
  If ActiveSheet.PivotTables.Count = 0 Then GoTo NoPT
 
  On Error GoTo NoPT
  If IsEmpty(Target) And ActiveCell.PivotField.Name <> "" Then
    Cancel = True
    GoTo NoPT
  End If
  mSheet = ActiveSheet.Name
  curCell = ActiveCell.Address
  ptname = Sh.Range(curCell).PivotTable
 
  If ActiveSheet.PivotTables(ptname).EnableDrilldown Then
    Selection.ShowDetail = True
    Call FillColors
    mSheet = ActiveSheet.Name
    End If
 
NoPT:
  On Error GoTo 0
 
End Sub

Quote from VBA Noob

Hi,

I'm got a pivot table in a sheet called "Pivot" which when the user drills down into the pivot by double clicking it opens up a new sheet with the data. I wanted the event handler to run a macro after the users double click. Got the below code but not working.

Any thoughts ??

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)


If ActiveSheet.PivotTables.EnableDrilldown and _
Not(Intersect(Target, ActiveSheet.PivotTables(1).TableRange2) Is
Nothing) _
Then Call FillColours


End Sub

Display More

Thanks in advance

VBA Noob

Display More

Hi,

I'm got a pivot table in a sheet called "Pivot" which when the user drills down into the pivot by double clicking it opens up a new sheet with the data. I wanted the event handler to run a macro after the users double click. Got the below code but not working.

Any thoughts ??

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)


If ActiveSheet.PivotTables.EnableDrilldown and _
Not(Intersect(Target, ActiveSheet.PivotTables(1).TableRange2) Is
Nothing) _
Then Call FillColours


End Sub

Thanks in advance

VBA Noob

Re: Use criteria to add colour to rows (Up to 6)

Sorry,

Thanks to you to Tom

Danny

Re: Use criteria to add colour to rows (Up to 6)

Thanks Norie,

That seems to have done the trick

Danny

Re: Use criteria to add colour to rows (Up to 6)

Hi Norie,

I'm got a spreadsheet which has colors e.g Green for Budget, Yellow for P. yr etc which I then put into a pivot table but when you interrogate the pivot the color doesn't come through went it opens a new page. So to make it easier for the end user I want to add a macro to look at column C and change each rows content color depending on the criteria in C2, C3 etc looping throught to the end.

Tried your code but gives a run time error

ActiveCell.EntireRow.ColorIndex = lColor

Hope this explains it

Hi,

I'm looking to add colour to a row selection when a certain criteria is met. There is more than three criteria so the below Conditional formatting won't work on the test table also attached.

Order customer source £
4 bill a 6
4 bill d 5
5 bill a 7
5 bill b 4
5 bill c 3

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($C1=""a"",TRUE,FALSE)"
Selection.FormatConditions(1).Interior.ColorIndex = 36
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($C1=""b"",TRUE,FALSE)"
Selection.FormatConditions(2).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($C1=""f"",TRUE,FALSE)"
Selection.FormatConditions(3).Interior.ColorIndex = 40

So I've tried using a If and Do statement but I'm having trouble with it. Can anyone point me in the right direction

Range("C2").Select
Application.ScreenUpdating = False


Do


If ActiveCell = "a" Then Call SelectActiveRow
With Selection
.ColorIndex = 36
End With
End If
If ActiveCell = "b" Then Call SelectActiveRow
End
With Selection
.ColorIndex = 35
End With
End If
If ActiveCell = "c" Then Call SelectActiveRow
With Selection
.ColorIndex = 34
End With
End If
If ActiveCell = "d" Then Call SelectActiveRow
With Selection
.ColorIndex = 37
End With
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))


Application.ScreenUpdating = True


End Sub

Sub SelectActiveRow()
If IsEmpty(ActiveCell) Then Exit Sub
' ignore error if activecell is in Column A
On Error Resume Next
If IsEmpty(ActiveCell.Offset(0, -1)) Then Set LeftCell = ActiveCell Else Set LeftCell = ActiveCell.End(xlToLeft)
If IsEmpty(ActiveCell.Offset(0, 1)) Then Set RightCell = ActiveCell Else Set RightCell = ActiveCell.End(xlToRight)
Range(LeftCell, RightCell).Select
End Sub

Thanks in advance

VBA Noob