+ Reply to Thread
Results 1 to 12 of 12

Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repeat

  1. #1
    Registered User
    Join Date
    09-30-2015
    Location
    South Dakota
    MS-Off Ver
    Excel 2013
    Posts
    6

    Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repeat

    I have long been searching for the solution to my Excel problem and am about to throw in the towel. Can anyone please help me write a macro that works as described below:

    I have a very large worksheet that has data entered horizontally with a 6 column by 40 row range and I would like to copy that data to a new worksheet and stack it vertically within 6 columns for importing purposes. How can a write a macro that will make this function simple? Every range will be the same size, but I need to move the data from 5,000+ columns on Sheet 1 to only 6 columns by 5,000+ rows on Sheet 2 instead. I have attached a sample spreadsheet to better show what I am trying to accomplish. Thanks in advance for your help!
    Attached Files Attached Files
    Last edited by cjtimmer; 09-30-2015 at 12:47 PM.

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,528

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    Where is the example of how you want it to look after the macro is run?
    As a reminder, once your original request has been fulfilled please mark this thread as SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

    If you are happy with my help, then please consider clicking the add reputation button in the lower left hand corner of this post.

  3. #3
    Registered User
    Join Date
    09-30-2015
    Location
    South Dakota
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    I have updated my workbook to include the data I am looking for on Sheet 2. Technically, the range doesn't need to go A1:F41, but instead, can end when empty. Whatever is easiest.
    Last edited by cjtimmer; 09-30-2015 at 12:50 PM.

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,528

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    Sorry to be a pain, but I need to figure out how to find, via code, the actual data, so I would like to see how the data would be set up if there was more data. Specifically if there was more data below what you have. Is it always going to be formatted the same way with the header row Item # etc and some data below that and then another header row for a new section of data below that and the header rows would all be on the same row across?
    I modified your sheet to show what I am asking. I just copied the same data, but I think you see what I mean.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-30-2015
    Location
    South Dakota
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    No pain at all. If I can find a solution, it will be time well spent.

    Every section will be formatted exactly the same. Section # identifier in row 1 spanning 6 columns, Section Name identifier in row 2 spanning 6 columns, BOM Description in rows 3-7 spanning 6 columns, and Data Headers in row 8 spanning 6 columns. This is consistent throughout the entire spreadsheet as 6 columns wide; however, each 6 column section may contain more or less row entries than others. Please let me know if this doesn't better explain my spreadsheet. Thanks!

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,528

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    That's good, but here's what I'm the most interested in.

    If you look at the sheet I posted in post #5

    You see how I pasted some more data, highlighted in yellow below your original data.

    I have some questions. Would there ever be another section of data below what you included in your example?

    If so, you see how I have in row number 25 the header row for the new set of data A25 and the second set of data G25 and both are on row 25, would this be the case if you have sets of data below the original.

    Here's the technical reason I ask. If they would be on the same row then I would just have the code look for Item# in column A and I would have my new row to start looking for data to copy, otherwise for each section of data I would have to search for the item# gain.

    So if in the second section of data instead of item number being on the same row, row #25 it was staggered and might be on row #27, then I need to know this.
    Last edited by skywriter; 09-30-2015 at 02:56 PM.

  7. #7
    Registered User
    Join Date
    09-30-2015
    Location
    South Dakota
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    No, there will never be another section of data below the first. ITEM #, BOM QTY, PRINT, BOM ID, SEQ #, NEXT BOM, will only reside in row 8 only.

  8. #8
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,528

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    Okay the last thing I should have asked and didn't, is row 9 always empty like you show in your sample?

    I went ahead and assumed it was. If it's not let me know it's an easy fix.

    Alt + F8 to bring up the macro list, then run the macro.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-30-2015
    Location
    South Dakota
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    That's perfect! You made it look so simple. Thank you very much!

    One last question, if you don't mind. If I also needed the BOM Description (rows 3-7) to repeat itself for each section, within a new column, could this be added to the macro? I've attached a new file to better describe what I need to happen (highlighted on Sheet 2).
    Attached Files Attached Files
    Last edited by cjtimmer; 09-30-2015 at 04:02 PM.

  10. #10
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,528

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    Try this.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-30-2015
    Location
    South Dakota
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    I'm sooo close but something isn't allowing my actual spreadsheet to complete correctly all the way through. I have attached my actual spreadsheet and you can see on the "BOM Import" sheet, the data is transferring properly up until Row 369, and then the data moves into the improper column and shortly thereafter, the data skips around spontaneously. Is there anything in the code that would cause this or do you have any ideas? I did notice that it completely stops reporting the BOM Description at this point as well.
    Attached Files Attached Files

  12. #12
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,528

    Re: Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repe

    When you get to column RM there's now seven columns until the next item# label. RM, RN, RO, RP, RQ, RR, RS, then the next item# label is RT. You have 2 SEQ# labels. So after that the columns the code is looking for the data in are all off.
    I would check for other extra columns.
    Last edited by skywriter; 09-30-2015 at 06:33 PM.

+ 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 range and paste on Sheet1 but after A6
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-16-2014, 02:42 AM
  2. [SOLVED] Copy and paste range as values to specific sheet/range
    By lukestkd in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-06-2014, 09:48 PM
  3. [SOLVED] Copy Range of data from sheet1 and sheet2 paste it in sheet3
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2013, 05:55 AM
  4. Copy Range of data from sheet1 and sheet2 paste it in sheet3
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2013, 05:47 AM
  5. Replies: 3
    Last Post: 03-26-2013, 06:06 PM
  6. Replies: 4
    Last Post: 03-17-2013, 05:58 AM
  7. Copy dynamic range from previous sheet and paste after last used row in a range
    By Mistweaver in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-04-2010, 06:24 PM

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