Help with an Error 1004: Application Defined or Object Defined Error

[Obsessed]

Hello Everyone,

I'm sure this is an easy fix, but I'm new to VBA and am not good at spotting errors very well. I'm trying to make a simple macro that will take a range, and copy it down a new sheet a certain number of times (based on a separate list) and copy down data from said list into the newly copied data (I have a workbook attached which shows what I'm trying to do...Sheet1 is where I enter the data, Sheet2 is where the output is, Sheet3 is what I'd like the output to be).

Here is the code I have so far:

Please Login or Register  to view this content.

As I noted, I'm getting the Error 1004: Application Defined or Object Defined error on the last line of the loop. I'm thinking it doesn't like prow and rowcount in the cells function, but not sure why it doesn't like it. Any help you could provide would be greatly appreciated!

Thanks so much!

[AlphaFrog]

Try something like this...

Please Login or Register  to view this content.

[AlphaFrog]

BTW: the error was because Sheet1 is the active sheet and this is trying to define a range on Sheet2
Worksheets("Sheet2").Range(Cells(prow, 6), Cells(prow + rowcount, 6))

Cells(prow, 6), Cells(prow + rowcount, 6) are two cell references without a sheet reference. So the default sheet is the Active Sheet or in this case Sheet1. So the code is trying to create a range from Sheet2 using two cells from the activesheet Sheet1

You could activate Sheet2 and use this...
Range(Cells(prow, 6), Cells(prow + rowcount, 6))

Or specify Sheet2 for each cell reference
Range(Worksheets("Sheet2").Cells(prow, 6), Worksheets("Sheet2").Cells(prow + rowcount, 6))

[Obsessed]

AlphaFrog, you are a gentleman and a scholar.

For my learning, would you be able to explain what the following code does exactly? I'd like to understand how this code works, as I don't exactly understand it.

Please Login or Register  to view this content.

Thanks again for your help! Much appreciated!