Resizing a Range

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>
 
J

Joel

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.
 
S

Sheeloo

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
 
B

butrfly_kis3

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
 
K

Katie

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
 
B

butrfly_kis3

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

Sheeloo

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
 
B

butrfly_kis3

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
 
S

Sheeloo

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 :)
 
S

Sheeloo

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying & resizing a range? 0
IF Then Statements 0
IF Then Statements 0
A couple of easy questions... 5
Peculiar Runtime Error 1004 6
resizing a range 3
cannot insert row in vba 1
Copying a Range from Multiple Worksheets 4

Top