Copy rows to another worksheet a variable number of times

[rgrovier]

If this has been asked, I apologize for having not found it ... I have a spreadsheet with over 1000 rows. I want users to be able to indicate the number of times a row should be duplicated (1-19) and then automatically copy that row the indicated number of times to another worksheet within the same workbook. This process can be done at the end of the user input procedure and I will just add it to a macro that will be run prior to closing out the file. Any help will be greatly appreciated. Thanks.

Example:
Row 1 - 5, is copied 5 times
Row 2 - left blank, is not copied
Row 3 - 1, is copied only once

 

[Norie]

Peregrin

The OP probably needed to have the worksheet explicitly referenced because it wasn't the active sheet.

When you have something like this, without a reference to a worksheet for Range, VBA assumes you mean a range on whatever worksheet is active.

Set rngDest = Range(Worksheets(strDest).Rows(iDestRow), Worksheets(strDest).Rows(iDestEndRow))

 

[PeregrinTook]

Ok I see, thanks Norie.

I thought that defining the sheets within the (rng1,rng2) part of the range statement would be fine - guess not.

So to be safe, if I'm working between different sheets (especially when not activating them) I should always explicity name the sheet before the range? eg worksheets(shtName).range(rng1,rng2)

If that's the case, when I define it like that why can I not leave out the worksheets(shtName) within each argument of the (rng1,rng2) part of range?
eg

Set rngDest = Worksheets(strDest).Range(Rows(iDestRow), Rows(iDestEndRow))

Throws up an error - only works if I define the worksheet within the range arguments.

This one works for me & Reich:

Set rngDest = Worksheets(strDest).Range(Worksheets(strDest).Rows(iDestRow), Worksheets(strDest).Rows(iDestEndRow))

But this one only works for me (on 2 different pc's):

Set rngDest = Range(Worksheets(strDest).Rows(iDestRow), Worksheets(strDest).Rows(iDestEndRow))

Tis a finicky thing indeed vba, no?!?



PeregrinTook

 

[Norie]

Peregrin

As far as I know it really all depends on what sheet is active for this to work.

Set rngDest = Range(Worksheets(strDest).Rows(iDestRow), Worksheets(strDest).Rows(iDestEndRow))

If the worksheet strDest isn't active then you are trying to create a range object on the active sheet using range objects from strDest.

You may have activated the sheet in the code but that doesn't gaurantee that the sheet you've activated is the active sheet as far as VBA is concerned.

The safest way is to explicitly reference the worksheet.

You could also utilise the With...End With structure.

With Worksheets(strDest)
     Set rngDest = .Range(.Rows(iDestRow), .Rows(iDestEndRow))
End With

By the way for the OP's particular request I would probably have used the Resize method.

 

[PeregrinTook]

Norie,

Thanks again, I'm still very much learning VBA myself & am always keen to know more! You mention the With...End With structure - that I am familiar with and can see how it could've been used.

But you also mention the Resize method, this is something I've seen before in other posts but not actually used myself yet - it relates to defining an array, right?

Would you mind giving an example of how you would've used it for the OP's scenario at all please? I'd be keen to see it applied in a situation I already understand, so I can deconstruct it better and see how to use it properly myself - but it may also help the OP in terms of processing time?

Many thanks
PeregrinTook

 

[Norie]

Peregrin

Let's say the number of rows to insert/copy are in column B then you could try something like this.

Sub ExpandRows()
Dim rng As Range
Dim NoRows As Long

    Set rng = Range("A1")
    
    While rng.Value <> ""
        NoRows = rng.Offset(, 1).Value
        If NoRows > 1 Then
            rng.Offset(1).Resize(NoRows - 1).EntireRow.Insert
            rng.EntireRow.Copy rng.Offset(1).Resize(NoRows - 1)
            Set rng = rng.Offset(NoRows - 1)
        End If
        Set rng = rng.Offset(1)
    Wend
End Sub

 

[PeregrinTook]

Nice, thanks Norie - will play about with this tomorrow & see how it works. Much tidier looking routine though!

All the best
PeregrinTook

 

[rgrovier]

PeregrinTook & Norie -

Not only am I a newbie to VBA and this forum but also message boards in general. I assume I'm the OP and that stands for ... Original Poster????

As for the version of Excel I'm using, I am using 2003 ... so that might explain something.

You both lost me in the Resize/End With discussion. If I followed correctly, though, this is an alternative to my original scenario???

In any event I report back when I get a chance to test the speed of the last code and this new code and let you know.

Thanks!

 

[Norie]

rgrovier

Yes OP does mean original poster, in my 'board vocabulary' anyway.

Sorry for sort of hijacking the thread.

I was sort of following it, and was originally going to post some code but then saw that Peregrin (Pippin?) was dealing with it.

Try the code I posted, and post back if you have any problems or other questions.

 

[rgrovier]

Well the first code is working and I don't know where to begin with Norie's code (not sure what it replaces). I tried replacing everything and got nothing.

I'll be checking back if anyone wants to continue to expand on this thread. Thanks, again, to both of you ... and especially you Peregrin!