1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel formula to copy number of rows?

Discussion in 'Business Applications' started by csmgrgirl, Jan 17, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. csmgrgirl

    csmgrgirl Thread Starter

    Joined:
    Jan 17, 2012
    Messages:
    6
    I am not sure if this is possible, but need to give it a shot. Hopefully someone understands what I mean!

    I need to create a workbook with two sheets. On the first sheet are going to be options where someone (not myself) will need to input a number for a quantity of an item. On the second sheet will be rows of specifications for different items. I need to know if there is a formula that will take that number and copy/paste that many of the row that I link it to. I'm thinking I may need a third sheet? Number is input on sheet 1 and linked to sheet 2 where it copies a specific row and then pastes that many rows onto sheet 3?? The most important part of the problem is that it has to copy an entire row (or say 7 cells from a particular row) not just one cell.

    Help!! :confused:
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi welcome to the forum,

    This will be macro work and it's not really that difficult but I've no idea waht your sheet's layout looks like and very important you should always mention the version of Excel you're using because of functionality in the most recent versions which si no longer supported in the older versions (2003 and older)

    There are quite a few postings, I don't remeber them on this forum with similar requests but I don't remember the poster's names, you could use and advanced search.

    If you attach a sample file I could take a look and make some suggestions
     
  3. csmgrgirl

    csmgrgirl Thread Starter

    Joined:
    Jan 17, 2012
    Messages:
    6
    Sorry for not having more details. I'm just beginning work on the spreadsheet itself so I will post that when I have it started. I am working in Excel 2007. Do I have the correct terminology to search other threads? I'm not even sure if I'm wording it correctly.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    your key words are copy cells, copy range you name it.
    You need a least a minimum of idea what you want and need before you start asking.

    Google is a perfect search engine for this, but don't let the guessing what you need to others, be concise and try to explain as it is, that will make it easier to understand for the ones that are willing to help.

    You can't just walk into a mechanic workshop and say it isn't working. :)
     
  5. csmgrgirl

    csmgrgirl Thread Starter

    Joined:
    Jan 17, 2012
    Messages:
    6
    Ok, here is the basic beginning of the spreadsheet showing the end result of what I want the formulas/macros to do. On Sheet1 you can see that I have entered quantities for two of the items. Then on Sheet2 are rows of information for each of those same items. I need to then have on Sheet3 the row from Sheet2 copy/pasted into the quantity of rows from Sheet1.

    I hope that makes enough sense for someone to help me!!

    Thank you!!
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Okay, it's clear and I understand what you need / want, you'll have to be a little patient, but I'll be able to write the necessary macro to do this for you.

    If you're going to implement it in another file it may need editting but we'll get to that when the time comes, first see if what I can do for you is what you really wanted to achieve.
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Well, it was easier that I thought.

    I've attached the macro enabled version (xlsm) and the macro is in module1

    You will need to allow macro's if you want this to work.

    Press the keycombination Shift Ctrl K to run the macro or just run if from the macro menu

    It will add the number of rows for that person for that decription in sheet 3 and if successfull the values in the input sheet, Sheet1 will be removed to avoid repetion.

    For those combinations Name / Description not found the value remains where it is.

    I've not added much comments to the vba code but it will work.

    The only condition for this macro to run correctly, should you move it to another sheet then you will have to enter the sheets names in the correct places in the macro code:

    Search for "Sheet1", "Sheet2" and "Sheet3"
    and replace these three values with your sheet names.

    You only need to it at the beginning of the macro since I use varaibles to address these sheets.
     

    Attached Files:

  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    BTW, the macro is built in such a way that you can add columns and rows to Sheet1 (the input sheet).
    I did not hardcode the range you have but coded it to allow for expansion.
     
  9. csmgrgirl

    csmgrgirl Thread Starter

    Joined:
    Jan 17, 2012
    Messages:
    6
    Amazing!! I'm going to add rows down on Sheet2 for the specs for the other two columns on Sheet1. Will that work the way it's set up now or will I need to edit or add to the macro? I'd like to learn how to do it myself if possible.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    If you stick to the column layout you have now it will work without a problem.
    Learning, well just check for VBA tutorials via Google and practice onyour own recorded macros, that's the way I started several years ago
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Have you tried it?
     
  12. csmgrgirl

    csmgrgirl Thread Starter

    Joined:
    Jan 17, 2012
    Messages:
    6
    This is perfect and works great! However... we just tried using it with our software and realized that we need to switch sheets 1 and 3. Basically it's because the software we are using needs to read the info from the "target sheet" on Sheet1, not Sheet3. I tried just swapping the sheet placement and then renaming Sheet1 and Sheet3 in Module 1, but it doesn't seem to have worked. Do I have to change it somewhere else? I thought for sure I had it under control now, but apparently not! :eek: Your help is so very much appreciated! Attached is the edited spreadsheet I am working with.

    Meghan :)
     

    Attached Files:

  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I'll check it, you probably want to keep the cigars for yourself :)
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Well, Catherine is not in the database sheet so it doen's work
     
  15. csmgrgirl

    csmgrgirl Thread Starter

    Joined:
    Jan 17, 2012
    Messages:
    6
    OMG, I didn't even realize that! Thank you so very much for your help to see the obvious. And thank you even more for figuring out what I was talking about and creating this for me. You were more of a help than you will ever know! I believe in Kharma, and I believe you will be getting something really great back for your selflessness!

    Best wishes!
    Meghan
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1036763

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice