VBA when copying and pasting

[Mia]

Hi,

I have a problem with copying and pastig.

I have wrote a code wich copies a table after sorting it and pastes i in
to a new workbok. Everything works fine when I'm standing in the
table and runs the macro, but if I'm standing above the table
it copies all the rows and not only the filtered ones.

What have I done wrong? Do anyone knows?
My code are:

Sheets("A").Unprotect
Application.ScreenUpdating = False

Dim bok As Workbook
Dim blad As Worksheet
Dim Period As Date
Dim Avslut As Date

Dim Rapportmånad As String
Rapportmånad = ActiveSheet.Range("B3")

Set bok = Workbooks.Add

Workbooks("C").Sheets("Adata").Activate

Period = ActiveSheet.Range("b3")
Avslut = ActiveSheet.Range("B4")

ActiveSheet.ListObjects("Atabell").Range.AutoFilter

ActiveSheet.ListObjects("Atabell").Range.AutoFilter Field:=21, Criteria1 _
:="<=" & Range("b3").Value, Operator:=xlOr, Criteria2 _
:="="

ActiveSheet.ListObjects("Atabell").Range.AutoFilter Field:=23, Criteria1 _
:=">" & Range("b4").Value, Operator:=xlOr, Criteria2 _
:="="

ActiveSheet.ListObjects("Atabell").Range.AutoFilter Field:=2, Criteria1 _
:="3919"

ActiveSheet.ListObjects("Astratabell").AutoFilter.Range.Copy

bok.Activate

Set blad = Worksheets.Add()
blad.Name = "3919"

Worksheets("3919").Range("A1").PasteSpecial (xlPasteFormats)
Worksheets("3919").Range("A1").PasteSpecial (xlPasteValues)

I asked the question before but forgot information, I cross my fingers that
someone can help med.

 

[joel]

Three things

1) I wouldn't use activesheet since the workbooks are going to
change
2) Your worksheet add doesn't have a before or after which means a
new workbook will be created. Is that what you want.
3) I would copy the autofilter results using special cells with the
property visible.


Sheets("A").Unprotect
Application.ScreenUpdating = False

Dim bok As Workbook
Dim blad As Worksheet
Dim Period As Date
Dim Avslut As Date

Dim Rapportmånad As String

Set oldsht = ActiveSheet
Rapportmånad = oldsht.Range("B3")


Set bok = Workbooks.Add


With Workbooks("C").Sheets("Adata")

Period = .Range("b3")
Avslut = .Range("B4")

..ListObjects("Atabell").Range.AutoFilter

..ListObjects("Atabell").Range.AutoFilter Field:=21, _
Criteria1:="<=" & Range("b3").Value, _
Operator:=xlOr, _
Criteria2:="="

..ListObjects("Atabell").Range.AutoFilter Field:=23, _
Criteria1:=">" & Range("b4").Value, _
Operator:=xlOr, _
Criteria2:="="

..ListObjects("Atabell").Range.AutoFilter Field:=2, _
Criteria1:="3919"


..ListObjects("Astratabell").specialcellss(xlCellTypeVisible).Copy
End With

With bok

Set blad = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
blad.Name = "3919"

..Worksheets("3919").Range("A1").PasteSpecial _
pste:=xlPasteFormats
..Worksheets("3919").Range("A1").PasteSpecial _
Paste:=xlPasteValues
End With