Macro to Move Visible Cells after Subtotaling

Greetings. Gurus.

I'm working with a large master list of part numbers, and matching data to these numbers from a varying data table via vlookup. I have to vlookup to the actual part number, (13,000 numbers), then subtotal the data based on the "Rollup" part number. (eg: Two different old PNs have now "rolled up" into a single part number. I am pulling sales data from all of the old numbers, and subtotaling the sales to the new number.) Too much information.

Here's my issue. After subtotaling on the rollup number, I want to copy and paste ONLY the rollup numbers and associated data to a different sheet. This should be easily accomplished by going to level 2 of the subtotals page, hiding all the old PNs and their data, selecting the remaining visible cells, then "Go To", "Special", "Visible cells". Normally, you could copy only the visible cells, and paste them wherever you want. But because of the total length of the subtotaled worksheet, (nearly 25,000 rows), I get the error message, "Micrsoft Excel cannot create or use the data range reference because it is too complex."

You can overcome this by selecting smaller ranges of data, and copying and pasting a little at a time, but since the "visible rows" will vary, I can't figure a way to incorporate this into a macro. I've tried setting specific ranges in the macro, and copying and pasting fewer cell at a time, but if the range named in the macro is a hidden cell, the macro won't select any of them.

Here's an example of what I've got so far. I realize it's not the most efficient code, but I'm working with limited knowledge and ability.

Sub Paste_Subtotals()
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, _
        7), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Range("B1:G6000").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Subtotals").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("B6001:G12500").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Subtotals").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
End Sub

I would have to repeat the copy and paste 4 times to get the full list of parts and data, which isn't a problem if I know it will select the right cells each time, but I'm open for other suggestions as well.

Sorry such a long post. Any help would be greatly appreciated.

Jerry

Try something like...

Sub Test()
For Each Row In Selection.Rows
    If Row.Height > 0 Then
        Row.Copy Destination:=Sheets("Subtotals").Cells(65536, 1).End(xlUp).Offset(1, 0)
    End If
Next Row
End Sub

I'm assuming that you have already hidden the rows that you don't want copied and that there is always a value in the first column

mrice,

You are correct in that I have already hidden the rows I don't want copied, but after subtotaling, there is no value in the first column. Column A contains the old part numbers, column B contains the rollup numbers, and these are the numbers I am subtotaling. Lastly, I don't want to copy or move column A, just visible rows from column B thru G.

I'll try this and see what it does, though.

Thanks,

Jerry

In this case you will need to edit the macro to look in a column where there is continuous data. Its the cells(65536,1) part that you need to change, changing the 1 for the appropriate column number.

This does exactly what I needed it to do. I will select all columns containing data from B thru G, and then paste only the visible rows into cell A1 on my "Subtotals" worksheet.

There is no data on my subtotals sheet until the new data is pasted, so I removed the offset function from the end of your statement.

Thanks fo the help!

Mr. Ice,

I'm afraid I posted prematurely when I said the code was perfect.

The code you sent worked nicely when I selected columns A thru G, and then ran the code, except that it pasted the column headings from the data worksheet below the column headings on the subtotals worksheet.

I thought that by removing the offset, it would just replace the headings, which would be fine. Well, you probably already know, removing the offset ain't the answer.

I'll play with it until I get it figured out from here.

Thanks again.

Jerry