how to insert row to multiple worksheet..?

[eing]

I have a lot of sheet that need to be inserted by rows..
But, it is really wasting my time if I have to insert it one by one on each worksheet..
Do you have any solution to solve the problem please..?

[Jul Stev]

Are the inserted rows in a same place for each sheets? If yes then you can try to block all the sheets ( Hold CTRL key and choose all the sheets you want ) then insert the rows in any sheet. The inserted rows will appear in every sheet that you have block.

[eing]

thanks for the the reply..
yes, I inserted rows in a same place of each sheets..
I've tried the control key and it really works to the worksheets..
but I have another problem..
Sometimes, I always forgot to ungrouping the tab that make all of the data in each worksheet changed..
So, I'm thinking about inserting the row automatically and without grouping the tab..
Is there any solution to do this..?

[herukuncahyono]

hi eing,
Would be better if you send samples(attachment worksbook-with content that is not exactly the same as your "project"),and more specific questions,so we can find a solution (together)

[HaHoBe]

Hi, eing,

the following code will insert a row in each worksheets at the position of the ActiveCell (even if you have marked a range):

Please Login or Register  to view this content.

Ciao,
Holger

[eing]

I don't think the attachment will helping me explaining it..

maybe I can use this example..:
I just want to insert a row between D & E in the sheet 1.
When I inserting the row, automatically the D & E row in the sheet 2 inserted too..
But, I need to do it automatically..
Maybe I need VB code to do this, but I didn't have any knowledge about it..
That's why I asking this in the forum now..

[HaHoBe]

Hi, eing,

Rows are marked with Numbers on the left side of the worksheet (thatīs what my code picks up on) while D & E stand for Columns loacted at the top of the sheet - you need to make clear if you want rows inserted or columns.

For Columns you might use

Please Login or Register  to view this content.

Ciao,
Holger

[eing]

@ HaHoBe

I've tried to copy your code to my workbook's VB, but it didn't running well as I want..
Have any idea..?

[eing]

@HaHoBe
I'm sorry I've mispelled it..
What I mean is row 4 & 5, not D & E..
Thanks for the correction..
:p

Your code is working with my workbook..
but it's insert the row if I run the code from the VB..
I'm so sorry, but that's not what I truly meant..

it is some kind like this:
-I want to insert the row of sheet 1 to 7, but not with the sheet 8 & 9..
-In the sheet 1, I pointing my mouse to row 4..
-I right-clicking on it, and clicking the insert button..
-when I did this, I see sheet 2,3,4,5,6,7 were inserted too..

that's what I trying to catch..
can you helping me with it, please..?

[HaHoBe]

Hi, eing,

But, it is really wasting my time if I have to insert it one by one on each worksheet

I'm so sorry, but that's not what I truly meant

Are you settled with what you want and what you tell us by now?

Code goes into ThisWorkbook and will only work if you right click into Column A of the sheets:

Please Login or Register  to view this content.

Ciao,
Holger

[eing]

Are you settled with what you want and what you tell us by now?

Sorry I couldn't explain it well to you..
I'd trying hard to explain it well..

I've copied your last code to my workbook, but I don't know how to run it..
I tried to insert the row but nothing happened to the other worksheet..

[HaHoBe]

Hi, eing,

code must be inserted into ThisWorkbook and will be triggered if action within any sheet in Column A is taken.

Ciao,
Holger

[eing]

I've tried, but maybe I'm still not be able to do it as it should be..
I'm sorry for asking this but maybe you could upload the sample workbook of your code please..
So, I could knew why I can't run it on my own workbook..

[eing]

@HaHoBe
Now I understand what you mean about it and how to use the code..!
Thank you..


But I think there's still a weakness in this code..
It's only work for inserting the row, but when I try to deleting the row, It didn't working to the other worksheet..
I'm sorry, It is out of my calculation before..
I should have known this so you can make a more specific code for me..
Would you modify the code, please..

[HaHoBe]

Hi, eing,

But I think there's still a weakness in this code..

What did you ask for in the thread title? Is the code provided doing what you asked for?

AFAIK there is no event that is being triggered if a row or column is deleted - there are only workarounds to catch this. Had you mentioned deleting in your opening post I can assure you I would have passed this thread for sure.

Ciao,
Holger

[HaHoBe]

Hi, eing,

the easiest way I found was using another event in Column A to trigger the deleting of rows (DoubleClick). Code goes into ThisWorkbook as well:

Please Login or Register  to view this content.

Ciao,
Holger

[eing]

Yeah you're absolutely right..
I should've make a more specific thread in my post for my problem..

But thanks for your help, now I can have some code to doing my job..
I'm really appreciate your help..
thank you..

[eing]

Hi HaHoBe..
Your code is really running good in my worksheet..
But I'm feeling a bit uncomfortable when I using the rightclick to insert the row and the doubleclick to deleting the row..
I'm just thinking, maybe it would be better if the command added with the ctrl or alt key..
Somekind like this:
to insert the row = click the cell in Column A and press Ctrl+RightClick.
to delete the row = click the cell in Column A and press Ctrl+DoubleClick.
I hope you're willing to modify the code, please..
thanks..

[HaHoBe]

Hi, eing,

I can understand that but I havenīt seen any code on using events with Alt or CRTL. Maybe we could use the event to show a UserForm with 2 radiobuttons for inserting and deleting and handle that variable to decide whether to delete or insert?

Ciao,
Holger

[eing]

I think it would be nice..
I'll be waiting for the code then..

[HaHoBe]

Hi, eing,

code for ThisWorkbook:

Please Login or Register  to view this content.

Code for a standard module:

Please Login or Register  to view this content.

Code behind Userform frmQuestion:

Please Login or Register  to view this content.

Ciao,
Holger

[eing]

I've test the code and it is running well..
But when I want to edit the column with the right click, why the form question still appearing..?
It should be just happen to the row right.?
Are you trying to make it worked to the column too..?
That will be nicer if you do..

I'm sorry, but what I'm trying to say is, sometimes I need to modify the row with the right click menu..
such as paste special the entire of row 15, change the format cell of the row 2, etc..
That's why I'm asking you to add the event with the ctrl/alt button, because I won't be able to edit the row if the right click only can be used to insert the row for the selection sheet..

[HaHoBe]

Hi, eing,

Your post does not comply with Rule 8 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).

http://www.ozgrid.com/forum/showthread.php?t=180854

Ciao,
Holger

[eing]

I don't know if I was cross posting this thread to the other forum or not..
But, Isn't it a different question I offer to the ozgrid?
Here, I was trying to know how to inserting & deleting the row right..? only to insert & delete the row..
But at the ozgrid's thread, I was asking on how to insert/delete the column and copying the formula too..
Is it still the same thread I was posted..?
I don't know if it was..
This is my first time been in the forum..
I'm sorry for breaking the rules..

But please, I need to know how to solve these problem for my project..
How to insert/delete the row, and how to insert/delete the column and copy the formula..
I need the Both of them..

Some people say inserting the row is essentially the same things as inserting the column..
I don't know if it was right or wrong..
But sorry, I have no any experience in using the VB than this, that's why I was asking the both thread..
If I have one, maybe I just need to edited the codes myself. And I wish I could do that so this will be never happened..

And HaHoBe, I apologize, I don't want to ask the one in ozgrid's here because I don't want you to asked me again what I was asking for in the first post of my thread before..
Please no hard feeling on this and keep helping me here, will you..?

Maybe you can use the code that nilem (in the ozgrid thread) was posted..
http://www.ozgrid.com/forum/showthre...=180854&page=2
It's almost close to what I was hoping about..
But I don't know which part I should erase in the code, because each time I try to erase the copy formula part and change the words column to row, error always come to my excel..
Besides, it's applied the code to all of the sheets, not the selected as you were offered to me before..
Sorry for this inability..
And I'm sorry if you think I've wasted your time to helping me out..
Thank you..

[kshnoije]

Good day. I realize this is a very old thread, but I'm a new poster with limited Excel 2013 experience and was hoping for some assistance. My issue may be similar to previous post but it's not clear. I'm not familiar with VBA and would like to tackle this without VBA if possible. Here's my scenario (spreadsheet example attached)...

I have two worksheets in a workbook. Sheet 1 is source data of teammates (index number, first and last name, email, phone, etc.) listed alphabetically. Sheet 2 is our teammate training record. It imports the first 3 columns from Sheet 1 (index number, first and last name) using VLOOKUP, and I have 12 additional columns for me to enter their monthly training attendance as the year goes on. Now, a new teammate joins, so on Sheet 1, I insert a new row at the appropriate place to add that new person's basic information, and on Sheet 2 a new row is automatically added for that person in columns 1-3 (index, first and last name) with VLOOKUP and I can start entering their monthly training.

Here's the problem... Let's say the new person is added somewhere in the middle of the alphabet on Sheet 1. As it stands right now, Sheet 2 adds the new person's info in columns 1-3 and the remaining Sheet 2 columns are unaffected. So I have to manually go into Sheet 2 and insert new cells for the new person, pushing appropriate cells down to realign the existing data with the correct person. How do I make it automatically add a complete row across for the new employee on sheet 2?

Test: In the attached spreadsheet Sheet 1, I insert row between existing row 5 and 6 for a new teammate named "TEST". I insert "TEST" into cell B6 and C6. I repair the index column by dragging A6 downward. Now in Sheet 2, I see that Mr. TEST is inserted in columns B and C, but the rest of that row (columns D through O) containing Mr. Whiskey's data haven't moved down, so I have to insert cells in those columns to drive Mr Whiskey's data back down to his row.

Any assistance is greatly appreciated.

-D.

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread