VBA/Macro to pull referenced sheet name into new sheet

[wahlquil]

Hello everyone -
I have never posted on this forum but use it often. Hope to find an easy answer to my problem.

I have copied/pasted a macro into my excel2011 workbook. It is the following:

Please Login or Register  to view this content.

What this does is create a new sheet at the end of my workbook, and pulls all the data from each sheet and pastes it in order on the new sheet.

I would like to add something to this macro that will insert the name of the sheet and puts it in a row right before the data from said sheet is pasted.

Hope this makes sense - I'll answer any questions that will help my question get answered!

Thanks so much,
Lindsey

[BellyGas]

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

To create an empty row between data areas (notice the +1). Then add:

Please Login or Register  to view this content.

just before the

Please Login or Register  to view this content.

[wahlquil]

Thank you for the quick reply BellyGas!

I have entered what you said, and the result is Run-time error '1004': Method 'Range of object'_Global' failed.

Any suggestions?

Thanks again.

[BellyGas]

When you get that error, click debug and copy the line of code you'll see highlighted into this forum. There'll be a reason like a misstyped character or maybe a variable used in the command doesnt have a value.....but without knowing which line it errors on....

[wahlquil]

Hi BellyGas -
Thanks again. This is the line of code that is highlighted as error:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Try this one:

Please Login or Register  to view this content.

[wahlquil]

Thanks for the reply John!
It appears that this helped. No error, and the sheet names are pulling in ahead of the sheet data!
Is there any way to specify formatting for the sheet names (ie, bold, font size, all caps, etc.)?

[alansidman]

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.)