Test1.xlsmHello all,
I have written a vba code and I want to see if anyone can help to ameliorate it. It is a simple code.
In the attached file you will three colored rows. The macros i have written enable us (after clicking on the blue box) to copy these three lines into specific places on the sheet. Its simple.
But if we add a row or column they will copied into different places or not at all!
My question: is there a way (without doing it manually each time) that will let the macros work (i.e copying these 3 lines into specific locations) despite adding rows or columns.
I dont wont that each time after adding rows or columns to go into the code and make corrections.
Thank you very much!!!
Can it be done by naming the cells you want to target? That way they will move when rows or columns are added.
Column is potentially not an issue. But what if I add a row?
I am looking for a way (maybe writing the macros differently) to automate this.
Hi Naja
Please explain what it is you want to do...it's not clear from your Code. Be aware, when adding or deleting rows it's best to start from the bottom rather than from the top.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Hello John,
Ok. I you look at row 7 (yellow). If you click on the blue button it will be copied into rows 10, 11, 24, 38 etc...
If insert a row for instance above row 5 (maybe to add a title or a pic etc...). The yellow row wont be copied to where i want it be or not at all. To change that i need to do it manually i.e enter the view code and change the range.
Is there a way (maybe to write the macros differently) so that if i add a row or column, the yellow row will still be copied where it is supposed to be?
Thank you
Hi Naja
When you add rows your cell references change. Explain what it is you're trying to do...perhaps we can help.
Before and After is always good...
Test2.xlsmHello John,
In the attached file you will see an after. I added a row above the yellow row (it is now row 8 instead of row 7). If you click on the blue button the yellow row values wont be copied where they are supposed to be (refer to Test1 file i posted in first post).
I want a way that no matter how many rows or columns i add, the yellow row values will always will be copied where they supposed to be. (i dont want to do it manually).
Help anyone????
Hi Naja
Try this![]()
Option Explicit Sub Copy_Rows() Dim rng As Range Dim LR As Long Dim LC As Long With Sheets(1) LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row LC = .Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column With Sheets(1).Range(.Cells(6, 5), .Cells(LR, 5)) Set rng = .Find("A", LookIn:=xlValues, lookat:=xlWhole) Application.ScreenUpdating = False If Not rng Is Nothing Then Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Copy Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(3, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(4, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(17, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(31, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(70, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(71, 0).PasteSpecial Application.CutCopyMode = False Set rng = .Find("B", LookIn:=xlValues, lookat:=xlWhole) If Not rng Is Nothing Then Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Copy Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(3, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(4, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(7, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(8, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(18, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(19, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(35, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(45, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(58, 0).PasteSpecial Application.CutCopyMode = False End If Set rng = .Find("C", LookIn:=xlValues, lookat:=xlWhole) If Not rng Is Nothing Then Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Copy Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(4, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(11, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(22, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(28, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(52, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(61, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(69, 0).PasteSpecial Sheets(1).Range(Cells(rng.Row, 1), Cells(rng.Row, LC)).Offset(71, 0).PasteSpecial Application.CutCopyMode = False End If End If End With End With Application.ScreenUpdating = True End Sub
Hello John,
First of all I want to thank you for your help.
I copied the code. Ot seems to work once. If if make any modifications the new value wont be corrected.
Maybe I have pasted wrongly? Or i am making mistakes?
Hi Naja
What kind of modifications are you referring to?
You REALLY need to explain what you're doing and WHY...
I have the yellow row with certain values (in row 7). These values must be copied (after clicking on the button) to certain specific rows.
If I add a row above row 7. The value of the yellow row wont bwe copied were they are supposed to.
My question: how can we ameliorate my macro so that no matter how many rows or columns i add, the values of the yellow cells will always be copied where are supposed to.
Hi Naja
I believe, based on my understanding of your requirements, the Code does just thisIf the Yellow Cells are in Row 7, they'll be copied to Rows 10, 11, 24, 38, 77 and 78. If the Yellow Cells are in Row 8, they'll be copied to Rows 11, 12, 25, 39, 78 and 79. If the Yellow Cells are in Row 9, they'll be copied to Rows 12, 13, 26, 40, 79 and 80.no matter how many rows or columns i add, the values of the yellow cells will always be copied where are supposed to
So, what am I missing?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks