When copying Cells A thru E from the source workbook, it only pastes columns C & D in the destination workbook?

[John.McLaughlin]

When copying Cells A thru E from the source workbook, it only pastes columns C & D in the destination workbook?

My Source workbook is an individual customers order with a worksheet named "List"
My destination workbook is named Pending and contains the worksheet "Sold"


The macro finds the last empty Row in the destination worksheet ok, but only the data in column C & D from the source workbook are pasted to the destination worksheet?


What am I missing?

Thanks in advance!

Sub Macro5()
'
   Application.ScreenUpdating = False
   
   Dim wbTarget            As Workbook 'workbook where the data is to be pasted
   Dim wbThis              As Workbook 'workbook from where the data is
   Dim strName             As String   'name of the source sheet/ target workbook 
   Dim filelink            As String   ' name of workbook
   Dim targetFile As String
   
' ----------------- Find last row
    Dim DstRng As Range
    Dim DstWks As Worksheet
    Dim LastRow As Long
    Dim N As Long, r As Long
    Dim SrcRng As Range
    Dim SrcWks As Worksheet




   'set to the current active workbook (the source worksheet)
   Set wbThis = ActiveWorkbook


   'unhide sheet and select it
    Sheets("List").Visible = True
    Sheets("List").Select
    
   'get the active sheetname of the open workbook
   strName = ActiveSheet.Name
    


    ' Activate open workbook
    Workbooks("Pending.xlsm").Activate
    Set wbTarget = ActiveWorkbook
    Sheets("Sold").Select


  
   'activate the source book
   wbThis.Activate
    
   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False
    
   
  ' copy the range from source book
    wbThis.Sheets("List").Range("A5:E5").Copy
   
   


' Assign the Worksheets
        Set SrcWks = wbThis.Sheets("List")
        Set DstWks = wbTarget.Sheets("Sold")
        
' Get all cells in the Source Range starting with row 5
        Set SrcRng = SrcWks.Range("A5:E5")
        LastRow = SrcWks.Cells(Rows.Count, "B").End(xlUp).Row
        If LastRow < SrcRng.Row Then Exit Sub Else Set SrcRng = SrcRng.Resize(LastRow - SrcRng.Row + 1, 5)
        
        
' Find the next empty row in the Destination Range starting at row 3
        Set DstRng = DstWks.Range("A3:E3")
        LastRow = DstWks.Cells(Rows.Count, "A").End(xlUp).Row
        Set DstRng = IIf(LastRow < DstRng.Row, DstRng, DstRng.Offset(LastRow - DstRng.Row + 1, 0))
        
          ' Copy the Source cells to the next empty Destination row if the Source Cell in "A" is not empty
            For r = 1 To SrcRng.Rows.Count
                If SrcRng.Cells(r, "A") <> "" Then
                   SrcRng.Rows(r).Copy DstRng.Offset(N, 0)
                   DstRng.Offset(N, 0).Value = DstRng.Offset(N, 0).Value
                   N = N + 1
                End If
            Next r
  
       
   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False
    
   'save the target book
   wbTarget.Save


   'activate the source book again
   wbThis.Activate


    'hide List sheet and select order sheet
    Sheets("List").Visible = False
    Sheets("ORDER").Select
    
    
 


Application.CutCopyMode = False
Application.ScreenUpdating = True
Range("A1").Select


wbThis.Activate
ActiveSheet.Unprotect
Range("F23").Select
    With Selection.Font
        .Name = "Trebuchet MS"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .Color = 5287936
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With


[ORDER!G$23].Value = Now()


ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
        
Application.CutCopyMode = False
Application.ScreenUpdating = True


Set wbTarget = Nothing
Set wbThis = Nothing






'
End Sub

 

[Fluff]

If you just want values try

            For r = 1 To SrcRng.Rows.Count
                If SrcRng.Cells(r, "A") <> "" Then
                   DstRng.Offset(N, 0).Resize(, 5).Value = SrcRng.Rows(r).Value
                   N = N + 1
                End If
            Next r

 

[igold]

I just ran your code on some fictitious data, and while your code may be a bit convoluted, it successfully copied all five columns A:E correctly to the destination workbook.

 

[John.McLaughlin]

If you just want values try

            For r = 1 To SrcRng.Rows.Count
                If SrcRng.Cells(r, "A") <> "" Then
                   DstRng.Offset(N, 0).Resize(, 5).Value = SrcRng.Rows(r).Value
                   N = N + 1
                End If
            Next r

Thanks Fluff, you never cease to amaze me

I just ran your code on some fictitious data, and while your code may be a bit convoluted, it successfully copied all five columns A:E correctly to the destination workbook.

igold, it ran successfully for me when I first wrote it, but when trying to use it on actual customer data I ran into the problem.


Fluff's answer seems to of fixed it, but why it worked once, and not again is beyond me.


Thank you both for your time and expertise

 

[Fluff]

I'm guessing the cols A,B & E have formulas, that when copied over to the other sheet were not returning anything.

 

[igold]

Thanks for the feedback. I am happy that Fluff was able to help you get what you needed.

 

[John.McLaughlin]

Fluffy,

All the source sheet cells had basic formulas like the one below. It simply combined the last name of the customer with his invoice number. If that offers any insight why the macro behaved as it did?

=IF(SHEET1!$B5<>"",TRIM(ORDER!B$4) &"  # "&TRIM(ORDER!B$3),"")

The source sheet was simply a "scratch pad" sheet that arranged the data I wanted to copy to the "running" sold list.

I've never taken any classes, and as my code shows, it's a little cobbled together

igold, success has many fathers, I appreciate you taking the time of sharing your thoughts too!!

 

[Fluff]

When that formula gets copied from row 5 on list, to row 3 on sold, it will be looking at Sheet1 B3. If that is blank then the formula will return "" so it will appear as if nothing has been copied.