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]

If you have formatted the ENTIRE columns, it may try to copy the whole column.
Is all your data contiguous? If so, it may be better to your the Current Region to select/copy just your data.

If you run this code, does it select all your data?

Sub Test()
    Sheets("Reports").Select
    Range("A1").CurrentRegion.Select
End Sub

If so, then you should be able to use Range("A1").CurrentRegion.Copy to just select the range you want to copy.

 

[mkseto]

If you have formatted the ENTIRE columns, it may try to copy the whole column.
Is all your data contiguous? If so, it may be better to your the Current Region to select/copy just your data.

If you run this code, does it select all your data?

Sub Test()
    Sheets("Reports").Select
    Range("A1").CurrentRegion.Select
End Sub

If so, then you should be able to use Range("A1").CurrentRegion.Copy to just select the range you want to copy.

Unfortunately, it didn't work for me. I ended up with just the Excel "blank" screen with NO worksheet.

 

[Fluff]

When you say

When the data is pasted to the new workbook, it has 1048576 rows

Do you mean that every row has data in it, or that you have a load of empty cells below your data?

 

[Joe4]

Please post a sample of what the data on your "Reports" sheets looks like so that we know what we are working with (otherwise, we are just "guessing", and you see how well that works).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[mkseto]

Sorry guys, I will post a sample sheet later this afternoon (after work). In the meantime, clarifications:
Prior to running this macro, I had indeed formatted the sheet by selecting various columns, such as:
- change numbers to be displayed with thousands delimiter + 2 decimal places
- customize number to show as 5-digits
- center-aligned .... etc.
Sounds like this is what cause the whole 1048576 rows to be copies despite there are only a few hundred rows actually having data (all rows with data are continuous at the top, all others are just completely blank rows).

 

[Fluff]

If you do Ctrl End on that sheet, where do you end up?

 

[mkseto]

If you do Ctrl End on that sheet, where do you end up?

Takes me to row# 1048576.

 

[Fluff]

In that case it will probably be like that in the original sheet, as you are just copying the sheet & nothing else.

 

[mkseto]

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.