VBA Concatenate problem

[mcinnes01]

Hi,

I have a few different worksheets I have been working on for a while, but I am just adding some new functionality in for emailing that I have been working on. My problem is that before it created the output file by copying the data in to a temp worksheet then copy the temp worksheet to a new workbook.

The way I do this now is by creating a new workbook straight away, avoiding the need to create a temp worksheet.

This is probably me just being daft, but I have an odd field on the output that concatenates a few fields.. Below is an example of my original code.

Set wsData = Sheets("OUTPUT LIST")
Set wsCSV = Worksheets.Add(After:=Sheets(Sheets.Count))

With wsData

    wsCSV.Range("A1") = "EMP_ID"
    .Range("EmpRng").Copy wsCSV.Range("A2")
    
    wsCSV.Range("B1") = "ID_UNITS"
    .Range("HrsRng").Copy wsCSV.Range("B2")

    wsCSV.Range("C1") = "ID_RATE"
    .Range("RateRng").Copy wsCSV.Range("C2")

    wsCSV.Range("D1") = "ID_VALUE"
    .Range("ValRng").Copy wsCSV.Range("D2")
 
    wsCSV.Range("E1") = "PAYROLL_ID"
    .Range("CORng").Copy wsCSV.Range("E2")

    wsCSV.Range("F1") = "GEN_CODE"
    .Range("GLRng").Copy wsCSV.Range("F2")

    wsCSV.Range("G1") = "DESCRIPTION"
    .Range("FDRng").Copy wsCSV.Range("G2")
    
    wsCSV.Range("H1") = "ID_DATE"
    .Range("FDRng").Copy wsCSV.Range("H2")
    
    NR = 2
    For cell = 1 To (Range("FDRng").Cells.SpecialCells(xlCellTypeConstants).Count)
        Range("G" & NR) = .Range("FDRng").Cells(cell) & " - " & _
            .Range("TDRng").Cells(cell)
        NR = NR + 1
    Next cell
End With

wsCSV.Move
ActiveSheet.Name = "OVERTIME_CSV"

This is the new code I have but I am not sure how to get the concatenated field in?

    Set Sourcewb = ThisWorkbook
        
        With Sourcewb
            
            Set wsData = .Sheets("OUTPUT")

        End With
    
                    Set Destwb = Application.Workbooks.Add
            

                        With Destwb.Worksheets("Sheet1")

                            .Range("A1") = "EMP_ID"
                            .Range("B1") = "ID_UNITS"
                            .Range("C1") = "ID_RATE"
                            .Range("D1") = "ID_VALUE"
                            .Range("E1") = "PAYROLL_ID"
                            .Range("F1") = "GEN_CODE"
                            .Range("G1") = "DESCRIPTION"
                            .Range("H1") = "ID_DATE"
                            .Range("A2").Name = "Area"

                        End With
                    
                                With wsData
                            
                                    NR = .Cells(.Rows.Count, "A").End(xlUp).Row
                                    .Range("A2:H" & NR).Copy Destination:=Destwb.Worksheets("Sheet1").Range("Area")
                        
                                End With
            

ActiveSheet.Name = "OVERTIME_CSV"