VBA Copy to sheets

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
594
Office Version
  1. 365
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
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I would recommend checking what the values of "intRow" is at the time of error, then look at the "Master" sheet and see what is in the cell it is trying to pull from.
I have seen errors like that when it is trying to pull from a cell that has an error in it.
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,127
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top