Wow! That works great Holger! Thank you very much for the solution and your rapid replies! Look like it is an absolute macro and not relative to where the cursor is in the active sheet, so, in my actual data set, I will just need to adjust the column and cell references to be appropriate for that data, correct? What I had supplied was just a sample so that I could show what I needed. I think I should be able to easily make this work on my real data. Thanks Again! RZ
Hi, Excel-RZ,
the amendments could be made in the 2 constants at the begin of the code:
You may think about getting the column number using Application.Inputbox(type:=8) for a range but you would need to adapt the code slightly (range wants a string as the first part while Cells will work with both strings and longs for Columns).
Sub EF965720_3() Dim lngCounter As Long Const cstrCOL As String = "J" Const clngSTART As Long = 10 For lngCounter = Range(cstrCOL & Rows.Count).End(xlUp).Row To clngSTART Step -1 With Cells(lngCounter, cstrCOL) If IsNumeric(.Value) And .Value > 1 Then With .Offset(1, 0).Resize(.Value - 1, 1) .EntireRow.Insert End With .Offset(1, 1).Resize(.Value - 1, 5).Value = .Offset(0, 1).Resize(1, 5).Value End If End With Next lngCounter End Sub
The Offset must be adapted if other than the next five columns should be copied or more or less than five columns.
Ciao,
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Hello Holger,
One more thing (I hope)... I just applied your code to my real data set and it worked! Yay! One improvement I need to make is that the cells that are being copied have just the values of the original cells and not the formulas. Sorry, I did not mention before that some of my source cells contain formulas. Normally, this is a very easy thing to change when pasting, as you just select values only or formats only or just paste the whole contents and everything goes, including formulas. In this case, it is not clear to me what part of the VBA code I would change to make it copy all cell attributes (formulas, formats and values). Would you please advise me on how to change the code to get the formulas to copy as well? Thanks for you help again! RZ
Hi, Excel-RZ,
from the last posted code change the code line
to read.Offset(1, 1).Resize(.Value - 1, 1).Value = .Offset(0, 1).Resize(1, 5).Value
Ciao,.Offset(0, 1).Resize(1, 5).Copy Destination:=.Offset(1, 1).Resize(.Value - 1, 1)
Holger
That works great! Thanks again for all your help! How cool is this (for me anyway)... collaborating with a guy in Germany to get my Excel challenged solve... My family 6 generations back was from Baden Baden. Thanks again Holger! I will mark this thread as solved. (..as soon as I can figure out how to do that.) Ciao! RZ
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Excel-RZ 
Register To Reply
Bookmarks