Append text to paste in VBA

[dwool40]

I am using the following to copy and paste. I would like to add text to the pasted values. For example, original paste would be 5830D. I would like to add to the end so it would be 5830D-902

Sub Copynotice()
'enter correct path to Downloads folder
    sFile = Sheets("Setup").Range("C10").Value & "\DataGridExport.xlsx"
    If Dir(sFile) = "" Then
        MsgBox "Please download today's Pending Make Ready Report."
        Exit Sub
    End If
Dim wb1 As Workbook, wb2 As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook
'enter correct path to Downloads folder
Set wb2 = Workbooks.Open(Sheets("Setup").Range("C10").Value & "\DataGridExport.xlsx")
wb2.Sheets("Report1").Range("E2:E120").Copy
wb1.Sheets("Notice").Range("D2").PasteSpecial Paste:=xlPasteValues
wb2.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Micron]

I wouldn't suggest this if you had more columns and didn't know what the paste range would be but perhaps

Dim i As Integer

'after your paste line:

For i = 2 to 120
  If wb1.Sheets("Notice").Range("D" & i) <> "" Then Range("D" & i) = Range("D" & i) & "-902"
Next

Seems to me that if you're copying rows 2 to 120 from 1 column into D2 then the target range is D2:D120
One could assign the cell value to a variable to shorten that line, but hey...

 

[dwool40]

I wouldn't suggest this if you had more columns and didn't know what the paste range would be but perhaps

Dim i As Integer

'after your paste line:

For i = 2 to 120
  If wb1.Sheets("Notice").Range("D" & i) <> "" Then Range("D" & i) = Range("D" & i) & "-902"
Next

Seems to me that if you're copying rows 2 to 120 from 1 column into D2 then the target range is D2:D120
One could assign the cell value to a variable to shorten that line, but hey...

What I have is multiple workbooks downloaded daily that need the data imported to workbook Notice column D. The paste rage can be infinite but the copy range is rarely above 120 but can also be infinite. I use one macro to run Copynotice(), Copynotice2(), Copynotice3() and so on.

Sub Copynotice()
'enter correct path to Downloads folder
    sFile = Sheets("Setup").Range("C10").Value & "\DataGridExport.xlsx"
    If Dir(sFile) = "" Then
        MsgBox "Please download today's Pending Make Ready Report."
        Exit Sub
    End If
Dim wb1 As Workbook, wb2 As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook
'enter correct path to Downloads folder
Set wb2 = Workbooks.Open(Sheets("Setup").Range("C10").Value & "\DataGridExport.xlsx")
wb2.Sheets("Report1").Range("E2:E120").Copy
wb1.Sheets("Notice").Range("D2").PasteSpecial Paste:=xlPasteValues
wb2.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Sub Copynotice2()
'enter correct path to Downloads folder
    sFile = Sheets("Setup").Range("C10").Value & "\DataGridExport (1).xlsx"
    If Dir(sFile) = "" Then
        MsgBox "Please download today's Pending Make Ready Report."
        Exit Sub
    End If
Dim wb1 As Workbook, wb2 As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook
'enter correct path to Downloads folder
Set wb2 = Workbooks.Open(Sheets("Setup").Range("C10").Value & "\DataGridExport (1).xlsx")
wb2.Sheets("Report1").Range("E2:E120").Copy
wb1.Sheets("Notice").Range("D2").End(xlDown).Offset(1).PasteSpecial Paste:=xlPasteValues
wb2.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

The issue is there might be duplicated data that I need to differentiate which is why I want to add the text. So, for Copynotice() add "text1, Copynotice2() "text2", etc. This could go on up to 10 times. Would it be easier for each to go to a separate sheet and run another macro to reference the individual sheets?

 

[Micron]

I don't understand why you'd hard code ranges if they're size is volatile. For that you could determine the used range that's being copied, pass that value to a variable then make the loop end at that value. However, I've been told that looping like that is inefficient over large ranges.

As for having 10 macros for 10 sheets, I guess you'd have to decide what is easier. If the source workbook paths and the count of workbooks is static you could loop over a sheet in the destination workbook that contains their paths instead of editing 10 macros every time there's an issue. You might also want to look into dynamic named ranges. I'm not positive, but believe you can use a named range in code in the same way as a range reference such as A1:A10.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Append text to paste in VBA
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.