VBA Code to Insert a Row

[cleteh]

Trying to figure out why I'm getting an error of Insert Method of Range class failed in the code below highlighted red. Im simply trying to insert a new row at line 9 in the excel sheet titled Score Table.


Private Sub ImportForm(whichForm As String, whichRow As String, refresh As Boolean, filePath As String, ImportMultiple As Boolean)

Application.ScreenUpdating = False

Dim fileDialog As fileDialog
Dim strPathFile As String, strPath As String, dialogTitle As String, scoreTableTitle As String, potentialTitle As String, auditInstance As String
Dim wbSource As Workbook
Dim rngToCopy As Range, rngDestin As Range
Dim numQuestions As Double

If refresh = False And ImportMultiple = False Then
dialogTitle = "Please select Form " & whichForm & "..."
Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
With fileDialog
.InitialFileName = ThisWorkbook.Path & "\"
.AllowMultiSelect = False
.Filters.Clear
.Title = dialogTitle
If .Show = False Then
MsgBox "Abort! Form " & whichForm & " was not selected."
Exit Sub
End If

strPathFile = .SelectedItems(1)
Set wbSource = Workbooks.Open(FileName:=strPathFile, ReadOnly:=True)

'IF MISC FORM DO NOTHING AND EXIT SUB
'Error here
If wbSource.ActiveSheet.Range("A3") = "Misc Assessment Form" Then
ThisWorkbook.Sheets("Score Table").Range("A9").EntireRow.Insert
Exit Sub

[ranman256]

ACTIVEWORKBOOK.sheets(...

[cleteh]

I still get an error using Active Workbook... I want to insert this row into the excel sheet containg the macro im running which is why I thought ThisWorkbook would work but neither of them do. I think the Active workbook would be the workbook containg the form im importing.

[SamT]

Either use a line Continuation Mark (Space+Underscore) or close the IF

Either

If this Then _
That

Or

If This Then
That
End If

[cleteh]

Thanks Sam but her is the complete code for the procedure....


Private Sub ImportForm(whichForm As String, whichRow As String, refresh As Boolean, filePath As String, ImportMultiple As Boolean)

Application.ScreenUpdating = False

Dim fileDialog As fileDialog
Dim strPathFile As String, strPath As String, dialogTitle As String, scoreTableTitle As String, potentialTitle As String, auditInstance As String
Dim wbSource As Workbook
Dim rngToCopy As Range, rngDestin As Range
Dim numQuestions As Double


If refresh = False And ImportMultiple = False Then
dialogTitle = "Please select Form " & whichForm & "..."
Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
With fileDialog
.InitialFileName = ThisWorkbook.Path & "\"
.AllowMultiSelect = False
.Filters.Clear
.Title = dialogTitle
If .Show = False Then
MsgBox "Abort! Form " & whichForm & " was not selected."
Exit Sub
End If

strPathFile = .SelectedItems(1)
Set wbSource = Workbooks.Open(FileName:=strPathFile, ReadOnly:=True)

'IF MISC FORM DO NOTHING AND EXIT SUB
Error_Here:
If wbSource.ActiveSheet.Range("A3") = "Misc Assessment Form" Then
ThisWorkbook.Sheets("Score Table").Range("A9").EntireRow.Insert
Exit Sub
Else

' Create new worksheet to hold the raw data
ThisWorkbook.Sheets.Add.Name = "Form " & whichForm & " Data"
ThisWorkbook.Sheets("Form " & whichForm & " Data").Visible = False

' If importing for the first time, create the form the user will see
Call SheetCopy(whichForm)
End If
End With

ElseIf refresh = True Then
strPathFile = filePath
Set wbSource = Workbooks.Open(FileName:=strPathFile, ReadOnly:=True)
With wbSource.ActiveSheet

' Create new worksheet to hold the raw data
ThisWorkbook.Sheets.Add.Name = "Form " & whichForm & " Data"
ThisWorkbook.Sheets("Form " & whichForm & " Data").Visible = False

Set rngToCopy = .Range(.Cells(1, "A"), .Cells(1000, "DA"))
Set rngDestin = ThisWorkbook.Sheets("Form " & whichForm & " Data").Cells(1, "A")
rngToCopy.Copy Destination:=rngDestin
End With

ElseIf ImportMultiple = True Then

strPathFile = filePath
Set wbSource = Workbooks.Open(FileName:=strPathFile, ReadOnly:=True)

' Create new worksheet to hold the raw data
ThisWorkbook.Sheets.Add.Name = "Form " & whichForm & " Data"
ThisWorkbook.Sheets("Form " & whichForm & " Data").Visible = False


' If importing for the first time, create the form the user will see
Call SheetCopy(whichForm)

End If
With wbSource.ActiveSheet
Set rngToCopy = .Range(.Cells(1, "A"), .Cells(1000, "DA"))
Set rngDestin = ThisWorkbook.Sheets("Form " & whichForm & " Data").Cells(1, "A")
rngToCopy.Copy Destination:=rngDestin
End With

' Close the workbook
wbSource.Close SaveChanges:=False
Set fileDialog = Nothing
Set rngToCopy = Nothing
Set wbSource = Nothing
Set rngDestin = Nothing

' Calculate file name length to break out the filename for later use
Dim pathLen As Double, counter As Double, strLen As Double
pathLen = Len(strPathFile)
counter = 1
Do While counter < pathLen
counter = counter + 1
If Left(Right(strPathFile, counter), 1) = "\" Then
pathLen = counter - 1
counter = pathLen
End If
Loop

' Get number of questions and 'Audit Instance', proceed only if 100 questions or less and valid 'audit instance' number
numQuestions = ThisWorkbook.Sheets("Form " & whichForm & " Data").Range("F6").Value
auditInstance = ThisWorkbook.Sheets("Form " & whichForm & " Data").Range("C6").Value

If numQuestions <= 100 And IsNumeric(auditInstance) = True Then

' Put the file name and date/time on the Import sheet
ThisWorkbook.Sheets("Import").Unprotect
ThisWorkbook.Sheets("Import").Range("$D$" & whichRow).Value = Right(strPathFile, pathLen) & " last imported on " & Date & " at " & Time
ThisWorkbook.Sheets("Import").Protect

ThisWorkbook.Sheets("Form Analysis").Unprotect
ThisWorkbook.Sheets("Form Analysis").Range("$C$" & whichForm + 1).Value = Date
ThisWorkbook.Sheets("Form Analysis").Range("$D$" & whichForm + 1).Value = Time
ThisWorkbook.Sheets("Form Analysis").Range("$E$" & whichForm + 1).Value = strPathFile
ThisWorkbook.Sheets("Form Analysis").Protect

' Inserts the procedure number and title into the score table if needed, regular form type only
Dim formType As String
formType = ThisWorkbook.Sheets("Import").Range("$G$1").Value

If formType = "Regular" Then
scoreTableTitle = ThisWorkbook.Sheets("Score Table").Range("$B$2")
potentialTitle = ThisWorkbook.Sheets("Form " & whichForm & " Data").Range("$C$4")
If potentialTitle = "" Then
potentialTitle = ThisWorkbook.Sheets("Form " & whichForm & " Data").Range("$A$4")
End If
If scoreTableTitle = "3.xxx Procedure Title" And potentialTitle <> "" Then
ThisWorkbook.Sheets("Score Table").Unprotect
ThisWorkbook.Sheets("Score Table").Range("$B$2").Value = potentialTitle
ThisWorkbook.Sheets("Score Table").Protect AllowFormattingRows:=True, AllowFormattingColumns:=True
End If
End If

' Populate the data (PER values, # of exceptions, etc)
Call PopAnalysis(whichForm)

' Populate the questions on "Form X"
Call PopForm(whichForm)

' Populate "Form X" with Ignore, Correct, and Incorrect (vendor only) responses
Call AutoSelect(whichForm)

' Build the formulas
Call BuildFormulas(whichForm)

' Prep the Form X worksheet for use
ThisWorkbook.Sheets("Form " & whichForm).Visible = True
ThisWorkbook.Sheets("Form " & whichForm).Unprotect
ThisWorkbook.Sheets("Form " & whichForm).Range("B4:B103").Rows.AutoFit
ThisWorkbook.Sheets("Form " & whichForm).Protect AllowFormattingRows:=True

' Disable the regular/vendor toggle button and the Import Multiple button
ThisWorkbook.Sheets("Import").OLEObjects("ToggleRegularVendor").Object.Enabled = False
ThisWorkbook.Sheets("Import").OLEObjects("ImportMultipleButton").Object.Enabled = False

' Disable the import button
ThisWorkbook.Sheets("Import").OLEObjects("ImportButton" & whichForm).Object.Enabled = False
Else
Application.DisplayAlerts = False
ThisWorkbook.Sheets("Form " & whichForm).Delete
Application.DisplayAlerts = True

If numQuestions > 100 Then
MsgBox "Your form has too many questions. There is a 100 question limit."
Else
MsgBox "This isn't a valid form. Only unmodified forms generated from the database should be imported."
End If
End If

' Make the Import workbook active, if for some reason it isn't already
ThisWorkbook.Sheets("Import").Activate

' Cleanup
Application.DisplayAlerts = False
ThisWorkbook.Sheets("Form " & whichForm & " Data").Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

[SamT]

wbSource is the Active Workbook. Does it have a Sheet "Score Table?"

Does ThisWorkbook have a Sheet "Score Table?"

Is "Score Table" really the exact name as it is on the Tab? No extra spaces?

Please, don't colorize the Code and do use the # Icon to place CODE Tags around your code. Use Line Labels or comments to show where the error occurs. See your previous posts for examples.

[cleteh]

Will do... yes that is the exact name.

[SamT]

wbSource is the Active Workbook. Does it have a Sheet "Score Table?"

Does ThisWorkbook have a Sheet "Score Table?"

[SamT]

BTW, after your next reply, I am moving this thread to the Excel Forum

[cleteh]

ActiveWorkbook does not but ThisWorkbook which is what I thought is used to refer to the workbook containing the macro has the sheet called Score Table

[cleteh]

Ok, thanks bad habit im usually in access

[SamT]

ThisWorkbook is Correct. Have you changed the Erroring code back to using "ThisWorkbook?" You don't need to repost the code. I will edit your previous posting to suit.

Dumb Question, but... Nothing on that sheet is Protected, is it?

[cleteh]

Actually that was great question, the sheet was protected. Thank you the code does work. This I know is a dumb question but where do I mark this solved?

[SamT]

:bigdance2

Use Thread Tools at the top of the Thread to mark Threads "Solved."