How to simplify the copy content code?

Question

I have a series of code to trace the file path and trace out the data from multiple excel file.

I was wondering is there any way I can modify the code so I do not need to keep copy and paste the same code by just changing the cell value?

Here will be the code:

Sub FetchData()

Dim shDestin As Worksheet
Application.ScreenUpdating = False
Set shDestin = ThisWorkbook.Sheets("Sheet1")
CopyFileContent Range("B11").Value & shDestin.Range("A1").Value, _
shDestin, 11
CopyFileContent Range("B12").Value & shDestin.Range("A1").Value, _
shDestin, 12
CopyFileContent Range("B13").Value & shDestin.Range("A1").Value, _
shDestin, 13
CopyFileContent Range("B14").Value & shDestin.Range("A1").Value, _
shDestin, 14
CopyFileContent Range("B15").Value & shDestin.Range("A1").Value, _
shDestin, 15
CopyFileContent Range("B16").Value & shDestin.Range("A1").Value, _
shDestin, 16
CopyFileContent Range("B17").Value & shDestin.Range("A1").Value, _
shDestin, 17
CopyFileContent Range("B18").Value & shDestin.Range("A1").Value, _
shDestin, 18
CopyFileContent Range("B19").Value & shDestin.Range("A1").Value, _
shDestin, 19

Is there any way like it can set range like B11:B19 then shDestin 11:19? It will be trouble because in future I might have 50+ file in a folder to trace the data...

Answer 1

You can also try For Each which I think is more readable.

Dim cel As Range

For Each cel In Range("B11:B19") '~~> or any range you want
    CopyFileContent cel.Value & shDestin.Range("A1").Value _
        , shDestin, cel.Row
Next