Macros only work if I have a MsgBox

[03856me]

I have these three macros that run and they work perfectly if I include the message box - like the message box clears the previous macro event. The macros are splitting one row of data to 3 separate rows on another worksheet. Can someone help with what I need to include rather than a message box? I know their is probably a more efficient way to do this but this is my last day of work and I need it fixed quickly.

Sub CopyDollarsEtc()
    SubTotalCopy
    GSTCopy
    TotalCopy
End Sub

'=== Sub Total ===============================================
Sub SubTotalCopy()
    Dim wb As Workbook
        Set wb = ThisWorkbook
    Dim wsTo As Worksheet
        Set wsTo = wb.Sheets("upload")
    Dim wsFrom As Worksheet
        Set wsFrom = wb.Sheets("Sheet1")
        
    wsFrom.Range("B5:B5000").Copy wsTo.Range("D2")    'VendorID
    wsFrom.Range("C5:C5000").Copy wsTo.Range("E2")    'InvoiceNumber
    wsFrom.Range("E5:E5000").Copy wsTo.Range("L2")    'Account
    wsFrom.Range("J5:J5000").Copy wsTo.Range("F2")    'Total Amount
    wsFrom.Range("F5:F5000").Copy wsTo.Range("M2")    'Sub-Total Amount
    
    MsgBox "Sub-Totals Copied"
    
    End Sub
    
'=== GST =====================================================
Sub GSTCopy()

    Dim wb As Workbook
        Set wb = ThisWorkbook
    Dim wsTo As Worksheet
        Set wsTo = wb.Sheets("upload")
    Dim wsFrom As Worksheet
        Set wsFrom = wb.Sheets("Sheet1")
    Dim glastRow As Long
        glastRow = Range("D" & Rows.Count).End(xlUp).Row + 1
        
    wsFrom.Range("B5:B5000").Copy wsTo.Range("D" & glastRow)    'VendorID
    wsFrom.Range("C5:C5000").Copy wsTo.Range("E" & glastRow)    'InvoiceNumber
    wsFrom.Range("L5:L5000").Copy wsTo.Range("L" & glastRow)    'Account
    wsFrom.Range("J5:J5000").Copy wsTo.Range("F" & glastRow)    'Total Amount
    wsFrom.Range("G5:G5000").Copy wsTo.Range("M" & glastRow)    'GST Amount
    
    MsgBox "GST Copied"
      
End Sub

'=== TOTAL ===================================================
Sub TotalCopy()
    Dim wb As Workbook
        Set wb = ThisWorkbook
    Dim wsTo As Worksheet
        Set wsTo = wb.Sheets("upload")
    Dim wsFrom As Worksheet
        Set wsFrom = wb.Sheets("Sheet1")
    Dim tlastRow As Long
        tlastRow = Range("D" & Rows.Count).End(xlUp).Row + 1
        
    wsFrom.Range("B5:B5000").Copy wsTo.Range("D" & tlastRow)          'VendorID
    wsFrom.Range("C5:C5000").Copy wsTo.Range("E" & tlastRow)          'InvoiceNumber
    wsFrom.Range("D5:D5000").Copy wsTo.Range("L" & tlastRow)          'Account
    wsFrom.Range("J5:J5000").Copy wsTo.Range("F" & tlastRow)          'Total Amount
    wsFrom.Range("G5:G5000").Copy wsTo.Range("M" & tlastRow)          'Invoice Total
    
    MsgBox "Totals Copied"
        
End Sub

 

[03856me]

Sorry, I did not explain what wasn't working correctly - If the code works correctly, I should end up with 3 rows for every row of data. The first macro (SubTotalCopy) works correctly, the sedond (GSTCopy) is written over the first and the third is written over the second. I have 368 rows of data and I should end up with 1104 when this works. I suspect my lastrow code is not firing correclty.

 

[Fluff]

You need to qualify the range with the sheet like

glastRow = wsTo.Range("D" & Rows.Count).End(xlUp).Row + 1

 

[03856me]

You need to qualify the range with the sheet like

glastRow = wsTo.Range("D" & Rows.Count).End(xlUp).Row + 1

Thank you Thank you, it worked,

 

[Fluff]

You're welcome & thanks for the feedback.

 

[03856me]

Thank you Thank you, it worked,

One more question - the user pointed out the invoice amount is copying the formula and not the value. I tried fixing it but I can't get the syntax right.

Sub SubTotalCopy()
  
    Dim wb As Workbook
        Set wb = ThisWorkbook
    Dim wsTo As Worksheet
        Set wsTo = wb.Sheets("upload")
    Dim wsFrom As Worksheet
        Set wsFrom = wb.Sheets("Sheet1")
      
    wsFrom.Range("B5:B5000").Copy wsTo.Range("D2")    'VendorID
    wsFrom.Range("C5:C5000").Copy wsTo.Range("E2")    'InvoiceNumber
    wsFrom.Range("E5:E5000").Copy wsTo.Range("L2")    'Account
    wsFrom.Range("M5:M5000").Copy wsTo.Range("F"& Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues")    'Invoice Amount ?????
    wsFrom.Range("F5:F5000").Copy wsTo.Range("M2")    'Sub-Total Amount
  
    End Sub

 

[Fluff]

How about

wsTo.Range("F" & rows.Count).End(xlUp).Offset(1).Resize(4996).Value = wsFrom.Range("M5:M5000").Value

 

[03856me]

How about

wsTo.Range("F" & rows.Count).End(xlUp).Offset(1).Resize(4996).Value = wsFrom.Range("M5:M5000").Value

That worked - I really appreciate your quick response. Could you explain the "Resize(4996)" portion of the code

 

[Fluff]

That resizes the range so that it is the same size as the range being copied.