+ Reply to Thread
Results 1 to 24 of 24

Copy a range of cells to different worksheets

  1. #1
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Question Copy a range of cells to different worksheets

    Hello All,
    I have on Big excel file that was created by exporting multiple crystal reports. Now from this excel file I would like to copy a range of cells to different worksheets ( have about 15 different worksheets). I can use something like
    Range("A36:M71").Copy Worksheets("1").Range("A1")

    But the problem is range is not fixed all the time in the exported excel file. Can some one please help my with VBA code? The identifier, that I can use is the company name, in the exported excel file i have each report start with my company name.

    Any help will be appreciated.

    Thank you.

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Copy a range of cells to different worksheets

    Is the range that will be copied to the other sheets is fixed "A36:M71"? and the destination will be A1 in all the sheets ??
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Hello,
    Thanks for your quick reply.. Most of the time the range is fixed but if I make any changes to the crystal report then this range(s) get changed. That's the reason I'm looking some dynamic way to identify this range and copy to the destination worksheets. Yes the destination will be A1 in all the worksheets.

    Thanks again..

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Copy a range of cells to different worksheets

    May be something like that
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Thanks for your reply. So I tried the code you send me and when I run, it asked to select the range of cells., instead of that can i identify that range with "My company name" as each new report start with the "My company name". if so how to modify the code you sent me?
    In the send part of the code (start with the for loop), it suppose to copy the selected range to the new worksheet, but it does not copy. Is any modification needed in the code?

    Thanks again.

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Copy a range of cells to different worksheets

    Can you upload sample of your workbook so as to be easier to deal with the issue better?

  7. #7
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Thanks for your reply. Please find attached is the sample data file. In this you will see that the new report start with the heading "My company name" followed by reportname and month end date... Each report needs to copy to the new worksheet (different tabs.)

    Any help would be appreciated..
    Attached Files Attached Files

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Copy a range of cells to different worksheets

    dax007

    This thread is cross posted in at least one other online forum.


    Your post does not comply with Rule 3 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    Please make sure to include the full link, so that other members will be permitted to continue helping you

    Note that if you have fewer than 10 posts, you may need to type the link, or past it in pieces (or ask a mod to do that for you)
    Dave

  9. #9
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Thank you for making me aware of it. I completely understand it.
    I tried to ask the same question on other forum, here is the link,

    https://www.mrexcel.com/forum/excel-...py+range+cells


    But so far I have not received the answer that help me.

    Thanks again..
    Last edited by dax007; 02-05-2019 at 10:06 AM.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Copy a range of cells to different worksheets

    dax007 thank you for the link.

    There have been 6 views so far of your file. So it's being looked at.

    Given the apparent complexity and layout this could take some time.

    If you feel it is not getting sufficient attention wait at least 24 hours since the last post. Then reply to your own thread with the word "bump". That will put it back at the top of the queue of "What's New".

    Hope this helps.

  11. #11
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Hello,
    Since I'm new to VBA, have done some research and come up with the following code,
    HTML Code: 
    But still something is not right because if I un-comment "Set c=.FindNext(c)" the it will copy entire sheet1 to all the worksheets along with graph. But that I do not want. I want each report (start with "My Company Name" in sheet1) to be copied in each different worksheet.

    so if some one can help me on this I would really appreciate.

    Thank you again...

  12. #12
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Hello,
    I have modified my code as below
    HTML Code: 
    This works fine and copy data up to the worksheet 22 (V). But for 23

    Getting following error
    Run-time error '1004':
    Application-defined or object-defined error

    When I click on "Debug" it pointed me to the following line

    Loop Until Left(Cells(LR, 1).Value, 15) = "My Company Name" Or Left(Cells(LR, 2).Value, 15) = "My Company Name"

    Where I do not see anything wrong as it was executed just fine for 22 worksheets.

    Can some one PLEASE help me what is not right in above code or what is still missing?

    Any HELP would be appreciated....

  13. #13
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Copy a range of cells to different worksheets

    Please use [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags for vba code, not html tags. IF you're using the toolbar icons then that is the # icon, not the <> one.

    edited post:-

    Only had a quick look at your code and misread what it was doing.

    What are you trying to achieve with the lines

    Please Login or Register  to view this content.
    LR=0 is causing the error by creating an invalid range when you pass it to Cells.
    Last edited by jason.b75; 02-07-2019 at 01:50 PM.

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Copy a range of cells to different worksheets

    Not sure if I overlooked anything else, but I think this should do what you are trying.

    Please Login or Register  to view this content.
    Please note that this code is written on the assumption that your sheets are named A,B,C,D, etc. If that is not the case, then it will need some changes made to look at the correct sheet names.

    Alternatively, as you were pasting to A1, I assume that the sheets are all blank, so a line could be included to automatically create a new sheet for each report.

    Hope this helps.

  15. #15
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Copy a range of cells to different worksheets

    If you are using the sample workbook you supplied with the most recent code you supplied.
    "My Company Name" does not exist anywhere in the sheet.

  16. #16
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Copy a range of cells to different worksheets

    I think we both misread the original code in similar ways, Dave.

    If what you say was the case, then the code would have failed 4 lines earlier than the OP has advised at
    Please Login or Register  to view this content.
    Also the failure would have been on the first sheet / iteration of the loop, not after 22 successful iterations.

    Best guess says that LR = 0 is returning 0 or negative row numbers to the line that is causing the error.

  17. #17
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Hello Mr. Jason.b75,

    THANK YOU so much for your time and sending the code, which is almost same as what i was expecting. I see one issue, last two reports that suppose to copy in sheet "W" and "X" receptively. But the last report copy in sheet "W" and the 2nd last report copy on Sheet "V" right below this sheet report. So not sure why it does like this..

    One more question that if my worksheets name, 1, 1f ,2,2f and so on, instead on A,B,C, D, in that case what should I change in the code to use that sheet names?

    Once again THANK YOU for your time and help.

    PS.. From next time sure I will make sure to use the "
    Please Login or Register  to view this content.
    " for VBA code. Also,

    If LR > 1200 Then
    LR = 0
    End If

    I was checking that if my row count is greater than 1200 (as I have data always under the 1200 rows) then setup the LR=0

    Thanks again..

  18. #18
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Hello,
    Once again Thanks Mr. Jason.b75 and all others for your time and help me on this issue. Further looking into the code and raw data I found a small issue in my data (My Company Name was spell slightly different, I think it was a typo in the original report) and that's the reason, sheet "W" report was copy into the sheet "V". So far I was looking the code and keep on thinking that what could be the wrong and why it does not work for the last two reports. Even my code the one I posted here was stopping at the same point. Now i fix the report and it worked just as what I was expecting..

    I still like to know that if my worksheets name, 1, 1f ,2,2f and so on, instead on A,B,C, D, in that case what should I change in the code to use that sheet names?

    Thanks again...

  19. #19
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Copy a range of cells to different worksheets

    The method that I used was slightly unconventional and based on the information you gave us to work with. It will only work with sheets with a single letter in alphabetical order, anything else will require one of the more common methods.

    You could either use the same method as you did with your own attempt, sh(1) = "1", etc. then change the copy line to
    Please Login or Register  to view this content.
    Or, if your sheets are in the same order, with no others in between then you could use
    Please Login or Register  to view this content.
    Without the sh(1) ="1" list. Note that this looks at the position (index) of the sheet tab, not the name, when ccount = 1 it will look at the second tab from the left (ccount + 1) = 2. This is on the assumption that the full report where the data is being copied from is the first tab on the left.

    See https://docs.microsoft.com/en-us/off...y-index-number for more on sheet index.

  20. #20
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Hello again,
    I took the piece of my and added that to your code and came up with following.. This work perfectly fine in terms of coping the data but as you can see that my array define is in reverse order, so not sure what needs to be changed in below code in order to fix the array...

    Please Login or Register  to view this content.




    Also tried your suggestion which works perfectly fine..
    Please Login or Register  to view this content.
    so once again BIG THANK YOU to you for your time and help.

  21. #21
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Copy a range of cells to different worksheets

    Should it be 1 then 1f, or 1f then 1?

    I see from your post that the second suggestion works, but I'll 'fix' the first method as well so you can learn from the code.

    This might work,

    Please Login or Register  to view this content.
    Last edited by jason.b75; 02-08-2019 at 04:53 PM.

  22. #22
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Thanks again for your quick reply. So in this case I do not need to use the for loop the way I used in my last post (#20)? Yes my worksheets name like 1f then 1 then 2f then 2 and so on...

    I tried this option plugging to the code

    Please Login or Register  to view this content.
    But when I run it I got following error..
    Run-time error '9':
    Subscripts out of range

    Usually such type of error throw when worksheet name does not page with the name given i array. But I do have 24 blank worksheet with the name starting 1f,1 and so on.
    So what is still missing? Also my array suppose to be in right order means 1f,1,2f,2 and so on..

    Thanks again..

  23. #23
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Copy a range of cells to different worksheets

    Quote Originally Posted by dax007 View Post
    So in this case I do not need to use the for loop the way I used in my last post (#20)?
    That is correct, the Do command is creating a loop anyway, for each time "My company name" appears in the source, so by using For and Next, you're effectively copying to every sheet for every instance of "my company name", I haven't tried that modification of the code, but at a glance, I think that it might be copying the first section of the report to each of the 24 sheets, then overwriting that with the second section, then the third, etc.

    I had a senile moment when posting that suggested line of code, it should have been (ccount -1) at the end of the array, by default an array of 24 items is 0 to 23, not 1 to 24. But looking again, we need to reverse it, so I came up with this.

    Please Login or Register  to view this content.
    Personally, I would have the array in forward order and use the simpler method of (ccount -1) instead, although, unless you're going to change the order of the sheets, or add extra sheets in between, the second method in post #20 will be adequate for your needs.

  24. #24
    Registered User
    Join Date
    01-16-2019
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Copy a range of cells to different worksheets

    Good Morning!
    Thank you so much. It worked.. As of right now I'm not going to change the WS order or name. So this code will work but in future if users would like to change or add new WS then will revisit the code and modify as needed.

    THANK YOU for your all help and time..

+ 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. [SOLVED] Copy and Paste Cells from multiple worksheets based on Input Box range
    By oloyejawnson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-19-2018, 04:35 PM
  2. [SOLVED] Copy cells/range from worksheets positioned between two worksheets
    By ucladidas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-08-2014, 12:12 PM
  3. [SOLVED] copy common cells and variable range from multiple worksheets to single master workbook
    By tg7384 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-15-2014, 03:05 PM
  4. Copy a range of cells without switching worksheets.
    By dbs105 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2013, 05:55 AM
  5. [SOLVED] Copy to this range of cells in multiple worksheets?
    By ThomasCarter in forum Excel General
    Replies: 2
    Last Post: 10-02-2012, 01:56 PM
  6. Copy selected cells from a range of worksheets and paste in master table
    By simba3088 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-20-2012, 07:59 AM
  7. [SOLVED] Copy and paste cells dynamically to a range in a series of named Worksheets.
    By skyping in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-27-2012, 04:22 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