VBA when copying and pasting
 

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 a

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.AutoFilte r

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

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

ActiveSheet.ListObjects("Atabell").Range.AutoFilte r 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.


--
Best regards
Mia

VBA when copying and pasting
 

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(xlCellTyp eVisible).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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170389

Microsoft Office Help