PDA

View Full Version : Copy number of rows to formula result



Phelony
04-15-2009, 01:45 AM
Hello again!

I've yet again got stuck in trying to make a macro to do something which I was sure was easy, but seem to have some issues in coding it. :dunno

What I've got, is the cell M1 that calculates the number of required pages of a form, the pages are 49 rows in length and 13 wide.

What's supposed to happen, :bug: is that this code should select the 13 columns and then the number of rows multiplied by the result found in cell M1 and then paste the whole lot into A1, so if M1=5 it multiplies 49 by 5 and copies 245 rows. :whistle: Easy huh? :banghead:

However, I appear to be having some syntax issues with getting it off the ground. I confess that I have actually just hijacked some code from another macro...which is probably where I'm falling down.:doh:

I've looked and looked and looked for a solution and can't seem to work out what should go in here to get the expected result.

So I turn to your greater wisdom in search of :help !

Phel x

'
' Macro2 Macro
'
Sheets("NCA A Page 2").Select
Dim g As Long
For g = Range("M1").Value
'
Range("N1:Z&g*49").Select.Copy
Range("A1").Select.Paste


End Sub

Norie
04-15-2009, 01:53 AM
That syntax is totally wrong and it won't even compile.

And I'm afraid the explanation of what you want to do isn't clear either.:)

You mention multiplying forms etc but I don't see anything like that going on in the code.:huh:

Phelony
04-15-2009, 02:10 AM
That syntax is totally wrong and it won't even compile.

And I'm afraid the explanation of what you want to do isn't clear either.:)

You mention multiplying forms etc but I don't see anything like that going on in the code.:huh:

Sorry, I've been fighting with this all morning so I guess it's my brain melting! :duel:

I'm building automated forms in excel, so the forms issue isn't really there.:whyme:

Basically, it goes like this.

Cell M1 contains the result of a formula that calculates a number based on criteria from other sheets. :sleuth:

Columns N to Z are hidden and contain information that is compiled by forumula, broken down into pages that are 49 rows in length each.

What I need to do, is to copy Columns N to Z and then the number of rows required (i.e. M1 * 49) and paste it into A1 where another function prints all of the unhidden pages off.:thumb

Basically just a copy and paste, but the copy range depends on a variable row found in another cell. :cool:

Does that help clarify? Sorry for mentioning forms, these are automated versions of handwritten forms and not proper forms built in Excel. Sadly, the best way of doing all of this is through Access, but it's client data and they won't let me use it. :bawl

Any help or guidance is wonderfully and warmly received! :yes

Phel x

Phelony
04-15-2009, 02:19 AM
' Macro3 Macro
'
'
Range("N1:Z49").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
End Sub


Basically this, although Z needs to be able to accept a variable row number based on the contents of M1.

So really, my question is, how do I get a variable row number (Z(M1*49))?

I'm digging myself further and further into the pit of confused! :cuckoo:

Bob Phillips
04-15-2009, 06:32 AM
Range("N1:Z1").Resize(Range("M1").Value * 49).Copy Range("A1")
End Sub

Phelony
04-15-2009, 08:36 AM
:bow: You're an absolute star! Works wonderfully! :love: