Copying rows and columns, including blanks, in excel vba without xlDown

Question

I'd like to copy Rows and Columns of a worksheet to a new workbook. I can open and activate the new book and new sheets, but the data I am copying is currently A1:F520 on one sheet and A1:G1031 on another. There are multiple blanks in between and within several "groups" of data sets. Example of Groups

For the sake of the visual organization of our data, I want to keep those blanks in. The ranges will be changing over time, so I don't want to define something specific. I'd rather keep it general like Range("A1", Range("A1".End(xlDown)). I suppose I could just include some ridiculously large range to ensure everything is there, but I'd rather accomplish this in a more dynamic (if that is the right word) sort of way.

This is what I have so far:

Option Explicit

Public LGSheet As Worksheet
Public SGSheet As Worksheet
Public NewWorkbook As Workbook
Public UserYear As Integer
Sub DefineYear()

    UserYear = Application.InputBox(Prompt:="Set Date for New Workbook", Type:=1)
    Set NewWorkbook = Workbooks.Add
    Set SGSheet = Worksheets.Add
    Set LGSheet = Worksheets.Add
    Call PasteLGTandSGT

End Sub
Sub PasteLGTandSGT()

    Workbooks("Finished Products YTD Generator").Activate
    Worksheets("LGT").Activate
        Range("A1", Range("A1").End(xlDown)).Copy
    LGSheet.Activate

Obviously I've put the xlDown bit here, and my google-fu isn't strong enough to find out anything more.

Use End(xlUp) from the bottom of the sheet - that will preserve the gaps — Tim Williams
I just gave that a shot, and it doesn't seem to work. I tried it from G1048576, A1048576, G520 and A520 - as well as the combinations (e.g., A1048576:G1048576). From the very bottom of the page it only copies the last row. From 520 it only copies the last 15 rows. I'll keep fiddling with it, but if you have anything else in mind I'd love to hear it. — webfootedoldman

Tim Williams Tim Williams · Accepted Answer · 2017-05-10T16:59:16

For example:

With Workbooks("Finished Products YTD Generator").Worksheets("LGT")
    .Range(.Range("A1"), .Cells(Rows.Count, 1).End(xlUp).Offset(0, 7)).Copy _
        LGSheet.Range("A1") 'or whatever
End With

Note that you don't need to activate sheets to copy or paste, and it's a good habit to get out of.

Copying rows and columns, including blanks, in excel vba without xlDown

1 Answers