Need to add data to table in result sheets

[humacdeep]

Hi,

I have data in table format in sheet1 but when I run the below code its not copying the result data in table format (in received sheet). Could any one please have a look and help me?

Thanks in advance for your help!!

Sub copy_data()
  
  p = InputBox("Please enter date", "Enter Start Date")
  q = InputBox("Please enter date", "Enter End Date")
  
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
    If Not Evaluate("ISREF('Received'!A1)") Then
      Sheets.Add(After:=Sheets("Sheet1")).Name = "Received"
    Else
      Sheets("Received").UsedRange.ClearContents
    End If
    
    .Rows(1).Copy Sheets("Received").Rows(1)

    On Error Resume Next

    With .UsedRange
      .AutoFilter 12, ">" & CLng(CDate(p & " 23:59")), xlAnd, "<" & CLng(CDate(q & " 00:00"))
      .Offset(1).Copy Sheets("Received").Cells(2, 1)
      .AutoFilter

  End With
  
   Sheets("Received").Columns.AutoFit

  Application.ScreenUpdating = True
End Sub

Regards,
Humac

[watersev]

hi Humac, can you post file in question?

[humacdeep]

Hi Watersev,

I have attached the file.

The below code is adding the result sheet to table but I am unable to make it dynamic. Please help.

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$W$591"), , xlYes).Name = _
        "Table2"

Regards,
Humac

[watersev]

try this:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$W$" & Cells(Rows.Count, 1).End(xlUp).Row), , xlYes).Name = "Table2"

PS. Not tested

[humacdeep]

It is working if I delete the existing Received Sheet and run the Macro and its not working when I try to run the macro with out deleting the existng sheet .

[watersev]

try this:

Sub test()

With Sheets("Received")
    
    .ListObjects.Add(, .Range("$A$1:$W$" & .Cells(Rows.Count, 1).End(xlUp).Row), , xlYes).Name = "Table2"

End With

End Sub

Run code "test"

[humacdeep]

Thanks a LOT Watersev... its working