Generate subsheet or workbook based on master

Hi all

I need a macro that can copy data from a master sheet onto either a subsheet or another workbook. I would very much like the following:

- be able to set a column range so I can pick out certain columns to be copied, as well as a row range. What would be extremely neat, is if the columns are a part of the macro i.e. fixed, while the row range could appear as an input option when the macro button is clicked.

- because I need 3-4 specific subsheets or workbooks copying different columns from the mastersheet, and those who are gonna use it are very unskilled in excel, I would like it as a macro button that can be named.

- it needs to be quite simple, because I'm quite the beginner at this stuff, which also means that a short tutorial would be much appreciated.

- it also needs to function in both excel 2010 and 2013.


In advance, thank you so much for your help and sorry for butchering the macro lingo.

Kind regards Chris

It would be easier to follow and test possible solutions if you could post a sample file with some data to demonstrate what you want to do. Explain in detail, step by step what you would like to see happen referring to specific cells and worksheets.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

@Mumps: A link to the sheet is posted here below. Basically what I want is to pick certain columns and a range of rows from the sheet named "uddannelser" and then copy it onto a new sheet or workbook - sheet1 is an example of how it should look.

@alansidman: I followed your instructions, but couldn't get it to work. Got an error at the end of the input options. Could you give me an example, perhaps based on the linked sheet below. I might have entered the columns, rows, and sheet wrong. I tried for columns: A1:A5 | for rows: 35 | for sheet: sheet1 (a sheet that was already created - should it not be?).

http://www.megafileupload.com/ojM7/Macro-needed.xlsx

Thanks in advance for all your help!

Kind regards Chris

Hi Chris. I'm sorry but my antivirus software blocks the site where you have uploaded your file. Please try a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Originally Posted by Mumps1

Hi Chris. I'm sorry but my antivirus software blocks the site where you have uploaded your file. Please try a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

https://1drv.ms/f/s!ApOE5FTZWJ3whJEytqrbMRgC5Pvplg - hopefully this works, its via ondrive.

Again what I am looking for is a macro where I can:

- type in the specific columns I want it to copy, but into the macro-code / VBA itself, NOT as a pop-up input option.

- I would like the row range to pop-up as an input option though.

- I would like it to output the selected columns to a new workbook, and NOT a sheet.

- It must be able to both function in Excel 2010 and 2013.

Basically something like this, but with a row range option:

Please Login or Register  to view this content.

In advance, thank you so much for the help!

Kind Regards Chris

Try:

Please Login or Register  to view this content.

@Chris

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

Try:

Please Login or Register  to view this content.

I get the run-time error '9' : "subscript out of range", when using your script.

I have been using the macro below with great success, and I think it is simple enough for others to comprehend as well. However, I still need a "row range" pop-up input option. Is it maybe possible to add that to the macro, without altering to much?

Please Login or Register  to view this content.

Again, thank you so much for all your help. Sorry for being a complete rookie at this

Originally Posted by Mumps1

Try:

Please Login or Register  to view this content.

I entered some dummy data in columns H, K and L in the file you posted and then ran the macro and it worked as you requested. Which line of code was highlighted when you got the error?

Try:

Please Login or Register  to view this content.

It was my pleasure.