Concatenating a value from a range before pasting it into another worksheet

[Talat]

Hi

I have the following code which works by copying a range of cells from one worksheet and pastes them into another. However, I now need to change one of the cell values from the range by adding an apostrophy ( ' ) in front of it so that if the cell value was 452 it becomes '452

 Sub InvoiceToBatch() 
     'Copies the current invoice in the INVOICE TEMPLATE sheet of
     'this workbook to stocksbatch.xls
    Dim myRange      As String 
    Dim Account       As String 
    Dim InvDate       As Date 
    Dim InvNum        As Long 
    Dim InvSheet      As Worksheet 
    Dim BatchSheet    As Worksheet 
    Dim NextRow       As Long 'the next available invoice row on the batch sheet
    Dim oRow          As Long 'row number on BatchSheet
    Dim iRow          As Long 'row number on InvSheet
     
    Set InvSheet = ThisWorkbook.Worksheets("INVOICE TEMPLATE") 
     
    Workbooks.Open Filename:="G:\PUBS\PP-MS\INVOICES\stocksbatch.xls" 
    Set BatchSheet = ActiveWorkbook.Worksheets("Sheet1") 
     
    oRow = BatchSheet.UsedRange.Rows.Count + 1 
    iRow = 20 
     
     
    Do 
         
        myRange = InvSheet.Range(InvSheet.Cells(iRow, "B"), InvSheet.Cells(iRow, "K")).Address & _ 
        "," & InvSheet.Cells(iRow, "Q").Address 
        InvSheet.Range(myRange).Copy 
         
        BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues 
         
        InvSheet.Range("E5").Copy 'Account
        BatchSheet.Cells(oRow, "A").PasteSpecial xlPasteValues 
        InvSheet.Range("K2").Copy 'InvNum
        BatchSheet.Cells(oRow, "B").PasteSpecial xlPasteValues 
        InvSheet.Range("F17").Copy 'InvDate
        BatchSheet.Cells(oRow, "C").PasteSpecial xlPasteValues 
         
        iRow = iRow + 1 
        oRow = oRow + 1 
    Loop Until IsEmpty(InvSheet.Cells(iRow, "B")) Or InvSheet.Cells(iRow, "B") = Q 
    Application.CutCopyMode = False 
    ActiveWorkbook.Close True 'save changes and close
    Application.Run "FAPBatchFile" 
     
End Sub

This change need to be applied to values in (iRow, "Q") where the value of i can be from 20 to 35. I tried adding a line like

set value (iRow,"Q")= "'" & (iRow,"Q") but I get an error.

Can anyone help solve this for me?

Thanks.

Talât

 

[Jerry Sullivan]

Hi Talât,

This statement will do what you describe...

Cells(iRow, "Q") = "'" & Cells(iRow, "Q")

If the reason you're adding the apostrophe is to format the cell as text,
then this is generally a better approach...

Cells(iRow, "Q").NumberFormat = "@"

 

[Talat]

Thanks Jerry.

I tried both methods, but I am afraid I have not managed to get this to work. I am guessing that it matters where I place the code within the overall code, and although I tried various , to me obvious places, I did not manage to get it to work.

Sorry!

 

[Jerry Sullivan]

No worries. My response was giving you the correct syntax in place of your try of:
set value (iRow,"Q")= "'" & (iRow,"Q")
....and didn't really address how to integrate it into your overall procedure.

Taking another look at this, are the cells that you want to have formatted as text actually in BatchSheet.Cells(oRow, "D")?

 

[Talat]

Thanks again Jerry.

No, the values are in InvSheet in colum Q and can be anywhere in rows Q20 to row Q38.

The code copies them from these locations and pastes into the Stocksbatch sheet. It is at this stage that I need to concatenate the value of Q(x) with a " ' " so that if it was 410 in INVSheet it becomes '410 in the Stockbatch sheet.

Talât

 

[Jerry Sullivan]

I think we are saying the same thing. The values that are in Col Q don't actually get changed there, but rather in the destination cells after pasting.

One method that should work would be to modify the code like this:

InvSheet.Range(myRange).Copy 

BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues 
BatchSheet.Cells(oRow, "D") = "'" & BatchSheet.Cells(oRow, "D")

BTW, I should note that if you were copying large amounts of data, the code could be made more efficient by not stepping through each row. For the number of rows you have though this difference would not be noticable.

 

[Talat]

I am sorry, Jerry, but it is still being stubborn and not working.

I put the code in as you suggested, but I still do not see any apostophy ( ' ) in front of the number copied across from the INVSheet.

Shall I send you the files?

Talât

 

[Jerry Sullivan]

Hi again Talât,

Sorry that I didn't have time to study this more thoroughly earlier today.

The problem was the multiple ranges that the code was copying. When you copy a range like "B20:K38,Q20:Q38" to Cell D20, then the values that were in Column Q of InvSheet are actually pasted into Column N of BatchSheet.

So with this adjustment, your original code this should work....

InvSheet.Range(myRange).Copy 

BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues 
BatchSheet.Cells(oRow, "N") = "'" & BatchSheet.Cells(oRow, "N")

I also wanted to show you one way to revise the code to copy the data without looping through each row, which is a bit more efficient.
This also formats Col N of batchsheet as Text instead of adding the apostrophe, which is usually a better practice.

Sub InvoiceToBatch2()
     'Copies the current invoice in the INVOICE TEMPLATE sheet of
     'this workbook to stocksbatch.xls
    Dim wsInvSheet    As Worksheet
    Dim sAccount      As String
    Dim dtInvDate     As Date
    Dim lInvNum       As Long
    Dim lRowBatch     As Long 'next available row on batchsheet
    Dim lNumRows      As Long 'number of rows to copy
    Application.ScreenUpdating = False
    
    Set wsInvSheet = ThisWorkbook.Worksheets("INVOICE TEMPLATE")
    With wsInvSheet
        lNumRows = .Cells.Find(What:="*", After:=.Range("A1"), _
                SearchDirection:=xlPrevious).Row - 20 + 1
        If lNumRows < 1 Then Exit Sub
        sAccount = .Range("E5")
        lInvNum = .Range("K2")
        dtInvDate = .Range("F17")
    End With
                
    Workbooks.Open Filename:="G:\PUBS\PP-MS\INVOICES\stocksbatch.xls"
    With ActiveWorkbook.Worksheets("Sheet1")
        lRowBatch = .Cells.Find(What:="*", After:=.Range("A1"), _
                SearchDirection:=xlPrevious).Row + 1
        wsInvSheet.Range("B20:K20").Resize(lNumRows).Copy
        .Cells(lRowBatch, "D").PasteSpecial xlPasteValues
        wsInvSheet.Range("Q20").Resize(lNumRows).Copy
        .Cells(lRowBatch, "N").PasteSpecial xlPasteValues
        .Cells(lRowBatch, "N").Resize(lNumRows).NumberFormat = "@"
        .Cells(lRowBatch, "A").Resize(lNumRows) = sAccount
        .Cells(lRowBatch, "B").Resize(lNumRows) = lInvNum
        .Cells(lRowBatch, "C").Resize(lNumRows) = dtInvDate
    End With
    Application.CutCopyMode = False
    ActiveWorkbook.Close True 'save changes and close
    Application.Run "FAPBatchFile"
End Sub

 

[Talat]

Hi Jerry,

Apologies for the delay in getting back to you. I was unwell last few days.

This is working now with your suggested code below.

 InvSheet.Range(myRange).Copy 

BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues 
BatchSheet.Cells(oRow, "N") = "'" & BatchSheet.Cells(oRow, "N")

As for the other more substantive to replace the original subprocedure, it is not. It is copying and pasting many more lines of unwanted data from the INVSHEET.

As I have a working code now, I am do not really want you to spend time looking at this. The process is not sluggish at all, so I can live with it.

Also thanks for your PM. If you still need me to send you the files for a bigger picture for your own curiosity, I am happy to do that. Let me know.

Until then, many many thanks for your help and perseverence with me on this. I appreciate it.

Talât