Ignore formulas while Pasting values while copying from data table to multiple sheets?-CodePudding

I am trying to transfer data from a database into multiple sheets in a workbook having the same template. However, I wan't to ignore the formulas while copying. My current macro gives error of circular referencing while transferring the data.

How can I ignore the formula while copy data to different templates through a looping process.

Also I would like to know how can I transfer data from multiple/different columns referenced from wkSht.Name to different columns of the template sheets.

The following code only transfers data from Range(cell.Offset(0, 18), cell.Offset(18, 18) of the master sheet to O17 cell of the template sheets of the same workbook.

Can anyone update the code and add criteria for copying cell values only instead of formula.

Sub FC()
Dim wkSht As Worksheet
Dim cell As Range

For Each cell In Sheets("Combine").Range("A4:A600").Cells

    For Each wkSht In ThisWorkbook.Worksheets
    
        If cell.Value = wkSht.Name Then
       
        Sheets("Combine").Range(cell.Offset(0, 18), cell.Offset(18, 18)).Copy wkSht.Range("O17")
        
        End If
    
    Next wkSht

Next cell

Application.ScreenUpdating = True

End Sub

CodePudding user response：

Please, test the next code:

Sub FC()
 Dim wkSht As Worksheet, wsC As Worksheet, rngSearch As Range
 Dim shNCell As Range

Set wsC = Sheets("Combine")
Set rngSearch = wsC.Range("A4:A600")
    For Each wkSht In ThisWorkbook.Worksheets
        'find the sheet name cell in rngSearch:
        Set shNCell = rngSearch.Find(what:=wkSht.Name, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
        'if found:
        If Not shNCell Is Nothing Then
          'copy the below built array in the necessary place
          wkSht.Range("O17").Resize(19, 1).value = wsC.Range(shNCell.Offset(0, 18), shNCell.Offset(18, 18)).value
        End If
    Next wkSht
End Sub

Not tested, but this should be the idea. If something goes wrong, please tell me what the problem it does (or does not...).

I will comment the code lines in some minutes, after finishing something urgent...

CodePudding user response：

The formula works for different cells too