Need to add destination worksheet row number as prefix to imported data ( dash and 5 digits)

[sspatriots]

Hi,

I'm trying to figure out how to add the destination sheet row number at the beginning of the content copied from Cell C3 of the source worksheets that this code is looping through.

For example:

If cell C3 of the source worksheet is "Arrow Street" and it is going into row 15 of the destination worksheet, I need the entry in column A15 of the destination worksheet to read "00015 - Arrow Street".

I'm guessing this is the line of code that needs to change:

wsSummary.Range("A" & nr).Value = wsData.Range("C3").Value

Any ideas would be greatly appreciated...

Sub ImportInfo()
    Dim sPath As String 'path of folder containing info
    Dim sFileName As String '
    Dim wsSummary As Worksheet 'worksheet to paste data to in this workbook
    Dim wsData As Worksheet 'sheet with data to copy
    Dim wb As Workbook 'workbooks to loop through
    Dim nr As Long 'next row to add the data
    Dim cbValue As Variant
    
    'Get the worksheet to add the info to
    Set wsSummary = ThisWorkbook.Worksheets("Sheet1")
    
        
    'first row is 2
    nr = 2
    
    sPath = "C:\Users\SShockley\OneDrive - Delaware Elevator\Documents\FACTORY RELEASE FORMS\" '[COLOR=#ff0000][B]Change as required[/B][/COLOR]
    
    sFileName = Dir(sPath & "*.xlsm")
    
    
    Application.ScreenUpdating = False
    
    
    Do While sFileName <> ""
        'open workbook
        Set wb = Workbooks.Open(Filename:=sPath & sFileName, ReadOnly:=True)
        'get the sheet to copy from
        
        
        Set wsData = wb.Sheets("DEM Release Form")
        'get the data
        
        wsSummary.Range("A" & nr).Value = wsData.Range("C3").Value
        wsSummary.Range("B" & nr).Value = wsData.Range("C4").Value
        wsSummary.Range("C" & nr).Value = wsData.Range("H4").Value
        wsSummary.Range("D" & nr).Value = wsData.Range("B8").Value
        wsSummary.Range("E" & nr).Value = wsData.Range("F8").Value
        wsSummary.Range("F" & nr).Value = wsData.Range("C10").Value
        wsSummary.Range("G" & nr).Value = wsData.Range("G10").Value
        wsSummary.Range("H" & nr).Value = wsData.Range("B9").Value
        wsSummary.Range("I" & nr).Value = wsData.Range("I9").Value
        
    
    If cbValue = ActiveSheet.OLEObjects("CheckBox1").Object.Value = "True" Then
        
        wsSummary.Range("K" & nr).Value = "YES"
        
    Else
            
        wsSummary.Range("K" & nr).Value = ""

    End If
        
        
    If cbValue = ActiveSheet.OLEObjects("CheckBox2").Object.Value = "True" Then
        
        wsSummary.Range("J" & nr).Value = "YES"
        
    Else
            
        wsSummary.Range("J" & nr).Value = ""

        
    End If
        
     
        'get next row
        nr = nr + 1
        'close the workbook
        wb.Close
        'get next workbook name
        sFileName = Dir
    
    
    Loop
    
    
    Application.ScreenUpdating = True
    
    
    
End Sub

Thanks, SS

 

[Micron]

Using the information given I would think

wsSummary.Range("A" & nr).Value = "000" & nr & wsData.Range("C3").Value

assuming you're getting your "row 15" from nr

 

[sspatriots]

That did it. Thanks so much.

 

[Micron]

You're welcome. I wish they were all that simple.
I half expect to find that when the number is 150, you don't want 000150.

 

[Joe4]

You're welcome. I wish they were all that simple.
I half expect to find that when the number is 150, you don't want 000150.

If that was the case, and you always wanted it to be 5 digits, and just pad zeroes on the front end, that can be done pretty easily by changing this part:

"000" & nr

to this:

Format(nr, "00000")

and that would be able to handle 15, 150, etc.