+ Reply to Thread
Results 1 to 10 of 10

creating a variable range in VBA to be copied to variable sheets

  1. #1
    Registered User
    Join Date
    02-01-2010
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2007
    Posts
    42

    Lightbulb creating a variable range in VBA to be copied to variable sheets

    I'm close but can't figure out what I've got wrong.

    The "Interface" tab has a field of information that should be distributed (by row) to variable sheets name "1" through "32".
    column E (starting from row 83) contains numbers (variable) (1; 2; 6 - for example) that represent the sheet name where the info should go to.
    Column F (starting from row 83) contains the cell reference (variable) where the info should be pasted ($A$35 - for example).
    Columns H through U are the data that should be copied (not cut) and pasted.


    Please Login or Register  to view this content.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: creating a variable range in VBA to be copied to variable sheets

    Hi, adamNBU,

    maybe like this:
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: creating a variable range in VBA to be copied to variable sheets

    Maybe:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    02-01-2010
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: creating a variable range in VBA to be copied to variable sheets

    Perfect, Holger. You did in 5 minutes what I was trying to do all last night.

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: creating a variable range in VBA to be copied to variable sheets

    Hi, adamNBU,

    I´m pretty sure that Jerry´s code is doing the same as well (with less variables than mine). Glad you got it solved - it actually took me 8 minutes

    Ciao,
    Holger

  6. #6
    Registered User
    Join Date
    02-01-2010
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: creating a variable range in VBA to be copied to variable sheets

    Thank you both!

    I will try Jerry's code as well.

    One last question: when I use Holger's code, the pasted data is without formatting. How can I paste this without changing the formatting to the pasted sheet?
    Last edited by adamNBU; 09-29-2013 at 01:15 PM.

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: creating a variable range in VBA to be copied to variable sheets

    Hi, adamNBU,

    AFAIK that could only be achieved by using PasteSpecial like
    Please Login or Register  to view this content.
    Ciao,
    Holger

  8. #8
    Registered User
    Join Date
    02-01-2010
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: creating a variable range in VBA to be copied to variable sheets

    Jerry's code does not change the destination formatting so, problem solved

    Thanks, guys!

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: creating a variable range in VBA to be copied to variable sheets

    We are a village...

  10. #10
    Registered User
    Join Date
    02-01-2010
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: creating a variable range in VBA to be copied to variable sheets

    Since you guys were so quick on solving this issue, perhaps you know what i'm doing wrong in this other post I just posted.
    It's a similar issue.

    http://www.excelforum.com/excel-prog...70#post3422170

+ 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. Help in selecting variable range while creating macro
    By Nayaz79 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-17-2013, 01:21 AM
  2. Call a constant/variable by creating another variable
    By pienaaj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-12-2013, 10:36 AM
  3. Sum Across Variable Range of Sheets
    By QuestaTH in forum Excel General
    Replies: 2
    Last Post: 10-14-2010, 05:11 PM
  4. Creating chart by passing sourcedata as variable range
    By lavi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-07-2010, 09:18 AM
  5. range variable across sheets
    By Ward Germonpré in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2005, 03:06 PM

Tags for this Thread

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