Line count in sheet determines paste range in another sheet

[wpreqq99]

From cell B6 all the way down in sheet Official List, I want to count
the number of lines not empty. (There will be no empty lines in
between.)

After the number is calculated, I want to start at cell A6 in sheet
GDATA and copy the formulas in cells A6.B6 down to as many lines as
there were in sheet Official List.
Then, I want to make sure all the cells below that are empty. (Some
days may have more lines than others. I want to delete these formulas
if they are beyond the number of lines from above.)

For example, the macro counts 500 lines in Official List (starting
from cell B6).
It copies cells A6.B6 in GDATA, and pastes it to A7.B506 (500 lines)
It then deletes any line beyond line 506 that contain any data.

I would appreciate any help…
Thanks
jeff

 

[SAPkannan]

From cell B6 all the way down in sheet Official List, I want to count
the number of lines not empty. (There will be no empty lines in
between.)

After the number is calculated, I want to start at cell A6 in sheet
GDATA and copy the formulas in cells A6.B6 down to as many lines as
there were in sheet Official List.
Then, I want to make sure all the cells below that are empty. (Some
days may have more lines than others. I want to delete these formulas
if they are beyond the number of lines from above.)

For example, the macro counts 500 lines in Official List (starting
from cell B6).
It copies cells A6.B6 in GDATA, and pastes it to A7.B506  (500 lines)
It then deletes any line beyond line 506 that contain any data.

I would appreciate any help…
Thanks
jeff

Hi Jeff,

Copy the following code into a module. Hope it will work.

Sub Copy_Cells()

Sheets("Official List").Select
Range("B6").Select
finalrowb = Range("B6").End(xlDown).Row
' Loop through each row
For x = 1 To finalrowb
Range("B" & x, "B6").Select
Next x
Selection.Copy
Sheets("GDATA").Select
Range("A6").Select
ActiveSheet.Paste

Application.CutCopyMode = False
rowcount = Selection.Rows.Count
rowvalue = 6 + rowcount
drange = Range("A" & rowvalue).Select
Range(ActiveCell, "A65536").Select
Selection.EntireRow.Delete
Range("A1").Select

End Sub

Tx
Kannan