Help Please add last row to copy range

[roberttaekim]

Hi excel experts. Can someone please help with instead of copying a defined range 1300 rows below, can you help with doing a last row of data based on the last row of data in column A?
Really appreciate any help.

[/
Sub CopyPending()
'
' CopyPending Macro
'

Dim NmStr As String             'ActiveWorkbook
Dim FDate As String             'Lookup Value
Dim WB As Workbook              'the workbook to open


NmStr = ActiveWorkbook.Name
Windows(NmStr).Activate
'Selects the value in A6 to do a search in the workbook to open
FDate = ActiveWorkbook.Sheets("Recon").Range("A6").Value
'Open workbook based on address in B5
Set WB = Workbooks.Open(Range("B5").Value)
'Find the value of FDate in the newly opened workbook and copy range 1 row below from active cell to column K down to the 1300 row below
    Cells.Find(What:=FDate, After:=ActiveCell, LookIn:= _
        xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Range("A1:K1300").Select
    Selection.Copy
'Activate recon workbook
Windows(NmStr).Activate
ActiveWorkbook.Sheets("OpsReport_LCs_PendingFees").Select

'Find Fdate value in the recon workbook and paste it 1 row below
    Cells.Find(What:=FDate, After:=ActiveCell, _
        LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste

Sheets("RECON").Select
    Application.CutCopyMode = False


WB.Close savechanges:=False


'
End Sub
]

 

[jolivanes]

Range("A1:K" & Cells(Rows.Count, 1).End(xlUp).Row).Copy

 

[roberttaekim]

Range("A1:K" & Cells(Rows.Count, 1).End(xlUp).Row).Copy

Thanks but that is copying all rows below the last data row in column A. How do you get it to select only the range of data?

 

[jolivanes]

Please explain what you want to accomplish.
Let us know what is in Column A. Data from the top to the last cell?

Just the last row?

Cells(Rows.Count, 1).End(xlUp).Resize(, ActiveSheet.UsedRange.Columns.Count).Copy

 

[roberttaekim]

Sorry if it wasn't clear. There's all sorts of data in column A. I am searching for the declared value FDate because it's supposed to select the range starting 1 row below where that searched value is. The selection should extend to column K and should go down all the way to last row of data in column A.

The code you provided works fine but it doesn't end at the last data. It selects every row below. Thanks again for your help

 

[jolivanes]

Re: The code you provided works fine but it doesn't end at the last data. It selects every row below. Thanks again for your help
If you select Range A1 and then click on the "End" button while holding down the "Ctrl" button, where do you end up?
Is it on the "last data" row in Column K? That's where it should end if you just have data.
Do you have formulas extending past the "last data"?
You have declared Fdate as String. Is it not a date?

 

[jolivanes]

Looking at your code and your explanations, please explain in detail and in a concise manner what you want to achieve