Problem with copy sheet to new workbook - ended up with 1048576 rows

[mkseto]

I'm new to VBA.
I use the following macro to copy data from a sheet to a new workbook (then close the sheet without saving):

Sub Copy_To_New_Workbook()
Sheets("Reports").Copy
Workbooks("Template.xlsm").Close SaveChanges:=False
End Sub

I hope someone can help with the following:
1) When the data is pasted to the new workbook, it has 1048576 rows despite there are only a few hundred rows with data. Is there a way to modify my macro above so that the new workbook would only contain rows with data?
2) How can I modify the above code so that I don't have to refer to the file name ("Template.xlsm") to close it?

Hope someone can help with these silly questions (again, I'm only starting to learn VBA).

 

[Joe4]

The sheet in question has some other macros. I decided to run each one separate and was able to trace down where the sheet became to have 1048576 rows.
The sheet had 20,000 rows (i.e. all 20,000 rows had data in all columns except "A", where some were blank) up until before the below macro:

Sub Fill_Column()
Sheets("Reports").Select
With Range("A:A")
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
End Sub

The macro was to fill the blank cells in column "A" with value in the cell above. It completed the task successfully as all blank cells in column "A" were filled, but something "added" additional rows to make the sheet 1048576 rows big (though Rows 20,001 to 1048576 were completely blank).

Wonder if this macro can be modified to avoid the huge number of blanks rows being added.

It sure can, if you can tell us the logic we can use to determine how down it needs to go. Is there some other column or something that we can look at to determine what row the data ends on?

 

[Fluff]

How about

Sub Fill_Column()
Sheets("Reports").Select
With Range("A1:A" & Range("B" & Rows.count).End(xlUp).Row)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
End Sub

 

[mkseto]

How about

Sub Fill_Column()
Sheets("Reports").Select
With Range("A1:A" & Range("B" & Rows.count).End(xlUp).Row)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
End Sub

That worked, THANK YOU so much. Excel no longer adds the extra rows, and makes the whole process run much faster as well. THANK you again!!!

 

[Fluff]

Glad we could help & thanks for the feedback.