Generic Macro

[Karen]

Hi all
I am using the macro below which I found in one of the threads to draw
data from a "Summary" sheet based on a helper column - "G" in
readiness for a chart.
I would like however to reuse this macro on any of the sheets within
my workbook and not just the "Summary" sheet. The other sheets also
have the same helper column etc.
How could I make the code Generic?.
I tried Activesheet.select but have a problem when returning to it
which I'd like to.
TIA

Karen

Sub CreateChart()
Application.DisplayAlerts = False
Sheets("Summary").Select
' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
' Loop through each row
For x = 3 To FinalRow
' Decide if to copy based on entry column G
ThisValue = Range("G" & x).Value
If ThisValue = "1" Then
Range("A" & x & ":AG" & x).Copy
Sheets("Chart").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
'ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("Summary").Select

End If
Next x

Cleanup
Chart
Sheets("Summary").Select
Application.DisplayAlerts = True
End Sub

 

[Dave Hawley]

Hi Karen

If I have understood, just remove all

Sheets("Summary").Select

lines.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Karen]

Dave
You have understood it - sorry I wasn't clear and thanks - it worked a treat.

Karen

 

[Karen]

Dave
If you can indulge me once more, no sooner had I got to using the
macro when I realised that I have problem namely is there a way of
naming the chart with the tab of the worksheet from which I am pulling
the data?
One of the functions I found (courtesy of Chip Pearson) is under but I
can't figure out how to merge/utilise this. I have also copied my
original posting to save the bother of calling it up.

Function Sheetname() As String
Application.Volatile
Sheetname = Application.Caller.Worksheet.Name
End Function

Thanks very much
Karen





Hi all
I am using the macro below which I found in one of the threads to draw
data from a "Summary" sheet based on a helper column - "G" in
readiness for a chart.
I would like however to reuse this macro on any of the sheets within
my workbook and not just the "Summary" sheet. The other sheets also
have the same helper column etc.
How could I make the code Generic?.
I tried Activesheet.select but have a problem when returning to it
which I'd like to.
TIA

Karen

Sub CreateChart()
Application.DisplayAlerts = False
Sheets("Summary").Select
' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
' Loop through each row
For x = 3 To FinalRow
' Decide if to copy based on entry column G
ThisValue = Range("G" & x).Value
If ThisValue = "1" Then
Range("A" & x & ":AG" & x).Copy
Sheets("Chart").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
'ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("Summary").Select

End If
Next x

Cleanup
Chart
Sheets("Summary").Select
Application.DisplayAlerts = True
End Sub

 

[Dave Peterson]

I don't think Chip's code is the solution in this case. He uses this type of
function in a worksheet cell to return the name of that worksheet.

But you could use something kind of like it:

Option Explicit
Sub CreateChart()

Dim wks As Worksheet
Dim FinalRow As Long
Dim NextRow As Long
Dim ThisValue As Variant
Dim X As Long

Set wks = ActiveSheet

Application.DisplayAlerts = False

' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
' Loop through each row
For X = 3 To FinalRow
' Decide if to copy based on entry column G
ThisValue = Range("G" & X).Value
If ThisValue = "1" Then
Range("A" & X & ":AG" & X).Copy
Sheets("Chart").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
'ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
wks.Select 'added
End If
Next X

Cleanup
Call ChartProc(wks.name)
Sheets("Summary").Select
Application.DisplayAlerts = True
End Sub

I changed the name of your chart procedure to ChartProc. (Chart is used in VBA
for, er, charts.)

Then your chartproc has to be changed to accept that passed value:

sub ChartProc(mySheetName as string)
'do lots of stuff
'Title your chart using that passed parm)
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = mySheetName
End With
'do more things
end sub


I guessed that you really meant title the chart, instead of naming it.

 

[Sammy]

Dave/Karen
I have been following this thread as this looks kinda useful.
One problem though, how would you clear any existing data or even
chart prior to including a new one?

Sam

 

[Dave Peterson]

Once you find the range, you could clear the cells like:

Dim myRng as range
with worksheets("Chart")
set myRng =.range("A1:AG" & .cells(.rows.count,"A").end(xlup).row)
myRng.clearcontents 'or even .Clear
end with

You could even wipe it out with:
myrng.entirerow.delete

 

[Karen]

Dave
I must admit I hadn't considered this aspect. All of your suggestions
worked brilliantly. The bit I have a problem with is how to also
delete any existing chart. Once again I am in your debt.

TIA
Karen

 

[Dave Peterson]

Oops. Missed that part.

If it's the only one on the sheet:

With ActiveSheet
.ChartObjects(1).Delete
End With

You could specify the name, too:

With ActiveSheet
.ChartObjects("chart 1").Delete
End With

You can see the name of the chart in the Namebox (to the left of the formulabar)
if you hit and hold the control key and select your chart.

 

[Karen]

Dave
Thanks for the quick response.
I get the following error "Unable to get ChartObjects property of the
Worksheets class" that is until I place an "On Error" in which case
the original chart remains.
My code follows - would you mind having a look at it?
TIA
Karen

Code.....

Sub Macro3()
Dim myRng As Range
With Worksheets("Chart")
Set myRng = .Range("A1:C28" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
myRng.ClearContents
End With
On Error Resume Next
With ActiveSheet.ChartObjects(1).Delete
End With
CreateChart
End Sub

 

[Dave Peterson]

Where is that chart? Is it on the worksheet named Chart?

If yes:

Option Explicit

Sub Macro3()
Dim myRng As Range
With Worksheets("Chart")
Set myRng = .Range("A1:C28" & .Cells(.Rows.Count, "A").End(xlUp).Row)
myRng.ClearContents
On Error Resume Next
.ChartObjects(1).Delete
On Error GoTo 0
End With
CreateChart
End Sub

(You kind of combined a couple of lines into one.)

If it's not on worksheets("chart"), then

on error resume next
worksheets("otherworksheetnamehere").chartobjects(1).delete
on error goto 0

will do it.

 

[Karen]

Dave
It worked (the chart was on Worksheet "Chart").
Many many thanks for your help.

Karen