Help with macro code

[CarlosAntenna]

I have a sheet with a pivot table of variable size.
I want to further summarize the data below the pivot table.
I have put the appropriate formulas on another sheet.
I recorded a macro that was intended to:
select the formulas from sheet2
copy
select sheet1
move below the maximum expected size of the pivot table
move up to the bottom of the pivot table
move down one cell
paste

The problem is with moving down one cell. The macro recorded this as:
Range("A10").Select

How can I change that line to get the desired effect.

Carlos

***Macro code follows***

Sub SumBySize()
'
' SumBySize Macro
' Macro recorded 9/3/2004 by
'

'
Sheets("Sheet2").Select
Range("A2:C9").Select
Selection.Copy
Sheets("Sheet1").Select
Application.Goto Reference:="R400C1"
Selection.End(xlUp).Select
Range("A10").Select
ActiveSheet.Paste
End Sub

 

[CarlosAntenna]

It also seems to crash if I hide sheet2.
Can the macro be changed to make the selection on a hidden sheet?

Carlos

 

[Dave Peterson]

Option explicit
Sub SumBySize()

dim RngToCopy as range
dim DestCell as range

set rngtocopy = worksheets("sheet2").range("a2:c9").copy

with worksheets("sheet1")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

rngtocopy.copy _
destination:=destcell

End Sub

The .offset(1,0) says to come down 1 row from the last used cell in column A (in
this code).

 

[Dave Peterson]

Instead of selecting, you could just copy the range directly.

 

[CarlosAntenna]

Thanks, Dave.

I didn't understand all your code because I an a real newbie to VBA, but I
got it working by applying your suggestions like this.

Sub SumBySize()

Range("sheet2!a2:c9").Copy <== Copy directly
Sheets("Sheet1").Select
Application.Goto Reference:="R400C1"
Selection.End(xlUp).Offset(1, 0).Select <== Use offset
ActiveSheet.Paste

End Sub

 

[Dave Peterson]

There was a typo in my code:

set rngtocopy = worksheets("sheet2").range("a2:c9").copy
should have been:
set rngtocopy = worksheets("sheet2").range("a2:c9")

I decided to use some variables (I thought it would make it simpler) and if you
needed to do it again, you could just change the addresses.

Except for the error, it was perfekt. <vbg>.

Option explicit
Sub SumBySize()

'declare some variables to hold the ranges objects we'll use later
dim RngToCopy as range
dim DestCell as range

'pretty much just an assignment here.
'but with objects, you need the Set statement
'simple values don't need this
'dim myVal as long
'myval = 3
'doesn't need the set statement
set rngtocopy = worksheets("sheet2").range("a2:c9")

with worksheets("sheet1")
'the dots in front of the .cells, .rows means that these things
'belong to the preceding With statement--Worksheets("sheet1")
'in this case
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
'.cells(.rows.count,"A")
'is a way of writing
'.cells(65536,"A") or even .range("a65536")
'which is at the bottom of column A.
'end(xlup) is like hitting the End key followed by the arrow button
'inside excel. It finds the last used cell in the column.
'.offset(1,0) just means come down one cell.
end with

'after we set the ranges (correctly, sigh!), we just do the work.
rngtocopy.copy _
destination:=destcell

End Sub

Darn brain!

 

[CarlosAntenna]

In an earlier thread, Ron de Bruin wrote: "Start with books from John
Walkenbach". So I ordered Excel VBA Programming For Dummies. If it's even
half as good as the regulars here, I'll be up to speed in notime.

 

[David McRitchie]

Hi Carlos,
Better to pick a book twice as good as regulars <grin>
if you want a reference book. These books are in the
bookstores, so you can check out the table of content,
chapters, content, and index. If you're working with and changing
macros I think you could have started higher.

 

[Dave Peterson]

and John keeps a blog where you can lose days.

But one of his recent entries is that his book made it to print.

The publisher has one chapter available on line:
http://www.dummies.com/WileyCDA/DummiesTitle/productCd-0764574124,page-1.html
(currently two chapters!)

And if you never want to see the outside world again, start reading John's blog:
http://j-walkblog.com/blog/