Resizing a Range

  • Thread starter Thread starter Katie
  • Start date Start date
K

Katie

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.
 
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
 
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,ColumnSize:=NumColumns]).Address
ThisWorkbook.Save
End Sub
 
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
 
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 :-)
 
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:=NumRows,
ColumnSize:=NumColumns)
 
Back
Top