selecting multiple ranges in a string variab

[amid]

Hi guys,

I have a macro in which I have a string variable named RangeName. I use this variable to select a range of data to copy into an powerpoint slide. However I cant select multiple ranges of data to copy. For eg. when I type:

RangeName = ("x7:ah28,x46:aq67")

I get an error saying 'Run time error 1004: Application defined or Object defined error'. Can someone please help me out with this? Once I have declared the variable RangeName as string. Does the problem lie in this declaration -

Dim RangeName as string

Thanks.

[StephenR]

Remove the brackets, or try this way. EDIT: as Richard points out the brackets do not make a difference.

Dim RangeName As Range

Set RangeName = Range("x7:ah28,x46:aq67")

[Richard Schollar]

Is that the actual line you get the error on (the RangeName = (...) bit because that shouldn't error out. You don't need the surrounding brackets, but that wouldn't cause it to fail. Can you provide the full text of the macro and indicate precisely on which line the error occurs - thanks.

[amid]

Hi guys,

@Stephen: I tried using your idea but it still gives me an error saying 'Run time error 13 - type mismatch'.
@Richard: Removing the brackets doesnt help either. I get the same 'Run time error 1004' I was getting earlier.

Here is my code:

Sub Copy_Paste_to_PowerPoint43()

'Requires a reference to the Microsoft PowerPoint Library via the Tools - Reference menu in the VBE
Dim ppApp As PowerPoint.Application
Dim ppSlide As PowerPoint.Slide

'Original code sourced from Jon Peltier http://peltiertech.com/Excel/XL_PPT.html
'This code developed at http://oldlook.experts-exchange.com:8080/Applications/MS_Office/Excel/Q_21337053.html

Dim SheetName As String
Dim TestRange As Range
Dim TestSheet As Worksheet
Dim TestChart As ChartObject

Dim PasteChart As Boolean
Dim PasteChartLink As Boolean
Dim ChartNumber As Long

Dim PasteRange As Boolean
Dim RangePasteType As String
Dim RangeName As String
Dim AddSlidesToEnd As Boolean
Dim shts As Worksheet
'Parameters

'SheetName - name of sheet in Excel that contains the range or chart to copy

'PasteChart -If True then routine will copy and paste a chart
'PasteChartLink -If True then Routine will paste chart with Link; if = False then paste chart no link
'ChartNumber -Chart Object Number
'
'PasteRange - If True then Routine will copy and Paste a range
'RangePasteType - Paste as Picture linked or unlinked, "HTML" or "Picture". See routine below for exact values
'RangeName - Address or name of range to copy; "B3:G9" "MyRange"
'AddSlidesToEnd - If True then appednd slides to end of presentation and paste. If False then paste on current slide.

'use active sheet. This can be a direct sheet name



SheetName = Sheet44.Name

'Setting PasteRange to True means that Chart Option will not be used
PasteRange = True
RangeName = "x7:ah28,x46:aq67" '"MyRange"
RangePasteType = "Picture"
rangelink = True

PasteChart = False
PasteChartLink = True
ChartNumber = 1

AddSlidesToEnd = True


'Error testing
On Error Resume Next
Set TestSheet = Sheets(SheetName)
Set TestRange = Sheets(SheetName).Range(RangeName)
Set TestChart = Sheets(SheetName).ChartObjects(ChartNumber)
On Error GoTo 0

If TestSheet Is Nothing Then
MsgBox "Sheet " & SheetName & " does not exist. Macro will exit", vbCritical
Exit Sub
End If

If PasteRange And TestRange Is Nothing Then
MsgBox "Range " & RangeName & " does not exist. Macro will exit", vbCritical
Exit Sub
End If

If PasteRange = False And PasteChart And TestChart Is Nothing Then
MsgBox "Chart " & ChartNumber & " does not exist. Macro will exit", vbCritical
Exit Sub
End If


'Look for existing instance
On Error Resume Next
Set ppApp = GetObject(, "PowerPoint.Application")
On Error GoTo 0

'Create new instance if no instance exists
If ppApp Is Nothing Then Set ppApp = New PowerPoint.Application
'Add a presentation if none exists
If ppApp.Presentations.Count = 0 Then ppApp.Presentations.Add

'Make the instance visible
ppApp.Visible = True

'Check that a slide exits, if it doesn't add 1 slide. Else use the last slide for the paste operation
If ppApp.ActivePresentation.Slides.Count = 0 Then
Set ppSlide = ppApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
Else
If AddSlidesToEnd Then
'Appends slides to end of presentation and makes last slide active
ppApp.ActivePresentation.Slides.Add ppApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
ppApp.ActiveWindow.View.GotoSlide ppApp.ActivePresentation.Slides.Count
Set ppSlide = ppApp.ActivePresentation.Slides(ppApp.ActivePresentation.Slides.Count)
Else
'Sets current slide to active slide
Set ppSlide = ppApp.ActiveWindow.View.Slide
End If
End If

'Options for Copy & Paste Ranges and Charts
If PasteRange = True Then
'Options for Copy & Paste Ranges
If RangePasteType = "Picture" Then
'Paste Range as Picture
Worksheets(SheetName).Range(RangeName).Copy
ppSlide.Shapes.PasteSpecial(ppPasteDefault, link:=rangelink).Select
Else
'Paste Range as HTML
Worksheets(SheetName).Range(RangeName).Copy
ppSlide.Shapes.PasteSpecial(ppPasteHTML, link:=rangelink).Select
End If
Else
'Options for Copy and Paste Charts
Worksheets(SheetName).Activate
ActiveSheet.ChartObjects(ChartNumber).Select
If PasteChartLink = True Then
'Copy & Paste Chart Linked
ActiveChart.ChartArea.Copy
ppSlide.Shapes.PasteSpecial(link:=True).Select
Else
'Copy & Paste Chart Not Linked
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
ppSlide.Shapes.Paste.Select
End If
End If

'Center pasted object in the slide
ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

AppActivate ("Microsoft PowerPoint")
Set ppSlide = Nothing
Set ppApp = Nothing

End Sub

Any ideas??
I appreciate your help. Thanks.

[Richard Schollar]

On exactly which line does the VBE report this error when you try to run the code?

[amid]

It gives me an error on the red line in this part of the code:

If PasteRange = True Then
'Options for Copy & Paste Ranges
If RangePasteType = "Picture" Then
'Paste Range as Picture
Worksheets(SheetName).Range(RangeName).Copy
ppSlide.Shapes.PasteSpecial(ppPasteDefault, link:=rangelink).Select

[Richard Schollar]

Try replacing that line with the following and see if the code will now move beyond this point (it may well still error out, but hopefully further along in the code - if this is the case, please indicate where it now errors out):

Application.ActiveWorkbook.Worksheets(SheetName).Range(RangeName).Copy

[amid]

Hi Richard,

It errors out on the same line. I cant figure out a workaround for it. Please share any other ideas that you might have.

Thanks.

[Richard Schollar]

OK my bad, apparently you can't copy multiple selections (assuming that both areas have different rows AND different columns). So you would need to do them one at a time. I hadn't appreciated this before:

If your range had been "x7:ah28,x46:ah67" instead of "x7:ah28,x46:aq67" it would work, but because the second range has different rows AND different columns it fails.

[Richard Schollar]

If you can, I would ensure that your ranges are the same width column wise allowing you to do the paste in one operation:

RangeName = ("x7:aq28,x46:aq67")

This will ensure that the ranges line up when they are transferred to PowerPoint.