mtotton
05-19-2015, 03:56 AM
Hi,
I have a risk register in which each entry consists of up to 6 rows each. When all the predefined entries have been used I want the user to be able to press a button and copy the last entry, inserting it at the end. I put 999 in a row at the ned, the idea being that I would get the address of the 999, then count back and insert the rows. As rows keep being added I couldn't figure out another way of always finding the last entry.
I found this code which I tried to adapt to do the job, but I keep getting "sub or function not defined errors". Debug points at the Match function
Sub CopyConcatenate()
Dim ws As Worksheet
Dim rng As Range
'~~> Set this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("05_Risk Register")
With ws
'~~> Set your range find 999 in column B and return address. need to change row below to select 6 rows above this address!
Set rng = .Range(Address(Match(999, "B1:B999, 0) + Row(A2) - 1, Column(A2), 4))
'~~> Copy the range
rng.Copy
'~~> Insert the range
rng.Offset(5).Insert Shift:=xlDown
'~~> Clear the clipboard. More importantly remove the
'~~> ant like borders
Application.CutCopyMode = False
End With
End Sub
I used to write lots of macros and vba many years ago, but I have obviously forgotten everything as I can't figure it out. Any help would be gratefully recieved.
Mark
I have a risk register in which each entry consists of up to 6 rows each. When all the predefined entries have been used I want the user to be able to press a button and copy the last entry, inserting it at the end. I put 999 in a row at the ned, the idea being that I would get the address of the 999, then count back and insert the rows. As rows keep being added I couldn't figure out another way of always finding the last entry.
I found this code which I tried to adapt to do the job, but I keep getting "sub or function not defined errors". Debug points at the Match function
Sub CopyConcatenate()
Dim ws As Worksheet
Dim rng As Range
'~~> Set this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("05_Risk Register")
With ws
'~~> Set your range find 999 in column B and return address. need to change row below to select 6 rows above this address!
Set rng = .Range(Address(Match(999, "B1:B999, 0) + Row(A2) - 1, Column(A2), 4))
'~~> Copy the range
rng.Copy
'~~> Insert the range
rng.Offset(5).Insert Shift:=xlDown
'~~> Clear the clipboard. More importantly remove the
'~~> ant like borders
Application.CutCopyMode = False
End With
End Sub
I used to write lots of macros and vba many years ago, but I have obviously forgotten everything as I can't figure it out. Any help would be gratefully recieved.
Mark