+ Reply to Thread
Results 1 to 19 of 19

Need help with for each method

  1. #1
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    137

    Need help with for each method

    Hello Dear community,

    I use following VBA to copy paste data from excel to ppt.
    It copy range from each Worksheet.
    Can anyone help me to make a little change, so instead of each WS it can copy from range.

    What I want to achieve, something like:

    Dim k as range
    k = 18

    For each range Range("A11:I" & k)

    next
    k + 10

    Please Login or Register  to view this content.
    Full code
    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,690

    Re: Need help with for each method

    Do you want to take a picture of 8 rows (11 - 18) and 9 columns (A - I) (=Range("A11:I18") and place this in PowerPoint.
    Now do the same for a new range. How many rows down from 11 is this start of the new range? 10 cells? Range("A21:I28")?
    How many times do you want to do this or at which row number do you want to quit? Last used row?
    Are there empty row(s) between these blocks of data?
    We live in a time where intelligent people are silenced so that stupid people won't be offended.

  3. #3
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    137

    Re: Need help with for each method

    Quote Originally Posted by jolivanes View Post
    Do you want to take a picture of 8 rows (11 - 18) and 9 columns (A - I) (=Range("A11:I18") and place this in PowerPoint.
    Now do the same for a new range. How many rows down from 11 is this start of the new range? 10 cells? Range("A21:I28")?
    How many times do you want to do this or at which row number do you want to quit? Last used row?
    Are there empty row(s) between these blocks of data?
    Hello jolivanes, thanks for reply.
    Answer to all your question is "yes"
    Right now in sheet. I have 54 ranges.

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,690

    Re: Need help with for each method

    Please Login or Register  to view this content.
    'If you want to store the pictures on the sheet and retrieve later

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    137

    Re: Need help with for each method

    Quote Originally Posted by jolivanes View Post
    Please Login or Register  to view this content.
    'If you want to store the pictures on the sheet and retrieve later

    Please Login or Register  to view this content.
    Thank you very for reply. I tried 1st option. But it only copy and paste whole range at once. ( as single picture )
    Any idea what am I doing wrong?

    Please Login or Register  to view this content.
    Last edited by Akbarov; 08-04-2022 at 02:08 AM.

  6. #6
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,690

    Re: Need help with for each method

    Don't create non needed clutter by quoting.
    You said that there were empty rows between ranges to be copied. If the rows are not empty the code as it stands will not work.
    You might have non printable characters in these rows.
    If there are no empty rows, code has to be changed.
    Let us know what it is.

    Attaching your workbook would be the best.

  7. #7
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    137

    Re: Need help with for each method

    I tried with empty rows it works perfectly fine. But I have empty cells in some rows. Can there be different option?

  8. #8
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,690

    Re: Need help with for each method

    Try so.
    Please Login or Register  to view this content.
    If it does not work, attach your workbook, cleaned of any personal data like names, email addresses etc.

  9. #9
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    137

    Re: Need help with for each method

    Hello jolivanes,
    Can you give me a little explanation about your last code?
    I want to make it loop to the right , instead of down, but I do something wrong I think.
    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,690

    Re: Need help with for each method

    Please Login or Register  to view this content.
    If you want to loop to the right, you have to give us some range examples.
    For instance:
    Go to range A1, resize 5 rows and 3 columns, take picture and paste somewhere in relation to A1.
    Move 5 cells to the right and resize same as before, take picture and paste somewhere in relation to this last cell.
    Move another 5 cells to the right again, resize etc etc.

    Give us a clear example to work with.

  11. #11
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    137

    Re: Need help with for each method

    Thanks for answer.
    So range is simple: 1Row and 10 columns. F70:O70 then P70:Y70 etc..
    Is that possible to not paste pictures to worksheet? or is it important. It makes workbook so messy.

  12. #12
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,690

    Re: Need help with for each method

    Where do you want the pictures stored or pasted?
    You can store the pictures in a temporary folder also if you want.
    I don't know anything about transferring to Powerpoint but you can transfer the range directly to Powerpoint I imagine. I mentioned that in Post #4.
    BTW, you said that it makes the sheet messy but I was under the impression that the pictures would be transferred into Powerpoint and later deleted from the sheet.

  13. #13
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    137

    Re: Need help with for each method

    Loop solved. Only problem left is mess.. each time I run code it adds 50 pictures to worksheet each time..
    Last edited by Akbarov; 08-05-2022 at 10:39 PM.

  14. #14
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,690

    Re: Need help with for each method

    Been waiting on an answer for Post #12.
    But if you want to just delete the named pictures after copying to PowerPoint, this should do that.
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    137

    Re: Need help with for each method

    This code will do what I want.
    But I made some changes in code.
    To copy multiple pictures at once.. It copy ranges to worksheet, but transfers only second range to PPT.
    May be you can give me some suggestion?..
    Please Login or Register  to view this content.

  16. #16
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,690

    Re: Need help with for each method

    See what happens if you move the "k = k + 1" down to just before "Next i"

  17. #17
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    137

    Re: Need help with for each method

    Tried, still result is the same. It copy-paste second range. Can I name both ranges with different name? like myShape , myShape1 ?

  18. #18
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,690

    Re: Need help with for each method

    They have to have different names.

  19. #19
    Forum Contributor
    Join Date
    06-30-2018
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2016
    Posts
    137

    Re: Need help with for each method

    I get error: Unable to get pictures property in worksheet class.

    It happened after I tried to give each element different names
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 07-01-2021, 02:59 PM
  2. Feedback Appreciated: VBA Coding Method: Simple Number to Words using SLST Method
    By MohsenAlyafei in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2019, 05:42 AM
  3. Replies: 2
    Last Post: 09-25-2012, 12:53 AM
  4. A More Efficient Method Than the Evaluate Method?
    By anthony.mcgovern in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-22-2011, 05:39 AM
  5. Returning the number of cells from one method to another method.
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-08-2010, 11:28 AM
  6. GetObject method not work after Call Shell Method
    By ben in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-21-2006, 11:50 AM
  7. Why QUIT method doesn't work after COPY method?
    By surotkin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2005, 11:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1