Copy and Paste Last Row Problem

[greenhillchris]

Hi there

I have the following code that will open 2 workbooks and copy and paste data from one workbook to the other and then save and close the workbooks.

Sub OpenCopy1()

Workbooks.Open "C:\Users\cgreenhill\Desktop\Store Manager Individual Consultation Tracker - Master.xlsx"
Workbooks.Open "C:\Users\cgreenhill\Desktop\CW Region\Store Manager Tracker - 442 Staines.xlsx"

Workbooks("Store Manager Tracker - 442 Staines").Worksheets("Tracker").Range("B6:CM20").Copy _
Workbooks("Store Manager Individual Consultation Tracker - Master").Worksheets("Tracker").Range("B6")

Workbooks("Store Manager Individual Consultation Tracker - Master").Close SaveChanges:=True
Workbooks("Store Manager Tracker - 442 Staines").Close SaveChanges:=True

End Sub

This works great but I am looking to copy and paste based on the last row rather than defining ranges each time, so I have amended the above code to find the last row for each workbook but the problem now is that it doesn’t actually copy and paste anything it just opens and closes the two workbooks.

Sub OpenCopy2()

Dim LR1 As Long
Dim LR2 As Long

Workbooks.Open "C:\Users\cgreenhill\Desktop\Store Manager Individual Consultation Tracker - Master.xlsx"
Workbooks.Open "C:\Users\cgreenhill\Desktop\CW Region\Store Manager Tracker - 442 Staines.xlsx"

LR1 = Workbooks("Store Manager Individual Consultation Tracker - Master").Worksheets("Tracker").Range("B" & Rows.Count).End(xlUp).Row + 1
LR2 = Workbooks("Store Manager Tracker - 442 Staines").Sheets("Tracker").Range("B" & Rows.Count).End(xlUp).Row

Workbooks("Store Manager Tracker - 442 Staines").Sheets("Tracker").Range("B6:CM" & LR2).Copy _
Workbooks("Store Manager Individual Consultation Tracker - Master").Worksheets("Tracker").Range("B" & LR1)

Workbooks("Store Manager Individual Consultation Tracker - Master").Close SaveChanges:=True
Workbooks("Store Manager Tracker - 442 Staines").Close SaveChanges:=True
End Sub

I was wondering if anyone could advise me on where I am going wrong with this code and why it would not be copying and pasting.

Thanks for any help

 

[Anthony47]

You search last used row by testing column B: are you sure that after the visible data all the cells are "empty", even free of formulas?
Modify for testing you macro as follows:

Sub OpenCopy2()

Dim LR1 As Long
Dim LR2 As Long

Workbooks.Open "C:\Users\cgreenhill\Desktop\Store Manager Individual Consultation Tracker - Master.xlsx"
Workbooks.Open "C:\Users\cgreenhill\Desktop\CW Region\Store Manager Tracker - 442 Staines.xlsx"

LR1 = Workbooks("Store Manager Individual Consultation Tracker - Master").Worksheets("Tracker").Range("B" & Rows.Count).End(xlUp).Row + 1
LR2 = Workbooks("Store Manager Tracker - 442 Staines").Sheets("Tracker").Range("B" & Rows.Count).End(xlUp).Row
Debug.Print "Write on B" & LR1, "Copied: B6:CM" & LR2, Timer
Workbooks("Store Manager Tracker - 442 Staines").Sheets("Tracker").Range("B6:CM" & LR2).Copy _
Workbooks("Store Manager Individual Consultation Tracker - Master").Worksheets("Tracker").Range("B" & LR1)
Stop
Workbooks("Store Manager Individual Consultation Tracker - Master").Close SaveChanges:=True
Workbooks("Store Manager Tracker - 442 Staines").Close SaveChanges:=True
End Sub

Then execute the macro, that will halt on the Stop
At that point open (in the vba editor) the Immediate window (typing Contr-g should do the job; or Menu /View /Immediate window); you should read the starting address of your paste area and the address of the copied range. Check of the worksheets to find the inconsistency
Complete the macro by pressing (from the vba window) F5, to save and close the workbooks.

If that will not switch-on any light then excecute the macro twice and post what you read in the immediate window and we'll investigate with you

 

[davesexcel]

Maybe set the ranges to copy and paste.

Sub Get_It()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim LR1 As Long, LR2 As Long
    Dim rng1 As Range, rng2 As Range
   
    Application.ScreenUpdating = False
   
    'set the workbooks
    Set wb1 = Workbooks.Open("C:\Users\davem\Downloads\Store Manager Tracker - 442 Staines.xlsx")
    Set wb2 = Workbooks.Open("C:\Users\davem\Downloads\Store Manager Individual Consultation Tracker - Master.xlsx")
   
    'set the sheets in workbooks
    Set sh1 = wb1.Sheets("Tracker")
    Set sh2 = wb2.Sheets("Tracker")
   
    'set the last row and range to copy
    With sh1
        LR1 = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set rng1 = .Range("B6:CM" & LR1)
    End With
   
    'set the paste range
    With sh2
        LR2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        Set rng2 = .Range("B" & LR2)
    End With
   
    'copy source range to destination range
    rng1.Copy rng2
   
    'close and save workbooks
    wb1.Close True
    wb2.Close True

End Sub

 

[greenhillchris]

You search last used row by testing column B: are you sure that after the visible data all the cells are "empty", even free of formulas?
Modify for testing you macro as follows:

Sub OpenCopy2()

Dim LR1 As Long
Dim LR2 As Long

Workbooks.Open "C:\Users\cgreenhill\Desktop\Store Manager Individual Consultation Tracker - Master.xlsx"
Workbooks.Open "C:\Users\cgreenhill\Desktop\CW Region\Store Manager Tracker - 442 Staines.xlsx"

LR1 = Workbooks("Store Manager Individual Consultation Tracker - Master").Worksheets("Tracker").Range("B" & Rows.Count).End(xlUp).Row + 1
LR2 = Workbooks("Store Manager Tracker - 442 Staines").Sheets("Tracker").Range("B" & Rows.Count).End(xlUp).Row
Debug.Print "Write on B" & LR1, "Copied: B6:CM" & LR2, Timer
Workbooks("Store Manager Tracker - 442 Staines").Sheets("Tracker").Range("B6:CM" & LR2).Copy _
Workbooks("Store Manager Individual Consultation Tracker - Master").Worksheets("Tracker").Range("B" & LR1)
Stop
Workbooks("Store Manager Individual Consultation Tracker - Master").Close SaveChanges:=True
Workbooks("Store Manager Tracker - 442 Staines").Close SaveChanges:=True
End Sub

Then execute the macro, that will halt on the Stop
At that point open (in the vba editor) the Immediate window (typing Contr-g should do the job; or Menu /View /Immediate window); you should read the starting address of your paste area and the address of the copied range. Check of the worksheets to find the inconsistency
Complete the macro by pressing (from the vba window) F5, to save and close the workbooks.

If that will not switch-on any light then excecute the macro twice and post what you read in the immediate window and we'll investigate with you

Hi Anthony,

Thanks for your help on this, this has done the trick.

It was getting B6954 as the last row, the code was working all along it just I couldn’t see it as I was not expecting that to be the last row. I don’t even know how some data ended up in that cell.

I was not aware of this debug print function, very handy and I can now use going forward.

Thanks again

 

[greenhillchris]

Maybe set the ranges to copy and paste.

Sub Get_It()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim LR1 As Long, LR2 As Long
    Dim rng1 As Range, rng2 As Range
  
    Application.ScreenUpdating = False
  
    'set the workbooks
    Set wb1 = Workbooks.Open("C:\Users\davem\Downloads\Store Manager Tracker - 442 Staines.xlsx")
    Set wb2 = Workbooks.Open("C:\Users\davem\Downloads\Store Manager Individual Consultation Tracker - Master.xlsx")
  
    'set the sheets in workbooks
    Set sh1 = wb1.Sheets("Tracker")
    Set sh2 = wb2.Sheets("Tracker")
  
    'set the last row and range to copy
    With sh1
        LR1 = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set rng1 = .Range("B6:CM" & LR1)
    End With
  
    'set the paste range
    With sh2
        LR2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        Set rng2 = .Range("B" & LR2)
    End With
  
    'copy source range to destination range
    rng1.Copy rng2
  
    'close and save workbooks
    wb1.Close True
    wb2.Close True

End Sub

Hi Dave,

Thanks for the reply.

I have the code working now but I will be looking at simplifying the code and will look at your suggestion.

Thanks

 

[Anthony47]

Thank you for the feedback