Copy last used row

[Bagsy]

Hi All

I have been out of work for over a year, but have just managed to pick up some work, unfortunately I seem to have forgotten so much.

Can somebody please help me finish my code.
I have some data in columns B:F

I have got it working to the point where I can find the last used cell in column C, then offset to column B. I have then set it up to copy that cell and paste it in another workbook just to try it

But what I need to do is copy from the selected cell in column B to column F, a row of 5 columns.



Any help will be gratefully received

Sub ImportData()

Dim Wb As Workbook
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
Set Wb = ActiveWorkbook

Sheets("Del Note").Select
If Not IsEmpty(Range("Q2").Value) Then
CurrentSheet.Select
Exit Sub
Else

Application.ScreenUpdating = False ' turn off the screen updating
Sheets("Del Note").Select
Windows("_Delivery note list.xlsm").Activate

Sheets("Running List").Select
Range("C3").End(xlDown).Select

ActiveCell.Offset(0, -1).Copy'  I need to copy row from selected cell in column B to column F
Wb.Activate
Sheets("Del Note").Select
ActiveSheet.Range("Q2").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

End If

End Sub

 

[MARK858]

Maybe...

Dim MyCell As Range

Set MyCell = Windows("_Delivery note list.xlsm").Sheets("Running List").Range("C" & Rows.Count).End(xlUp)

MyCell.Offset(0, -1).Resize(, 5).Copy
Wb.Sheets("Del Note").Range("Q2").PasteSpecial xlValues

or

        Dim MyCell As Range

        Set MyCell = Windows("_Delivery note list.xlsm").Sheets("Running List").Range("C" & Rows.Count).End(xlUp)

        With MyCell.Offset(0, -1).Resize(, 5)
            Wb.Sheets("Del Note").Range("Q2").Resize(, 5).Value = .Value
        End With

 

[Bagsy]

Maybe...

Dim MyCell As Range

Set MyCell = Windows("_Delivery note list.xlsm").Sheets("Running List").Range("C" & Rows.Count).End(xlUp)

MyCell.Offset(0, -1).Resize(, 5).Copy
Wb.Sheets("Del Note").Range("Q2").PasteSpecial xlValues

or

        Dim MyCell As Range

        Set MyCell = Windows("_Delivery note list.xlsm").Sheets("Running List").Range("C" & Rows.Count).End(xlUp)

        With MyCell.Offset(0, -1).Resize(, 5)
            Wb.Sheets("Del Note").Range("Q2").Resize(, 5).Value = .Value
        End With

Thanks for assisting Mark858, I really appreciate the help

I have added the code you suggested but get an error “object does not support this or method” and highlights this row
Set MyCell = Windows("_Delivery note list.xlsm").Sheets("Running List").Range("C" & Rows.Count).End(xlUp)

Please can you tell me what I have got wrong Code below

Sub ImportData()

 Dim WB As Workbook
 Dim CurrentSheet As Worksheet
 Set CurrentSheet = ActiveSheet
 Set WB = ActiveWorkbook
 
    Sheets("Del Note").Select
     If Not IsEmpty(Range("Q2").Value) Then
      CurrentSheet.Select
    Exit Sub
    Else
    Application.ScreenUpdating = False ' turn off the screen updating

   Windows("_Delivery note list.xlsm").Activate
   Sheets("Running List").Select
  
 Dim MyCell As Range
 Set MyCell = Windows("_Delivery note list.xlsm").Sheets("Running List").Range("C" & Rows.Count).End(xlUp)

 MyCell.Offset(0, -1).Resize(, 5).Copy
 WB.Sheets("Del Note").Range("Q2").PasteSpecial xlValues
  
Application.CutCopyMode = False
End If
End Sub

 

[MARK858]

Try....

Sub ImportData()

    Dim WB As Workbook
    Dim CurrentSheet As Worksheet
    Set CurrentSheet = ActiveSheet
    Set WB = ActiveWorkbook
 
    Sheets("Del Note").Select
    If Not IsEmpty(Range("Q2").Value) Then
        CurrentSheet.Select
        Exit Sub
    Else
        Application.ScreenUpdating = False       ' turn off the screen updating

        '   Windows("_Delivery note list.xlsm").Activate
        '   Sheets("Running List").Select
        '
        Dim MyCell As Range
        Set MyCell = Workbooks("_Delivery note list.xlsm").Sheets("Running List").Range("C" & Rows.Count).End(xlUp)

        MyCell.Offset(0, -1).Resize(, 5).Copy
        WB.Sheets("Del Note").Range("Q2").PasteSpecial xlValues
 
        Application.CutCopyMode = False
    End If
End Sub

 

[Bagsy]

Mark that is just great.
Thank you so much

 

[Bagsy]

Finnished Code, in case it helps anyone else

Sub ImportData()

Dim WB As Workbook
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
Set WB = ActiveWorkbook
 
Sheets("Del Note").Select
    'ActiveSheet.Unprotect Password:="Password"
        If Not IsEmpty(Range("Q2").Value) Then 'this prevents running the code twice
    CurrentSheet.Select
Exit Sub
Else
    Application.ScreenUpdating = False ' turn off the screen updating
        Dim MyCell As Range
            Set MyCell = Workbooks("_Delivery note list.xlsm").Sheets("Running List").Range("C" & Rows.Count).End(xlUp)
        MyCell.Offset(0, -1).Resize(, 5).Copy
    WB.Sheets("Del Note").Range("Q2").PasteSpecial xlValues
Application.CutCopyMode = False
               
Windows("_Delivery note list.xlsm").Close True ' close the source workbook saving any changes
   Sheets("Del Note").Select
Set WB = Nothing ' free memory

Application.ScreenUpdating = True ' turn on the screen updating
'ActiveSheet.Protect Password:="Password"
    End If
End Sub