i would like to have a cell in sheet1, let's call it "number of rows" in D12. When a user enter a value in D12, it adds that number of rows in sheet2 and sheet3
i have formulas in sheet2 and sheet3 in a table, and i just need it to add that many rows and copy the formula down that way.
I found something that also unhides rows when a cell value is changed... but because i'm going to create csv files, i can't have fake data so unhiding doesn't work for my case.
your help is appreciated!
Put this in the worksheet module.
Please Login or Register to view this content.
1. Include a sample workbook with an example of the result you want
2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.
hi k64, thanks for your reply. I added the code and saved it but nothing happened in sheet 2 and 3 when i enter a value in sheet1, d4. It it matters, i am using Excel 2010.
i made some changes to the spreadsheet, so there are now 4 sheets. Sheet1, Sheet2, Sheet3, Sheet4. I don't really need the rows to be added to Sheet2, but if the code needs to stay simple to add rows to all other sheets (2,3,4) then i'm okay with that.
My code is currently set to copy row 1, and insert the rows above row 1 to sheets 2 and 3 based on D12. If you look at the numbers in my code, I'm sure you can figure out what each number does, and modify it however you want.
okay, i modified x = "2 To 3" to x = "3 To 4"
row 1 in sheet 3 and 4 are headers, row 2 is what I want to copy, so i changed .Rows(1) to .Rows(2)
so my code looks like this
but it still does nothing..Please Login or Register to view this content.
Do i double click on the main (target) sheet and enter the vba in there, or do i right-click on the main sheet then insert module and add code there?
It should be on the main (target) sheet.
oh, one more thing. sheet3 and sheet4 is a table, so i'm trying to add rows to the table. Is the code supposed to work with tables? If not i can convert my tables to plain text as long as the formulas get copied over.
I'm not entirely sure what you mean. I view everything in excel as a table. If it's a pivot table, I'm afraid my code won't work. If it's just normal excel values arranged with headers, it will.
hi k64, i had pivot tables.. but i added 2 additional sheets (sheet5 and sheet6) without pivot tables and changed the code for x = 5 to 6, however, the rows still aren't being added..
i've attached my file here with sample data.. can you see what i'm doing wrong?
thanks again for your help!
Last edited by dishguy05; 06-07-2014 at 01:24 PM.
ohhh, i got it to work (partially).
I was putting the code in Sheet1 instead of 'Workbook'
so, now it creates lines...
But, instead of inserting lines above, i need it to insert lines below the previous row, otherwise the formula doesn't work. Could you please help with that?
I found the error. I wrote my code for the workbook module. If you want it to be on the worksheet module, use this code:Note 2 things:Please Login or Register to view this content.
1. I wrote 1 + Target.Value since we're starting at row 2. If we want to insert above row n, it would be n-1 + Target.Value
2. It is producing #REF errors because we're trying to copy a formula that contains F1 up and there is no F0 or F-1. This isn't because of the macro, it happens when I do it manually. I don't know what exactly you're doing so I can't tell you how to fix it, but my first recommendation would be to use absolute references (ie F$1).
hey k64, thanks again for helping.
I see the rows are still being added above row 2. so the formula becomes row-1.. if we can get it to add the rows below row 2, then it would be row+2 and the formula would work exactly how i need i. I am trying to modify the code myself, but not entirely sure where i would do that.
There are two ways to do that. First, below row 2 is just above row 3, so you can putSecond, if you just like inserting below better, you can change the shift direction, making itPlease Login or Register to view this content.Please Login or Register to view this content.
hi k6,
thanks for all your help. In the end, i use this code and it does what i need.
Please Login or Register to view this content.
Glad I could help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks