Declare a variable to a cell

[vinidis]

Hi everyone!

I've been stuck with declaring a variable to a cell value and need some help.
Situation: I have a code that filters a table and transfers the filtered data to another sheet. The filter could be the date value but I can't set it properly. The result is always Error 1004.
I've tried a couple of solutions like set, with etc but non of them worked.

My code:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Option Explicit
Sub CopyFilteredData()

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim sDate As Variant

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]With Worksheets("Lista")
    sDate = .Range("B1").Value
End With

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Worksheets("GYARTAS").Range("A1:I1").AutoFilter
Worksheets("GYARTAS").Range("A1:I3000").AutoFilter Field:=2, Criteria1:=sDate

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Worksheets("GYARTAS").Range("A2:I25").SpecialCells(xlCellTypeVisible).Copy
Worksheets("Lista").Range("B35").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Worksheets("GYARTAS").AutoFilterMode = False

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]End Sub[/FONT]

Can someone do this to me?
Thank you.

 

[DanteAmor]

It works for me as follows:

In the "GYARTAS" sheet the column "B" format must be "dd/mm/yyyy" or "mm/dd/yyyy"

Try the following code:

Sub CopyFilteredData()
  Dim sDate As Date, sh1 As Worksheet, sh2 As Worksheet, x
  Set sh1 = Sheets("Lista")
  Set sh2 = Sheets("GYARTAS")
  sDate = sh1.Range("B1").Value
  sh2.Range("A1:I1").AutoFilter 2, Format(sDate, "mm/dd/yyyy")
  sh2.AutoFilter.Range.Offset(1).Copy
  sh1.Range("B35").PasteSpecial Paste:=xlPasteValues
  Application.CutCopyMode = False
  sh2.AutoFilterMode = False
End Sub

 

[vinidis]

It works for me as follows:

In the "GYARTAS" sheet the column "B" format must be "dd/mm/yyyy" or "mm/dd/yyyy"

Hello DanteAmor!

Works fine, thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.