I have two questions please.
In the code below, it brings up an error after copying 84 lines of data, is there a limit of how many cells the code copies(?), as I cannot see any inconsistency in the data as to why it brings up an error. (RUN-TIME ERROR 13 TYPE MISMATCH (Highlighted line in VBA Code - s
Also I want to have a header of the sheets it copies to, what do I need to change so I can have a header please?
In the code below, it brings up an error after copying 84 lines of data, is there a limit of how many cells the code copies(?), as I cannot see any inconsistency in the data as to why it brings up an error. (RUN-TIME ERROR 13 TYPE MISMATCH (Highlighted line in VBA Code - s
Code:
trAnswer20 = Sheets("Master").Cells(intRow, 22).Value
Also I want to have a header of the sheets it copies to, what do I need to change so I can have a header please?
Code:
Sub UpdateSheets()
Dim wksSheet As Worksheet
Dim intRow As Integer
Dim intLastRow As Integer
Dim strDataSheet As String
Dim strQuestion As String
Dim strAnswer As String
Dim strAnswer2 As String
Dim strAnswer3 As String
Dim strAnswer4 As String
Dim strAnswer5 As String
Dim strAnswer6 As String
Dim strAnswer7 As String
Dim strAnswer8 As String
Dim strAnswer9 As String
Dim strAnswer10 As String
Dim strAnswer11 As String
Dim strAnswer12 As String
Dim strAnswer13 As String
Dim strAnswer14 As String
Dim strAnswer15 As String
Dim strAnswer16 As String
Dim strAnswer17 As String
Dim strAnswer18 As String
Dim strAnswer19 As String
Dim strAnswer20 As String
Dim strAnswer21 As String
Dim strAnswer22 As String
Dim strAnswer23 As String
Dim strAnswer24 As String
Dim strAnswer25 As String
' Clear category sheets.
For Each wksSheet In ThisWorkbook.Worksheets
If wksSheet.Name <> "Master" Then
wksSheet.Cells.Clear
End If
Next wksSheet
' Count Master.
intLastRow = Sheets("Master").Range("A1").End(xlDown).Row
For intRow = 1 To intLastRow
strDataSheet = Sheets("Master").Cells(intRow, 1).Value
strQuestion = Sheets("Master").Cells(intRow, 2).Value
strAnswer = Sheets("Master").Cells(intRow, 3).Value
strAnswer2 = Sheets("Master").Cells(intRow, 4).Value
strAnswer3 = Sheets("Master").Cells(intRow, 5).Value
strAnswer4 = Sheets("Master").Cells(intRow, 6).Value
strAnswer5 = Sheets("Master").Cells(intRow, 7).Value
strAnswer6 = Sheets("Master").Cells(intRow, 8).Value
strAnswer7 = Sheets("Master").Cells(intRow, 9).Value
strAnswer8 = Sheets("Master").Cells(intRow, 10).Value
strAnswer9 = Sheets("Master").Cells(intRow, 11).Value
strAnswer10 = Sheets("Master").Cells(intRow, 12).Value
strAnswer11 = Sheets("Master").Cells(intRow, 13).Value
strAnswer12 = Sheets("Master").Cells(intRow, 14).Value
strAnswer13 = Sheets("Master").Cells(intRow, 15).Value
strAnswer14 = Sheets("Master").Cells(intRow, 16).Value
strAnswer15 = Sheets("Master").Cells(intRow, 17).Value
strAnswer16 = Sheets("Master").Cells(intRow, 18).Value
strAnswer17 = Sheets("Master").Cells(intRow, 19).Value
strAnswer18 = Sheets("Master").Cells(intRow, 20).Value
strAnswer19 = Sheets("Master").Cells(intRow, 21).Value
strAnswer20 = Sheets("Master").Cells(intRow, 22).Value
strAnswer21 = Sheets("Master").Cells(intRow, 23).Value
strAnswer22 = Sheets("Master").Cells(intRow, 24).Value
strAnswer23 = Sheets("Master").Cells(intRow, 25).Value
strAnswer24 = Sheets("Master").Cells(intRow, 26).Value
strAnswer25 = Sheets("Master").Cells(intRow, 27).Value
' Find next row in category sheet.
If Sheets(strDataSheet).Cells(1, 1).Value = "" Then
intRowDataSheet = 1
Else
intRowDataSheet = Sheets(strDataSheet).Cells(Rows.Count, 1).End(xlUp).Row + 1
End If
' Update category sheets.
Sheets(strDataSheet).Cells(intRowDataSheet, 1).Value = strQuestion
Sheets(strDataSheet).Cells(intRowDataSheet, 2).Value = strAnswer
Sheets(strDataSheet).Cells(intRowDataSheet, 3).Value = strAnswer2
Sheets(strDataSheet).Cells(intRowDataSheet, 4).Value = strAnswer3
Sheets(strDataSheet).Cells(intRowDataSheet, 5).Value = strAnswer4
Sheets(strDataSheet).Cells(intRowDataSheet, 6).Value = strAnswer5
Sheets(strDataSheet).Cells(intRowDataSheet, 7).Value = strAnswer6
Sheets(strDataSheet).Cells(intRowDataSheet, 8).Value = strAnswer7
Sheets(strDataSheet).Cells(intRowDataSheet, 9).Value = strAnswer8
Sheets(strDataSheet).Cells(intRowDataSheet, 10).Value = strAnswer9
Sheets(strDataSheet).Cells(intRowDataSheet, 11).Value = strAnswer10
Sheets(strDataSheet).Cells(intRowDataSheet, 12).Value = strAnswer12
Sheets(strDataSheet).Cells(intRowDataSheet, 13).Value = strAnswer13
Sheets(strDataSheet).Cells(intRowDataSheet, 14).Value = strAnswer14
Sheets(strDataSheet).Cells(intRowDataSheet, 15).Value = strAnswer15
Sheets(strDataSheet).Cells(intRowDataSheet, 16).Value = strAnswer16
Sheets(strDataSheet).Cells(intRowDataSheet, 17).Value = strAnswer17
Sheets(strDataSheet).Cells(intRowDataSheet, 18).Value = strAnswer18
Sheets(strDataSheet).Cells(intRowDataSheet, 19).Value = strAnswer19
Sheets(strDataSheet).Cells(intRowDataSheet, 20).Value = strAnswer20
Sheets(strDataSheet).Cells(intRowDataSheet, 21).Value = strAnswer21
Sheets(strDataSheet).Cells(intRowDataSheet, 22).Value = strAnswer22
Sheets(strDataSheet).Cells(intRowDataSheet, 23).Value = strAnswer23
Sheets(strDataSheet).Cells(intRowDataSheet, 24).Value = strAnswer24
Sheets(strDataSheet).Cells(intRowDataSheet, 25).Value = strAnswer25
Sheets(strDataSheet).Cells(intRowDataSheet, 12).Value = strAnswer26
Sheets(strDataSheet).Cells(intRowDataSheet, 12).Value = strAnswer11
Next intRow
' Size columns.
For Each wksSheet In ThisWorkbook.Worksheets
If wksSheet.Name <> "Master" Then
wksSheet.Columns("A:B").AutoFit
End If
Next wksSheet
End Sub