VBA Question for Range on open ended Columns

[arossijr]

I used the Macro Recorder to get the basics, added in for Criteria1 and Criteria2... All worked good till I needed to select certain rows and available cells...
Here is the code I am using with all the "Failures" for selecting the ranges..

Application.ScreenUpdating = False
Dim Startdate, EndDate As Date
Dim LngLastRow As Long



Any and all help welcome!!! Thank you!!!



Startdate = Sheets("Data").Range("B1").Value
EndDate = Sheets("Data").Range("B2").Value


Range("A4").Select
Selection.End(xlDown).Select
LngLastRow = ActiveCell.Row




'ActiveSheet.ListObjects("Data").Range.AutoFilter Field:=1, Operator:= _
'xlFilterValues, Criteria2:=Array(1, "5/31/2018")
'ActivateSheet.ListObjects("Data").Range.AutoFilter Field:=1, Criteria1:=">=" & Startdate, Operator:=xlAnd, Criteria2:="<=" & EndDate
Sheets("Data").ListObjects("Data").Range.AutoFilter Field:=1, Criteria1:=">=" & Startdate, Operator:=xlAnd, Criteria2:="<=" & EndDate

'ActiveWindow.SmallScroll Down:=9
'Range("A4:A108,B4:B108,C4:C108,G4:G108,I4:I108,J4:J108").Select
'Sheets("Data").Range("A4", "B4", "C4", "G4", "I4", "J4").Select
'Sheets("Data").Range(A.Address, B.Address, C.Address, G.Address, I.Address, J.Address).Select
'Sheets("Data").Range("A4,B4,C4,G4,I4,J4").Select
Sheets("Data").Range("A4" & LngLastRow, "B4" & LngLastRow, "C4" & LngLastRow, "G4" & LngLastRow, "I4" & LngLastRow, "J4" & LngLastRow).Select
'Range("J2").Activate
'Sheets("Data").ListObjects("Data").AutoFilter.Range.Copy
Selection.SpecialCells(xlCellTypeVisible).Copy
Sheets("Report").Select
Range("Table1[DATE]").Select
ActiveSheet.Paste
End Sub

 

[arossijr]

I tried a new approach which sorta works but now I am getting Error 91...
New Code is:
Dim Startdate, EndDate As Date
Dim LngLastRow As Long
Dim WB As Workbook
Dim Data As Worksheet
Dim Report As Worksheet


Set WB = ThisWorkbook
Set Data = WB.Sheets("Data")
Set Report = WB.Sheets("Report")


Startdate = Sheets("Data").Range("B1").Value
EndDate = Sheets("Data").Range("B2").Value
Data.Activate
'Range("Data[Date]").Select
'Selection.End(xlDown).Select
LngLastRow = ActiveCell.Row
Data.ListObjects("Data").Range.AutoFilter Field:=1, Criteria1:=">=" & Startdate, Operator:=xlAnd, Criteria2:="<=" & EndDate
Range("A4" & LngLastRow).Select
Range("B4" & LngLastRow).Select
Range("C4" & LngLastRow).Select
Range("G4" & LngLastRow).Select
Range("I4" & LngLastRow).Select
Range("J4" & LngLastRow).Select
Data.AutoFilter.Range.Selection.Copy
'Selection.Copy
'Selection.SpecialCells(xlCellTypeVisible).Copy
Report.Select
Range("A2").Select
ActiveSheet.Paste


End Sub


I am trying to copy Data from the Data Sheet but only Select Columns after it has been filtered. The Filter part is working perfectly the copy paste part is not.
IF I use the macro recorder and try to do this I can but the columns are not always the same length.. which is part of where i am getting stuck

 

[My Aswer Is This]

I hardly ever like trying to read some one's code which is not doing what they want. Especially code done with the recorder and try to figure out what you want and then fix the code.

Would you like to just tell me in words what your wanting to do here.
And please provide specific details

Like column numbers like 1 or 2
And sheet names.

And if we are dealing with a Table give me the Table Name

 

[arossijr]

I hardly ever like trying to read some one's code which is not doing what they want. Especially code done with the recorder and try to figure out what you want and then fix the code.

Would you like to just tell me in words what your wanting to do here.
And please provide specific details

Like column numbers like 1 or 2
And sheet names.

And if we are dealing with a Table give me the Table Name

What I am trying to do is set a Date Range for the Filter, then Copy the Data that is Filtered by the Data Range. I am using Tables. This is all done in 1 Workbook.
Sheet1(Data) has a Table named "Data" I am sorting by Date which is column 1 or Table Header [Date]
Then I want to Copy the Sorted Data from Column Headers [Date],[LOC],[Acct],[Part #],[Qty Sold],[Base]
to Sheet2(Report) to Table named Table1 (Was to be named Report)
The Filter works, just not the copy paste... The Data is always an unknown row count... Otherwise the copy works off the Recorder...

Thank you My Answer Is This!!

 

[arossijr]

Sheet(Data) Start Date is Cell B1, End Date is Cell B2
Table(Data) Headers are on Row 3 from A to N
[Date],[LOC],[Acct],[Acct Name],[Line],[PN Code],[Part #],[Description],[Qty Sold],[Base],[Sell],[Ext Sell],[Cost],[Inv #]

Sheet(Report)
Table(Table1) Headers are on Row 1 from A to F
[Date],[LOC],[Acct],[Part #],[Qty Sold],[Base]

So i want to take the Data from Table"Data" to Table"Table1"

 

[My Aswer Is This]

This is not the best way to do things. But since your learning have come up with this script here is how you want to do the lines of code you want:

Report.Select
Dim Lastrow As Long
Lastrow = Sheets("Report").Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(Lastrow, 1).PasteSpecial

 

[arossijr]

My Answer Is This,

Thank you for your information and code, just wondering where in the script to place this and do i need to alter anything for the columns i want to grab?

Thanks Again!

 

[My Aswer Is This]

Can you not see in your code where you have this:

Report.Select
Range("A2").Select
ActiveSheet.Paste
​

Just change what you have for what I provided

Cells(Lastrow, 1).PasteSpecial​

The 1 means column 1

 

[arossijr]

Just making sure, I have been running through as much as I can. I placed the code you provided in the spots i believe them to go to but I am now being stopped right after the line of code for filter...
RunTime Error 450 for Line: Sheets("Data").Range("A4" & LngLastRow, "B4" & LngLastRow, "C4" & LngLastRow, "G4" & LngLastRow, "I4" & LngLastRow, "J4" & LngLastRow).Select

 

[My Aswer Is This]

All I really know is you said my code works perfectly except for the paste portion. So I just provided those two lines of code you asked for.

You said:

The Filter works, just not the copy paste... The Data is always an unknown row count... Otherwise the copy works off the Recorder...
​