(xlRange As Range) from a cell value

[Karthik Thandapani]

Hi,

I have an existing module in which ranges were hard-coded and from that the slides are copied to PPT. Presently, there is an upgrade as the range should be taken from the cell value shown as below.

fyi, Private Sub ExcelTableToPowerPoint(xlRange As Range)
ExcelTableToPowerPoint (ActiveSheet.Range("B5:D10")) - Previously

Presently, I have the range in cell H7 as below
Worksheets("Sheet1").range("A1:G45")

Any help on this how to capture the above here;
ExcelTableToPowerPoint (ActiveSheet.Range("B5:D10"))

Thanks,
Karthik

 

[Smallman]

Hi Karthik

I am not sure what you are asking. Are you trying to trap a dynamic range that has its origins in a cell. That would mean every time the range changes you would need to change the cell contents.

Maybe you could trap some constant in VBA which identified your dynamic range or more simply just trap your range dynamically with VBA.

Any clarity would be helpful. If you say what you are trying to achieve you will get the best outcome, oh a file would help too.

Take care

Smallman

 

[Karthik Thandapani]

Hi, theBigGuy (since you come frwd to help, yes, you're)
I've a file attached for you.
To provide a summary, the macro button helps to get the work done. But recently we UPed the game with yes, dynamic ranges provided by the user in a cell.
Please see the attached file to get more info. Thanks!

 

[Smallman]

Hi Karthik

You don't need to select a sheet to do something on it. I am not sure if this will work with your complete procedure but this is the theory.

For Each ws In Sheets
    ExcelTableToPowerPoint (ws.[a1].CurrentRegion)
Next ws

As your list grows on each sheet the code will trap this movement.

Take care

Smallman

 

[Karthik Thandapani]

Sorry, may be I can explain more elaborately.
Ignore other sheets, just concentrate Sheet!Quarter1
You see columnM, thats where the slides will capture its range.value from. My requirement is to start loop from cell M9 to cell.value = empty

" For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ExcelTableToPowerPoint (ActiveSheet.Range(""A1:C5""))
    Next ws"

Presently, I've above code that will loop all available sheets and capture ranges only from ("A1:C5"). But we are now making it dynamic and allow users to input range.
Code snippet to generate slides is in place. But below are my requirements.

1. Loop each cell in column M
2. Capture cell.Value in column M as range

Many thanks!

 

[Smallman]

Hi Karthik

See this bit

Presently, I've above code that will loop all available sheets and capture ranges only from ("A1:C5"). But we are now making it dynamic and allow users to input range.

I understood this utterly and completely. See that code in post 4 - should be perfectly dynamic based on what ever is in the sheet no matter how big or small.

Also tried to explain that you do NOT need to select sheets in order to do things on the sheet.

Hope you have time to test the code I provided.

Take care

Smallman

 

[Karthik Thandapani]

@Smallman : I think there was a confusion. Please ignore the data you see starting cell "A1". As i mentioned, we have upgraded the option so the user will provide the ranges to be exported into charts.

For obvious reasons, regardless to the data, users have the flexibility to input ranges in column M. From there macro will read each cell M9 in column M and take that as a range for slide1, cell M10 for slide2 and so on.

Hope this helps.

 

[Smallman]

Hi Karthik

Would it be fair to say this is what you are looking for;

Sub Loopit()
Dim i As Integer
    For i = 9 To Range("M" & Rows.Count).End(xlUp).Row
        ExcelTableToPowerPoint(ActiveSheet.Range("M" & i)).Value 
    Next i
End Sub

Take care

Smallman

 

[Karthik Thandapani]

@Smallman : I'm getting compile error. Expected function or variable. And its highlighting here: ExcelTableToPowerPoint (between the loop range).value

 

[NARAYANK991]

Hi Karthik ,

A problem which is clearly and well defined is a problem half-solved.

Let us leave aside your code , and concentrate on what you want to do.

Do you want that :

the ranges A1 : C5 on every worksheet should be transferred to a PowerPoint presentation ?

Next , instead of a static reference such as A1 : C5 , do you want a dynamic range to be passed as a parameter ?

Next , how will the range on each worksheet be specified ? Do you want something like Range("A1:A" & lastrow) , where lastrow is derived , or do you want that the code will step through the range J9 : K11 on the tab named Quarter1 ?

Narayan

 

[Karthik Thandapani]

Hi Narayan,

Instead of a static reference such as A1 : C5 , do you want a dynamic range to be passed as a parameter ?
-Yes, each slide's range is derived in column M. For an example, cell M9 has
Worksheets("Sheet1").range("A1:G45") - as this is the range to get captured in slide1. Slide2 will capture range.value from cell M10.

And please expand the code to loop through column M.

I have attached an updated file.

Thanks in advance!

 

[NARAYANK991]

Hi Karthik ,

See if this is OK.

Narayan

 

[Karthik Thandapani]

@NARAYANK991 : looping is working like a breeze.
But there is a problem. What was requested is to capture range which is in cell M9.
Worksheets("Sheet1").range("A1:G45") as this would capture range("A1:G45") from sheet1.

Thanks!

 

[NARAYANK991]

Hi Karthik ,

See the file now ; I have changed the called subroutine , and you will not be able to use it with your earlier calling procedure.

Narayan

 

[Karthik Thandapani]

thanks @NARAYANK991 : it works like a breeze