PDA

View Full Version : [SOLVED] Inserting a block of rows at the end of a register



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

SamT
05-19-2015, 08:02 AM
I think that you are asking about an Excel issue, so I moved this thread to the Excel Folder.

I also used the # icon on the Post Editor menu to encapsulate the code in CODE tags for display

mtotton
05-20-2015, 01:35 AM
After lots of trying I used the following code

Sub CommandButton1_Click() Dim f As Range
Sheets("Miscellaneous").Select
Sheets("Miscellaneous").Rows("1:6").Select
Selection.Copy
Sheets("05_Risk Register").Select
Set f = ActiveSheet.Cells.Find(999)
Sheets("05_Risk Register").Rows(f.Row).Select
Selection.Insert Shift:=xlDown
End Sub
I put a blank entry in sheet miscellaneous, then find 999 and insert the entry - works!:-)

SamT
05-20-2015, 07:25 AM
This should duplicate your code

Option Explicit


Sub CommandButton1_Click()
Dim f As Range

Sheets("Miscellaneous").Rows("1:6").Copy

Set f = Sheets("05_Risk Register").Cells.Find(999)
f.Row.Insert
End Sub

If I correctly understand exactly what you are trying to do, this should work without the 999 cell

Option Explicit


Sub CommandButton1_Click()
Dim NextRow As Long

NextRow = Sheets("05_Risk Register").Cells(Rows.Count, 2).Row + 2

Sheets("Miscellaneous").Rows("1:6").Copy _
Destination:=Sheets("05_Risk Register").Rows(NextRow)

End Sub

mtotton
05-20-2015, 09:01 AM
Thank you, Interesting....I didn't realise you could compress it this much! As to your second example:
The "NextRow=Sheets..." is where you do your magic I surmise?
Each entry in my risk register is made up of 6 rows, in the first column (B), all the rows are merged. As I understand it, the address of the merged rows is the address of the first row. Does this affect your code?
Also I don't understand what your code does :dunno so a simple explanation would be appreciated! I can send in my register if needed but I will have to vpn into work and get it.

SamT
05-20-2015, 09:53 AM
The "NextRow=Sheets..." is where you do your magic I surmise?
Yes. and memorize that line. It is a very slight modification of "LastRow," probably the most used single line in VBA for Excel.

Rows is a Range

Rows(3) is the entire third Row. Note no quotes, OK to quote

Rows("1:3") is the entirety of the first three rows. Double quotes required

You can usually paste everything you copy into a single Cell, (or Row :))

Range("A1:Z100").Copy Range("AC1")

mtotton
05-21-2015, 05:05 AM
Hi, Thanks for all your support!

Your first code worked great after I changed f.Row.Insert to Rows(f.Row).Insert - and is much neater!

I copy/pasted your 2nd code and tried it and got an 1004 "Application-defined or object-defined error"
Debug pointed at:

Sheets("Miscellaneous").Rows("1:6").Copy _
Destination:=Sheets("05_Risk Register").Rows(NextRow)

Don't know what the problem is

SamT
05-21-2015, 08:03 AM
In a bit of short time at the moment. If these hint don't help, bump the thread up.


Breakpoints
F5 to Run, F8 to Step
Hover over Variables and Objects to see values. Requires: Options - >>Auto Quick Info
Typos: >>Opption Explicit. Auto-inserted with Options >> Auto Syntax Check.
Review previous code