Good Afternoon,

I have a macro that each time someone clicks the "Insert Summary Lines"
button I want it to resize the original range. The macro will copy two lines
in the current range and insert them, right now I can only get the code to
select the range and not save it, so when you click on the button again it
reverts back to the old method. Here is my code:

Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer
Dim Summary_Sheet As Range
Dim Sample As Range



NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Set Sample = Range("Summary_Sheet")
Worksheets("Summary").Activate
Range("First_Cell").Activate
Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 4
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows, ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ThisWorkbook.Save
End Sub

Any suggestions

You may want to use a dynamic range to do this. Take a look here for more
information

http://peltiertech.com/WordPress/200...ynamic-charts/
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Katie" wrote:

Good Afternoon,

I have a macro that each time someone clicks the "Insert Summary Lines"
button I want it to resize the original range. The macro will copy two lines
in the current range and insert them, right now I can only get the code to
select the range and not save it, so when you click on the button again it
reverts back to the old method. Here is my code:

Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer
Dim Summary_Sheet As Range
Dim Sample As Range



NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Set Sample = Range("Summary_Sheet")
Worksheets("Summary").Activate
Range("First_Cell").Activate
Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 4
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows, ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ThisWorkbook.Save
End Sub

Any suggestions

I sthe code doing the copying corectly? the copy and Inserts look all wrong
which is making it hard to find out how to fix the code. Get the copy and
Insert working first and then repost.

"Katie" wrote:

Good Afternoon,

I have a macro that each time someone clicks the "Insert Summary Lines"
button I want it to resize the original range. The macro will copy two lines
in the current range and insert them, right now I can only get the code to
select the range and not save it, so when you click on the button again it
reverts back to the old method. Here is my code:

Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer
Dim Summary_Sheet As Range
Dim Sample As Range



NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Set Sample = Range("Summary_Sheet")
Worksheets("Summary").Activate
Range("First_Cell").Activate
Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 4
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows, ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ThisWorkbook.Save
End Sub

Any suggestions

Add this just before the Workbook.Save statement
ActiveWorkbook.Names.Add _
Name:="Summary_Sheet", _
RefersTo:="Summary!" &
Range("Summary_Sheet").Resize(Rowsize:=NumRows,
ColumnSize:=NumColumns).Address

This basically redefines the range of Summary_Sheet

"Katie" wrote:

Good Afternoon,

I have a macro that each time someone clicks the "Insert Summary Lines"
button I want it to resize the original range. The macro will copy two lines
in the current range and insert them, right now I can only get the code to
select the range and not save it, so when you click on the button again it
reverts back to the old method. Here is my code:

Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer
Dim Summary_Sheet As Range
Dim Sample As Range



NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Set Sample = Range("Summary_Sheet")
Worksheets("Summary").Activate
Range("First_Cell").Activate
Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 4
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows, ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ThisWorkbook.Save
End Sub

Any suggestions

Joel,

The code is typed correctly. I have to places within this spreadsheet
that I am copying lines and inserting them. This part works fine, I am
even able to select the new range however it does not save it so when
the user clicks to insert another line it reverts back to the original
range definition.
I have several parts going into this macro therefore you were seeing a
few variables that didn't need to be there. I will show you what it
looks like now:

Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer
Dim Summary_Sheet As Range
Dim Sample As Range



NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Set Sample = Range("Summary_Sheet")
Worksheets("Summary").Activate
'Range("First_Cell").Activate
'Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
'Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 4
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows,
ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ThisWorkbook.Save
End Sub


--
butrfly_kis3
------------------------------------------------------------------------
butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23750

Awaiting your feeback on my post...

"butrfly_kis3" wrote:


Joel,

The code is typed correctly. I have to places within this spreadsheet
that I am copying lines and inserting them. This part works fine, I am
even able to select the new range however it does not save it so when
the user clicks to insert another line it reverts back to the original
range definition.
I have several parts going into this macro therefore you were seeing a
few variables that didn't need to be there. I will show you what it
looks like now:

Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer
Dim Summary_Sheet As Range
Dim Sample As Range



NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Set Sample = Range("Summary_Sheet")
Worksheets("Summary").Activate
'Range("First_Cell").Activate
'Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
'Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 4
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows,
ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ThisWorkbook.Save
End Sub


--
butrfly_kis3
------------------------------------------------------------------------
butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23750

Sheeloo,

When I type it just as you have it I receive a 1004 object error code. Am I
missing something?

Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer
Dim Summary_Sheet As Range




NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Worksheets("Summary").Activate
Range("First_Cell").Activate
Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 4
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows, ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ActiveWorkbook.Names.Add([Name:="Summary_Sheet"], [RefersTo:="Summary!" &
Range("Summary_Sheet").Resize(Rowsize:=NumRows,Col umnSize:=NumColumns]).Address
ThisWorkbook.Save
End Sub

Sheeloo,

I tried entering it the way you have it typed in the message but
receive a 1004 object error code...any ideas?

This is exactly what it looks like:
Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer
Dim Summary_Sheet As Range




NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Worksheets("Summary").Activate
Range("First_Cell").Activate
Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 4
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows,
ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ActiveWorkbook.Names.Add([Name:="Summary_Sheet"], [RefersTo:="Summary!"
&
Range("Summary_Sheet").Resize(Rowsize:=NumRows,Col umnSize:=NumColumns]).Address
ThisWorkbook.Save
End Sub


--
butrfly_kis3
------------------------------------------------------------------------
butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23750

It is one VBA statement so all should be in one line unless there is an "_"
at the end of the line...

I have added line numbers... to avoid confusion due to wrap in the post.
1. ActiveWorkbook.Names.Add _
2. Name:="Summary_Sheet", _
3. RefersTo:="Summary!" &
Range("Summary_Sheet").Resize(Rowsize:=NumRows,
ColumnSize:=NumColumns).Address

"Katie" wrote:

Sheeloo,

When I type it just as you have it I receive a 1004 object error code. Am I
missing something?

Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer
Dim Summary_Sheet As Range




NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Worksheets("Summary").Activate
Range("First_Cell").Activate
Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 4
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows, ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ActiveWorkbook.Names.Add([Name:="Summary_Sheet"], [RefersTo:="Summary!" &
Range("Summary_Sheet").Resize(Rowsize:=NumRows,Col umnSize:=NumColumns]).Address
ThisWorkbook.Save
End Sub

Sheeloo,

That will run with no error but when I try to run it again I found out
it deleted the summary sheet range.

Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer




NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Worksheets("Summary").Activate
Range("First_Cell").Activate
Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 2
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows,
ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ActiveWorkbook.Names.Add _
Name:="Summary_Sheet", _
RefersTo:="Summary!" & Range("Summary_Sheet").Resize(Rowsize:=NumRows,
ColumnSize:=NumColumns).Address
ThisWorkbook.Save
End Sub


--
butrfly_kis3
------------------------------------------------------------------------
butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23750

Strange...
I tested it many times before posting... It updated the range everytime.

Can you send me the file?

Add @hotmail.com to to_sheeloo to get my id.

Yes, id has a 'to' too :-)

"butrfly_kis3" wrote:


Sheeloo,

That will run with no error but when I try to run it again I found out
it deleted the summary sheet range.

Sub Insert_Rows()
Dim NumRows As Integer
Dim NumColumns As Integer
Dim S As Integer




NumberofRows = Range("Summary_Sheet").Rows.Count
NumberofColumns = 15
S = 0
Worksheets("Summary").Activate
Range("First_Cell").Activate
Set Sum_Description = Worksheets("Summary").Columns("C").Rows("7")
Range(Sum_Description.Address).Activate
Range("Summary_Line").Copy
Range("Summary_Line").Insert shift:=xlShiftDown, copyorigin:=True
S = S + 2
Range("Total_Summary").Copy
Range("Total_Summary").Insert shift:=xlShiftDown, copyorigin:=True

Do Until (NumberofRows = NumRows)
If (S 0) Then
NumRows = NumberofRows + S
NumColumns = NumberofColumns
Range("Summary_Sheet").Resize(Rowsize:=NumRows,
ColumnSize:=NumColumns).Select
End If
NumberofRows = NumberofRows + S
Loop
ActiveWorkbook.Names.Add _
Name:="Summary_Sheet", _
RefersTo:="Summary!" & Range("Summary_Sheet").Resize(Rowsize:=NumRows,
ColumnSize:=NumColumns).Address
ThisWorkbook.Save
End Sub


--
butrfly_kis3
------------------------------------------------------------------------
butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23750

I have just sent it.

Thanks for you help.
Katie


--
butrfly_kis3
------------------------------------------------------------------------
butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23750

I have sent the updated file back.

I should NOT have added the .address at the end...
also prepending "Summary!") was also not required.

right code is pasted below;
I have added line numbers... to avoid confusion due to wrap in the post.
1. ActiveWorkbook.Names.Add _
2. Name:="Summary_Sheet", _
3. RefersTo:=Range("Summary_Sheet").Resize(Rowsize:=N umRows,
ColumnSize:=NumColumns)

"butrfly_kis3" wrote:


I have just sent it.

Thanks for you help.
Katie


--
butrfly_kis3
------------------------------------------------------------------------
butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23750

Sheeloo,

Thanks for you help, it is working perfectly.


--
butrfly_kis3
------------------------------------------------------------------------
butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23750