VBA "activecell.range" help

[dukeofscouts]

Hello,

Id like to use a macro to copy a range of cells and paste them in a new range. To do this I've been able to get a macro that reads as follows:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 5/31/2009 by Dukeofscouts
'
'
Range("A2").Select
ActiveCell.Range("A1:A6").Select
Selection.Copy
ActiveCell.Offset(Range("d1"), Range("f1")).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(-3, -3).Range("A1").Select
End Sub

Where I need help is getting the "activecell.Range(A1:A6").select to be looking at B1 to read the number of rows in the range. B1 is "=sumproduct(--((a2:a102)<>""))

All my attempts to get this to work have resulted in error messages. Is this possible to do?

Thanks,
D.O.S.

 

[jbeaucaire]

Can you step away from the macro and explain in terms of the data the logic steps you're wishing would occur?

Use Excel Jeanie to post up some visible examples of the data you're referring to, and perhaps a mockup of the desired results you have in mind.

 

[jolivanes]

Would this work?

Range("A1:A6").Copy
    Range("B1").PasteSpecial XlPasteType:=xlPasteValues, _
                             XlPasteSpecialOperation:=xlPasteSpecialOperationNone, _
                             SkipBlanks:=False, Transpose:=False

Change B1 to where you want to paste.

Or have I misunderstood your requirements?

John

 

[jbeaucaire]

Are you saying that if the value in cell B1 was 10, then you would want the range to be:

Range("A2:A10").Select

And it if it were 20, then:

Range("A2:A20").Select

If so, you could try:

Range("A2:A" & Range("B1").Value).Select

 

[dukeofscouts]

You got it with that last one! there was my problem I was trying to put the Range ("b1") inside the quotes.

Thanks a million,
D.O.S.