Copy Columns

[hallingh]

Hi guys. I have a worksheet that has a ton of columns, about 200. Each column has a different formula in it that need to be copied throughout the column. However, I only want it to copy down to the number of occupied rows in another sheet. Like this:

The sheet "Raw Data" has 14 rows that are filled out with data (but the actual data starts at row 2.) The sheet "Stage 1" has 200 columns with row 2 containing a unique formula in each column. I need it to copy that formula down so it has the same number of rows that are occupied in "Raw Data"

If this isn't clear please let me know... Any help is greatly appreciated..

Thanks!

Hank

 

[shg]

Maybe ...

    With Worksheets("Stage1").Range("A2").Resize(, 200)
        .Copy .Resize(Worksheets("Raw Data").UsedRange.Rows.Count)
    End With

 

[hallingh]

Ah it is working exactly like I specified, but now I've realized that I made an error, sorry!

Is there any way to have it count the number of occupied rows in "Raw Data" starting from row 3 instead of from row 1? Again, sorry for the mistake.

Thanks for the reply!

Hank

 

[shg]

    With Worksheets("Stage1").Range("A2").Resize(, 200)
        .Copy .Resize(Worksheets("Raw Data").UsedRange.Rows.Count - 2)
    End With

 

[hallingh]

Hmmm I figured that out by changing it to:

With Worksheets("Stage 1").Range("A2").Resize(, 200)
        .Copy .Resize(Worksheets("Raw Infopath Data (BAC)").UsedRange.Rows.Count - 2)
    End With

But now I'm still having trouble with it copying too many rows. If I type in a value for 18 rows in the "Raw Data" sheet, it successfully copies it to 18 rows the first time I run the code. BUT, if I then clear the contents of both sheets and only enter values for 2 rows in "Raw Data" and run it again, it still copies the formulas on "Stage 1" to 18 rows... Can't figure out why?

Would switching it to this format (and this syntax is off but you'll get the idea) To End.xlUp help?

Thanks again for the code, it's helped a lot in getting the wheels in motion.

Hank

 

[shg]

Well then, ...

    With Worksheets("Stage1").Range("A2").Resize(, 200)
        .Copy .Resize(Worksheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Row - 2)
    End With

 

[hallingh]

That works perfectly...

Thanks a ton!

Hank

 

[shg]

You're welcome, glad it worked for you.