copying various row amounts to new sheet using a macro

[efish]

I'm trying to copy and paste several different filtered lists to a new sheet using a macro. My problem is that depending on the database dump the filtered list can contain A - XX rows of data (has same # of columns) and the next filter I perform needs to be pasted underneath the first. So my final sheet needs to look something like this

(group 1 - with A through ? rows)
data 1A......
data 1B......
data 1C......
blank row where I sum some of the columns

(group 2 - needs to start where group 1 ends)
data 2A......
data 2B......
data 2C......


Therefore, I need to use the macro to somehow paste the second group of data where the first group ends plus a few blank spaces.
Please let me know if anyone has an idea on how to do this. Thanks.

 

[Tazguy37]

First, you might try recording a macro that does this, and start with that.

I've done something similar to this myself. I'll paste the code here:

        ActiveSheet.Columns("A:E").AutoFilter Field:=4, Criteria1:=">=" & FilterStartDate, Operator:=1, _
            Criteria2:="<=" & FilterEndDate
        Columns("A:F").SpecialCells(12).Copy                'Copy filtered data
        Workbooks(NewBook.Name).Activate
        If Not SheetExists(MonthName(Months)) Then
            Sheets.Add AFTER:=ActiveSheet                   'Add a sheet for every new month in date column
            ActiveSheet.Name = MonthName(Months)            'and name it the current loop's month
        End If
        Range("A1").PasteSpecial -4163                      'Paste filtered data as values
        Workbooks(DataBook).Activate
        Columns("D:D").SpecialCells(12).Copy                'Copy all the dates
        Workbooks(NewBook.Name).Activate
        Worksheets(1).Range("R" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues 'and copy to sheet 1 for use in chart

Columns A-E get filtered for dates between the start and end filter dates (entered by the user before the code you see); dates are in Column D (Field:=4), and then copied and pasted to a new worksheet. The SpecialCells Property copies only visible cells.

Post back if you have any specific questions about what I've done here. Hope that helps!