Macro help.. Copying data from a table until the next empty row.

[mccaco]

I have been trying to create a macro which will copy all the data from certain columns in a table (B to G) between row 24 and the next empty row (as the table found on a sheet called "Data" is updated frequently so the final row of data will be moved further down the sheet). then pasted onto "sheet3" in cell A1. I havn't had much luck so far so I would be grateful if someone could give me a hand.

Thank you in advanve for any help.

Regards
Mccaco

 

[VoG]

Try

Sub test()
Dim LR As Long
With Sheets("Data")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B24:G" & LR).Copy Destination:=Sheets("Sheet3").Range("A1")
End With
End Sub

 

[mccaco]

 Sub LastModifiedFile()
    
    Dim dirName As String
    Dim fName As String
    Dim fileTime As Date
    Dim FileName As String
    Dim LatestFile As String
    Dim wbSource As Workbook
    Dim wbFile As Workbook
    Dim LR As Long
    Set wbSource = ActiveWorkbook
    
    dirName = "P:\New Issues\"
    fName = Dir(dirName & "*.*")
    
    While fName <> ""
        If FileDateTime(dirName & fName) > fileTime Then
            LatestFile = fName
            fileTime = FileDateTime(dirName & fName)
        End If
        fName = Dir()
    Wend
    
    If LatestFile = "" Then
        MsgBox "There are no files in the directory"
    Else
        Workbooks.Open dirName & LatestFile
        
        Set wbFile = ActiveWorkbook
        
        With Sheets("Data")
           LR = .Range("B" & Rows.Count).End(xlUp).Row
          .Range("B24:G" & LR).Copy Destination:=wbSource.Sheets("Sheet3").Range("A1")
   
        wbFile.Close
    End If
End Sub

This is what I have so far, basically I want to open up the most recently modified file in a folder copy the range of data that I mentioned at the top of the thread from that workbook. Paste the data onto "sheet3" in the source workbook where the macro will be triggered then close the workbook from where the data was copied.

 

[mccaco]

 Sub LastModifiedFile()
    
    Dim dirName As String
    Dim fName As String
    Dim fileTime As Date
    Dim FileName As String
    Dim LatestFile As String
    Dim wbSource As Workbook
    Dim wbFile As Workbook
    Dim LR As Long
    Set wbSource = ActiveWorkbook
    
    dirName = "P:\New Issues\"
    fName = Dir(dirName & "*.*")
    
    While fName <> ""
        If FileDateTime(dirName & fName) > fileTime Then
            LatestFile = fName
            fileTime = FileDateTime(dirName & fName)
        End If
        fName = Dir()
    Wend
    
    If LatestFile = "" Then
        MsgBox "There are no files in the directory"
    Else
        Workbooks.Open dirName & LatestFile
        
        Set wbFile = ActiveWorkbook
        
        With Sheets("Data")
           LR = .Range("B" & Rows.Count).End(xlUp).Row
          .Range("B24:G" & LR).Copy Destination:=wbSource.Sheets("Sheet3").Range("A1")
   
        wbFile.Close
    End If
End Sub

This is what I have so far, basically I want to open up the most recently modified file in a folder copy the range of data that I mentioned at the top of the thread from that workbook. Paste the data onto "sheet3" in the source workbook where the macro will be triggered then close the workbook from where the data was copied.

Basically it doesnt work and I have no idea why.