VBA to count rows from one sheet and to multiple that row count to copy a larger range in another sheet.

[gaz_luv]

Hi Guys,

Hope you can help.

I need to know some VBA code so that I can count the number of rows on one sheet (not including header row ) then apply that number to a formatted range

Part 1 Extract count.

Sheet 1 is an extract of data and has a row count of 4 but I only need a round count from Row 2 and down ie row count would = 3.

Part 2 Applying Row count to to copy a formatted sheet 2 dependent on row count value

Sheet 2 is a pre formatted range of cells

As you can see I would like to copy the number of formatted rows based on sheet 1 row count.

To be clear a
Sheet 1 row count = 1 would return sheet 2 copy range of A1:E9
Sheet 1 row count = 2 would return sheet 2 copy range of A1:E18
Sheet 1 row count = 3 would return sheet 2 copy range of A1:E27
and so on

I have no idea how to approach this so any help would be appreciated.

Thanks in advance