Macro to copy filtered data then paste into another worksheet (1 Viewer)

Groundrush · Sep 6, 2008

What is the best way to copy & paste without worrying about missing out records when using a macro?

I thought I sussed this out awhile ago but It seems that I'm still doing this incorrectly.

I have a large spreadsheet that has in average increase of about 260 records per week. At the moment it stands at 6544 records & I have till April next year for the spreadsheet to reach it's maximum record count.

The records are divided into 14 different contracts, Contract being the filtered field

I use this code to filter, copy then paste before moving onto the next filtered contract

Code:

Selection.AutoFilter
    
    ''Select Admin Contract, copy & paste into correct tab ready for summing up costs
    Selection.AutoFilter Field:=1, Criteria1:="Admin Buildings"
    Range("A1:R6545").Select
    Range("A2").Activate
    ''save space
    Selection.SpecialCells(xlCellTypeVisible).Select   
    Selection.Copy
    Sheets("Adm").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.Panes(1).Activate
    Cells.Select
    ''save space
    Selection.SpecialCells(xlCellTypeVisible).Select
    Cells.EntireColumn.AutoFit
    Sheets("All").Select
    Application.CutCopyMode = False

The problem I have is that each week when the records increase the range will be incorrect & some records will be missed.

I guess I need a way to find the last record & select the whole range?

any Ideas?
thanks

Brianwarnock · Sep 7, 2008

From Help

UsedRange Property
See Also Applies To Example Specifics
Returns a Range object that represents the used range on the specified worksheet. Read-only.

Example
This example selects the used range on Sheet1.

Worksheets("Sheet1").Activate
ActiveSheet.UsedRange.Select

Brian

Groundrush · Sep 7, 2008

Brianwarnock said:
From Help

Brian

What did you enter in the Help field to get that solution?

I've been struggling for ages looking for that

Thank you

Brianwarnock · Sep 8, 2008

Usedrange

But then I knew it existed.

It can be used like so for example

With ActiveSheet.UsedRange
Set lastcell = .Cells(.Rows.Count, .Columns.Count)
lastcol = .Columns.Count
End With
MsgBox lastcell
MsgBox lastcol
MsgBox lastcell.Address()

Brian

edit: I've just realised I used With ActiveSheet.UsedRange in the code I supplied in my example for your previous thread , back in May I think. You did read it didn't you, or am I wasting my time?

Groundrush · Sep 8, 2008

Brianwarnock said:
Usedrange

I've just realised I used With ActiveSheet.UsedRange in the code I supplied in my example for your previous thread , back in May I think. You did read it didn't you, or am I wasting my time?

Certainly not

The code you are refering to is

Code:

Sheets("ALL").Select
With ActiveSheet.UsedRange
'lastcol = .Cells(1, 1).Column + .Columns.Count - 1
lastrow = .Cells(1, 1).Row + .Rows.Count - 1
totalrow = .Cells(1, 1).Row + .Rows.Count
End With

colnum = 9
For colnum = 9 To 13
Cells(totalrow, colnum).Select
With Selection
    .Value = Application.WorksheetFunction.Sum(Range(Cells(2, colnum), Cells(lastrow, colnum)))
End With
     With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
Next colnum


End Sub

I did look at it & incorprate it in my macro but then had range problems when the records increased & could not work out why.

Groundrush · Sep 9, 2008

Groundrush said:

Certainly not

The code you are refering to is

Code:

Sheets("ALL").Select
With ActiveSheet.UsedRange
'lastcol = .Cells(1, 1).Column + .Columns.Count - 1
lastrow = .Cells(1, 1).Row + .Rows.Count - 1
totalrow = .Cells(1, 1).Row + .Rows.Count
End With

colnum = 9
For colnum = 9 To 13
Cells(totalrow, colnum).Select
With Selection
    .Value = Application.WorksheetFunction.Sum(Range(Cells(2, colnum), Cells(lastrow, colnum)))
End With
     With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
Next colnum


End Sub

I did look at it & incorprate it in my macro but then had range problems when the records increased & could not work out why.

but using your suggestion on another thread I don't get totals in the incorrect cells anymore...thanks
This thread also relates to
http://www.access-programmers.co.uk/forums/showthread.php?t=141456

Brianwarnock · Sep 9, 2008

I was a bit puzzled by the first few lines of code but now that you have it working I wont mess, glad you reported back.

Brian

Macro to copy filtered data then paste into another worksheet (1 Viewer)

Groundrush

Registered User.

Brianwarnock

Retired

Groundrush

Registered User.

Brianwarnock

Retired

Groundrush

Registered User.

Groundrush

Registered User.

Brianwarnock

Retired

Similar threads

Users who are viewing this thread