Copy last used row

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
413
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,299
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe...

VBA Code:
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

VBA Code:
        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
 
Last edited:

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
413
Office Version
  1. 365
Platform
  1. Windows
Maybe...

VBA Code:
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

VBA Code:
        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
VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,299
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try....

VBA Code:
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
 
Solution

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
413
Office Version
  1. 365
Platform
  1. Windows
Mark that is just great.
Thank you so much
 

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
413
Office Version
  1. 365
Platform
  1. Windows
Finnished Code, in case it helps anyone else
VBA Code:
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
 

Forum statistics

Threads
1,147,451
Messages
5,741,200
Members
423,648
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top