Posts by Giorgio

Giorgio · Dec 17th 2007

Re: pivot tables turning variables on off

I was editing the response to show what excels macro recorder suggested.
Sorry for any confusion caused.

Giorgio · Dec 17th 2007

I have a pivot table that i am trying to copy out to another sheet.

A note of clarification.
My variables are used in the sum field - I have 70 different columns of data, that I want to use in combination one column field and one row field. So each time I'm changing the sum calculated by add a new sum and remove the previous one.

I've tried to record the procedure to highlight a new variable and remove the previous. However it doesn't seem to work. Any suggestions gratefully received.

I enclose my code below.

Code

Sub pivot1()


Dim j As Integer
Dim k As Integer
Dim i As Integer
Dim StrM As String ' new variable
Dim StrM1 As String ' old variable
Dim StrN As String
Dim theBook As Workbook
Dim theSheet As Worksheet
Dim theSheet1 As Worksheet




Set theBook = ActiveWorkbook
Set theSheet = theBook.Sheets("Sheet4") ' this is where my data is
Set theSheet1 = theBook.Sheets("Sheet3") ' this is where my new table is


k = 2 ' column in my new table
j = 1
    For i = 1 To 70
    StrM1 = StrM
    StrM = "SCT43000" & j
    StrN = "Sum of SCT43000" & j
    
    
   If i < 2 Then
    theSheet.PivotTables("PivotTable2").AddDataField theSheet.PivotTables( _
        "PivotTable2").PivotFields(StrM), StrN, xlSum
    Else
    theSheet.PivotTables("PivotTable2").AddDataField theSheet.PivotTables( _
        "PivotTable2").PivotFields(StrM), StrN, xlSum
        theSheet.PivotTables("PivotTable2").PivotFields(StrM1).Orientation = xlHidden
    End If
     theSheet.Range("B4:F637").Copy (theSheet1.Cells(1, k))
    
    j = j + 1
    k = k + 5
    Next i
    
End Sub


This is the macro recorders code
ActiveSheet.PivotTables("PivotTable2").PivotFields("SCT4300068").Orientation = xlHidden
' this seems to remove the values but not as part of a macro.

Display More

Giorgio · Jul 11th 2006

Re: Conditional Formatting of Cells

thanks

Giorgio · Jul 11th 2006

Re: Conditional Formatting of Cells

sorry for any confusion caused and sorry for continuing to be a pain but I can't seem to get it to work.

IF cell a1 is greater than -7 I want it to be red
If cell a1 is greater than 14 I want it to be green.
CF does this.....

I want Cell a2 to red, or green according to cell A1.

I hope this clarifies my predicament.

Giorgio · Jul 11th 2006

Re: Conditional Formatting of Cells

doh!!!!!!!!!!!!!!!

thanks......couldn't see the wood for the trees.

hope you afternoon gets better.

Giorgio · Jul 11th 2006

Sorry to be a pain,

I am using conditional formatting to set the colour of a cell. I now want to change the colour of the adjoing cell to the same as the conditional format cell. CF doesn't have this as an option, any help on alternatives would be gratefully received.

Giorgio · Feb 7th 2005

Re: Saving a chart as a wmf

your absolutely correct. how do I install the wmf filter?

Giorgio · Feb 7th 2005

Re: Saving a chart as a wmf

[code]
Set chtActive = ActiveSheet.ChartObjects(1).Chart
chtActive.Export "c:/test/" & strSheetName & ".wmf", "wmf"
[\code]

I've declared the chart as chtActive and I'm trying to call the wmf a name that I've saved as strSheetName but i get an error

Run time error 1004.

Method 'Export' of object'_chart' failed

any ideas why?

thanks for your comments so far.

Giorgio · Feb 7th 2005

I was wondering as I am creating 100 excel sheets, (in 4 woorkbooks) with data and a chart (using a macro) is there anyway of saving the charts as a wmf file so I can use them in quarkexpress?

I am aware that one can save them to a cliboard but not sure if this helps in creating a wmf file.

any help would be gratefully appreciated.

Giorgio · Jan 19th 2005

Re: vba chart creation on a new worksheet

Thanks I feel as if a bus has run me over, can't imagine what was going on in my head to screw this macro up so badly in such a little amount of time. I will be eternally grateful for your help....

p.s. did I say it works. many thanks

Giorgio · Jan 19th 2005

I'm having problems with a spreadsheet trying to create a new spreadsheet and then creating a new chart. I seems to have corrupted the code but I can't figure out what has happened. (i performed the ultimate crime and saved over my original code) doh!!!!!!!!

Any help would be gratefully appreciated.

Code

Sub New_Chart()


Dim wkbTemplate As Workbook
Dim wbk As Workbook
Dim wksControl As Worksheet
Dim wksCountry As Worksheet
Dim wksNewSheet As Worksheet
Dim chtActive As Chart


Dim i As Integer
Dim j As Integer
Dim nRows As Integer
Dim strCountry As String
Dim strSheetName As String


ActiveWorkbook.SaveAs Filename = "C:\Co182_gf_A" & i & ".xls"
Set wkb = ActiveWorkbook
Set wksControl = wkb.Worksheets("all")
Set wksCountry = wkb.Worksheets("sheet1")


i = 2
For i = 2 To 20 'test actual figure is 180


strCountry = wksControl.Cells(1, i).Value
strSheetName = wksControl.Cells(1, i).Value


    Set wksNewSheet = wkb.Worksheets.Add
    wksNewSheet.Name = strSheetName
    wksNewSheet.Move after:=Sheets(wkb.Worksheets.Count - 1)
    wksCountry.Select
    wksCountry.Cells.Select
    Selection.Copy
    wksNewSheet.Select
    wksNewSheet.Paste


    '*** Paste country name into sheet
    wksNewSheet.Cells(6, 1).Value = wksControl.Cells(1, i).Value
Debug.Print strCountry
    '*** Change series for charts
    Range("B5:C38").Select
    Selection.Copy
    
    Range("E5").Select
    Selection.PasteSpecial Paste:=xlValues
    
    '*** CHART
    Range("E6:F38").Select
    Selection.Sort Key1:=Range("F6"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    Charts.Add
    ActiveChart.ChartType = xlBarStacked
    ActiveChart.SetSourceData Source:=Cells("& strsheetname &"! E5:F38")
    ActiveChart.Name = "& strsheetname & & 1 &"
    ActiveChart.Location Where:=xlLocationAsObject, Name:=strSheetName
    ActiveChart.HasLegend = False
    ActiveSheet.Shapes.strSheetName.IncrementLeft 19.5
    ActiveSheet.Shapes.strSheetName.IncrementTop -84.75
    ActiveSheet.Shapes.strSheetName.ScaleWidth 1.28, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes.strSheetName.ScaleHeight 1.63, msoFalse, msoScaleFromTopLeft
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    
    With Selection.TickLabels.Font
        .Name = "Foundry Form Sans"
        .Size = 10
    End With
    
    With Selection.TickLabels.Font
        .Name = "Foundry Form Sans"
        .Size = 8
    End With
        
    With Selection.TickLabels.Font
        .Name = "Foundry Form Sans"
        .Size = 10
    End With
    
    With Selection.TickLabels
        .Alignment = xlCenter
        .Offset = 100
        .Orientation = xlUpward
    End With


    '*** page setup here
    With wksNewSheet.PageSetup
    wksNewSheet.PageSetup.Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(0.78740157480315)
        .RightMargin = Application.InchesToPoints(0.31496062992126)
        .TopMargin = Application.InchesToPoints(0.511811023622047)
        .BottomMargin = Application.InchesToPoints(0.590551181102362)
        .HeaderMargin = Application.InchesToPoints(0.511811023622047)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
    
    End With
    ActiveWindow.View = xlPageBreakPreview
    wksNewSheet.PageSetup.PrintArea = "$e$1:$p$38"


    wkb.Save
    wkb.Close
    
    Exit For


Next i


  
End Sub

Display More

Giorgio · Jan 4th 2005

I have a spreadsheet and am copying the spreadsheet and updating the 7 charts on the sheet with data on this new sheet. No problems here and has been working for some six months. came back today after my christmas break and the charts are now causing errors. Code is below.

My question is where does excel assign the chart name, as I think it has corrupted as the data is being assigned to the wrong chart. any help would be gratefully appreciated.

The error i get is: Method 'Seriescollection' of object '_chart' failed

Code

'*** Change series for charts
    
    '*** AGE STRUCTURE CHART 1
    Set chtActiveChart = wksNewSheet.ChartObjects("Chart 3").Chart
    chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R8C3:R8C10," & strSheetName & "!R12C3:R12C10,1)"
    chtActiveChart.SeriesCollection(2).Formula = "=SERIES(,," & strSheetName & "!R13C3:R13C10,2)"


    '*** TENURE - CHART 2
    Set chtActiveChart = wksNewSheet.ChartObjects("Chart 1").Chart
    chtActiveChart.SeriesCollection(2).Formula = "=SERIES(,," & strSheetName & "!R31C5:R35C5,2)"
    chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R31C1:R35C1," & strSheetName & "!R31C4:R35C4,1)"
    
    '*** General health - CHART 3
    Set chtActiveChart = wksNewSheet.ChartObjects("Chart 4").Chart
    chtActiveChart.SeriesCollection(2).Formula = "=SERIES(,," & strSheetName & "!R51C5:R53C5,2)"
    chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R51C1:R53C1," & strSheetName & "!R51C4:R53C4,1)"
    
    '*** Economic Activity - CHART 4
    Set chtActiveChart = wksNewSheet.ChartObjects("Chart 2").Chart
    chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R77C1:R81C1," & strSheetName & "!R77C4:R81C4,1)"
    
    
    '*** Economic Inactivity - CHART 5
    Set chtActiveChart = wksNewSheet.ChartObjects("Chart 5").Chart
    chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R87C1:R91C1," & strSheetName & "!R87C4:R91C4,1)"
    
    '*** Occupation - CHART 6
    Set chtActiveChart = wksNewSheet.ChartObjects("Chart 6").Chart
    chtActiveChart.SeriesCollection(2).Formula = "=SERIES(,," & strSheetName & "!R101C5:R109C5,2)"
    chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R101C2:R109C2," & strSheetName & "!R101C4:R109C4,1)"
    
    '*** Industry - CHART 7
    Set chtActiveChart = wksNewSheet.ChartObjects("Chart 8").Chart
    chtActiveChart.SeriesCollection(2).Formula = "=SERIES(,," & strSheetName & "!R115C5:R125C5,2)"
    chtActiveChart.SeriesCollection(1).Formula = "=SERIES(," & strSheetName & "!R115C2:R125C2," & strSheetName & "!R115C4:R125C4,1)"

Display More

Giorgio · Nov 11th 2004

Re: automate data on a new page

I don't think vlookup will help on this occasion, but if you could provide me with more details I'll give it a bash.

Sorry to be taking up your precious time.

Giorgio · Nov 11th 2004

I have a sheet with data, c0116 london, on a second sheet, sheet1, I copy some data and create some charts. By using a drop down combo box I want to change cell c1 to another country and change all the data and tables. The highlighted cells on sheet1 all come from the other sheet.

I'm not sure if excel can do this. It sounds to me like something that should be possible but not sure how to approach this. Hope my posting makes sense.

Any help would be gratefully appreciated.

Giorgio · May 26th 2004

Sounded like an easy thing that has got me stumped. I've created a macro, assigned to a custom button and I would like to change the labrel on that button to something more appropriate than "custom button".

Any help would be gratefully appreciated.

Giorgio · Feb 12th 2004

I would just like to thank willr and richieuk for their time and patience helping me resolve my problems.

have a great weeekend

giorgio:tumble::tumble::tumble:

Giorgio · Feb 11th 2004

I suspect it is because I am using activesheet in my macro. I'll enclose a short bit as it is rather long.

sorry for being so stupid, thanks for being so patient with my problem.

here is my macro within yours.

Sub layout1()
'
' Keyboard Shortcut: Ctrl+q
'
Dim ws As Worksheet
For Each ws In Worksheets
Application.Run "PERSONAL.XLS!layout"
MsgBox ws.Name
Next
End Sub

Sub layout()
'
' Keyboard Shortcut: Ctrl+r
'
Range("A6").Select
Selection.Copy
Range("F1").Select
ActiveSheet.Paste
Range("B30:D45").Select
Application.CutCopyMode = False
Selection.Copy
Range("F2").Select
ActiveSheet.Paste
Range("A6,C6:D6").Select
Range("C6").Activate
Application.CutCopyMode = False
Selection.Copy

end sub

Giorgio · Feb 11th 2004

This code seems to run my macro, the messagebox says the name of various spreadsheets but the macro continues to work on the same sheet. should I have a line that says move from from one sheet to the next. also the message box ask for a reply when calling each sheet. rather than the macro continuing.

thanks for you help.

Quote
Originally posted by WillR
didn't know whether this was solved or no so...
Code
Sub sheetstuff()
Dim ws As Worksheet
For Each ws In Worksheets
    'your code here i.e.
    MsgBox ws.Name
Next
End Sub
Hope it helps

Giorgio · Feb 11th 2004

I'm sure there must be an easier way of running a macro on all the worksheets in a workbook. at present i am having to write a line for each worksheet. I've got some sheets with over 50 sheets.

any help would be greatfully appreciated.

Giorgio · Dec 9th 2003

It seems as if my macro skips the filling in of the sheet with data and goes to the next application run function which is a text to column function.

it works properly if i dim this statement however!!!ahhhhhhhhhhhhh!

I can't put a create new sheet statement as I want the macro to run on the select workbook and not the one which contains the macros. More than one person has access to the macros sheet.

Posts by Giorgio

Pivot Table changing sum values using VB

Pivot Table changing sum values using VB

Conditional Formatting of Cells

Conditional Formatting of Cells

Conditional Formatting of Cells

Conditional Formatting of Cells

Saving a chart as a wmf

Saving a chart as a wmf

Saving a chart as a wmf

vba chart creation on a new worksheet

vba chart creation on a new worksheet

How to Define names of Charts in a worksheet using vba

automate data on a new page

automate data on a new page

Changing the label on a customised macro

[Solved] VBA : Is there a way of running a macro for all th

[Solved] VBA : Is there a way of running a macro for all th

[Solved] VBA : Is there a way of running a macro for all th

[Solved] VBA : Is there a way of running a macro for all th

[Solved] VBA: flippin' macros