Assign value without formatting

Question

I'm looping through workbooks to aggregate the data to one sheet. The data on the various source sheets is always in the same columns but the rows will vary.

I'm assigning values but conditional formatting is coming through.

Screen updating is off.

How can I copy values from one book to another?

For Each sheet In Workbooks(filename).Worksheets
    If sheet.Name = "Template" Then
        lastrow = sheet.Range("A" & Rows.Count).End(xlUp).row
        For row = 2 To lastrow
            All.Range("A" & All_nextrow).Value = sheet.Range("A" & row).Value
            All.Range("B" & All_nextrow).Value = sheet.Range("B" & row).Value
            All.Range("C" & All_nextrow).Value = sheet.Range("C" & row).Value
            All.Range("D" & All_nextrow).Value = sheet.Range("D" & row).Value
            All.Range("E" & All_nextrow).Value = sheet.Range("E" & row).Value
            All.Range("F" & All_nextrow).Value = sheet.Range("F" & row).Value
            All.Range("G" & All_nextrow).Value = sheet.Range("G" & row).Value
            All.Range("H" & All_nextrow).Value = sheet.Range("H" & row).Value
            All.Range("I" & All_nextrow).Value = sheet.Range("I" & row).Value
            All.Range("J" & All_nextrow).Value = sheet.Range("J" & row).Value
            All.Range("K" & All_nextrow).Value = sheet.Range("K" & row).Value
            All.Range("L" & All_nextrow).Value = Workbooks(filename).Name
            All_nextrow = All_nextrow + 1
        Next row
    End If
Next sheet

Answer 1

This might be what you are looking for. There are some issues with your code you need to know.

In this example, I'm using "All" as the name of the sheet you are putting this onto.

This code uses a simple loop to go through the columns, where you were using Range and the actual letter name of the column, this uses Cells(lRow, lCol) and loops that way, until you get to column L where you change the pattern.

I also removed the for each worksheet, because you are running an IF statement making ONLY "TEMPLATE" the one that will be used. So there is no need to loop through all of them to find the one you know you want. If you meant to use more than that, the If Sheet.Name = "Template" needs to go.

Give this code a shot, and modify it to your needs. I will be happy to modify the answer if you comment with any glitches.

Sub DataAggregate()

Dim sheet As String
Dim all As String
Dim allRow As Long

    all = "All"    'whatever the name of "ALL" is, set here.
    allRow = 2

    sheet = "Template"

    lastRow = Sheets(sheet).Range("A" & Rows.Count).End(xlUp).row
        For lRow = 2 To lastRow
            For lCol = 1 To 11
                Sheets(all).Cells(allRow,lCol) = Sheets(sheet).Cells(lRow, lCol).Text
            Next lCol

            Sheets(all).Cells(allRow, "L") = sheet  'or filename'  'confused as to what you want
            allRow = allRow + 1
        Next lRow
    Next ws
End Sub