Object Required Error

[dgrimm]

Folks -

I am working on a project where I have a bunch of stacked data and I am separating it by looking for certain criteria. The template that I am using only has sheet 1 in it containing the data. I have 3 macros to create sheet2, separate the data and then pull the data that I want back to sheet2.

Create sheet2 is your basic create sheet macro (recorded macro)

Sub Create_Sheet()

Sheets.Add After:=Sheets(Sheets.Count)

Sheet1.Select


End Sub

Now the separate data and put headers on sheet2. I am getting the Object required error on the line Sheet2.select

Sub Get_Name()

Application.ScreenUpdating = False

Sheet2.Select

Dim InputRange(30000) As Variant
Dim PatRange(30000) As Variant
Dim RowNumber(30000) As Variant
Dim CheckInput(30000) As Boolean
Dim CheckInput2(30000) As Boolean
Dim CheckInput3(30000) As Boolean
Dim CheckInput4(30000) As Boolean
Dim CheckInput5(30000) As Boolean
Dim CheckInput6(30000) As Boolean
Dim Top As Double
Dim Bot As Double
Dim aRange As Range

Sheet2.Range("A1") = "Name"
Sheet2.Range("B1") = "Beg Row #"
Sheet2.Range("C1") = "End Row #"
Sheet2.Range("D1") = "Patient #"
Sheet2.Range("E1") = "Patient Billed Date"
Sheet2.Range("F1") = "Primary Billed Date"
Sheet2.Range("G1") = "Secondary Billed Date"
Sheet2.Range("H1") = "Balance"



For i = 1 To 30000
InputRange(i) = Sheet1.Range("Input")(i, 1).Value
RowNumber(i) = Sheet1.Range("Input")(i, 1).Row
If Right(InputRange(i), 1) = ")" And Left(InputRange(i), 7) <> "Primary" Then
CheckInput(i) = True
Else
CheckInput(i) = False
End If
Next i

k = 1
For i = 1 To 30000
If CheckInput(i) = True Then
Sheet2.Range("A1").Offset(k, 0) = InputRange(i)
Sheet2.Range("B1").Offset(k, 0) = RowNumber(i)
k = k + 1
End If
Next i


For j = 1 To 30000
RowNumber(j) = Sheet1.Range("Input")(j, 1).Row
If InputRange(j) = "Patient Unapplied Prepayment Total" Then
CheckInput2(j) = True
Else
CheckInput2(j) = False
End If
Next j

m = 1
For j = 1 To 30000
If CheckInput2(j) = True Then
Sheet2.Range("C1").Offset(m, 0) = RowNumber(j)
Sheet2.Range("D1").Offset(m, 0) = m
m = m + 1
End If
Next j

p = 1
For i = 1 To 30000

If CheckInput(i) = True Then
Top = Sheet2.Range("B" & p + 1).Value
Bot = Sheet2.Range("C" & p + 1).Value
Sheet1.Select
Rows(Top & ":" & Bot).Select
DataCarry = Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = Left(InputRange(i), 30)

Sheets(Left(InputRange(i), 30)).Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "Patient" & p

yy = WorksheetFunction.CountA(Range("Patient" & p))
Range("Patient" & p).Resize(yy, 8).Select
Selection.Name = "PatientL" & p


p = p + 1
End If
Next i



Sheet2.Select
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit
Columns("B:D").Select
Selection.EntireColumn.Hidden = True

Sheet1.Select

Range("A1").Select
Application.CutCopyMode = False

Application.ScreenUpdating = True


End Sub

I don't understand why I am getting this error and any help would be greatly appreciated.

Thank you

David

 

[JackDanIce]

What line is your error occuring on?

Can you explain what this part is doing:

For i = 1 To 30000
InputRange(i) = Sheet1.Range("Input")(i, 1).Value
RowNumber(i) = Sheet1.Range("Input")(i, 1).Row

What is Range("Input")? I'm assuming it's a named range, e.g. Ax:Ay and you are trying to fill arrays InputRange and RowNumber with values relating to this named range?

 

[dgrimm]

The error is occurring at sheet2.select right after application.screenupdating = false in the second sub.

The section that you are asking about goes through the data line by line up to line 30000 and is collecting part of the data that I am pulling. It is pulling a name and row number (for the beginning of the data).

 

[RoryA]

That would imply that you do not have a sheet whose code name (not tab caption) is Sheet2.

 

[RichardMGreen]

If your adding a sheet and then deleting it, it won't always be called sheet2. Open your VBA and check on the left for the actual sheet name.
You would need to check the name of the sheet you've just created before you select it.

::edit::

Norie - You beat me to it by a few minutes.

 

[dgrimm]

I thought of that (the sheet name thing). The macro is used only once each time it is opened and sheet 2 is deleted each time. So upon reopening it goes back to creating the sheet 2 code name (I believe is what it was called). But if that is problem I am willing to call the sheet a particular name to avoid this problem. Each time I open it the and create the sheet there are two sheets listed. Sheet1(Sheet1) and Sheet2(Sheet2), I know that if I try to run the create sheet macro again the Sheet3(Sheet3) will be created.

So I guess now I am asking would it be better to create a new sheet and name it a particular name???

Dave

 

[RoryA]

In this instance you would probably be better off using the tab name instead - you can control that since you are creating the sheet too. Or use a worksheet variable passed to the following procedures as an argument.

 

[taurean]

Certain part of the above code seems to be taken from Macro Recorder. Macro Recorder brings along some flotsam which you can clean up.

This part:

Sheet2.Select
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit

can be improved upon as:

Sheet2.Columns("A:H").EntireColumn.AutoFit

The code is readable as well as runs faster!

 

[JackDanIce]

As well as what rorya said, I still think your next error will be what I pointed out about your named range "Input"

I've changed the first macro to solve the name of the sheet problem and then part of the second, see if the error still occurs or if it's on a different line:

Sub Create_Sheet()

Sheets.Add After:=Sheets(Sheets.Count)
For i = 1 To Sheets.Count
    Sheets(i).Name = "Sheet" & i
Next i

End Sub

Sub Get_Name()

Application.ScreenUpdating = False



Dim InputRange(30000) As Variant
Dim PatRange(30000) As Variant
Dim RowNumber(30000) As Variant
Dim CheckInput(30000) As Boolean
Dim CheckInput2(30000) As Boolean
Dim CheckInput3(30000) As Boolean
Dim CheckInput4(30000) As Boolean
Dim CheckInput5(30000) As Boolean
Dim CheckInput6(30000) As Boolean
Dim Top As Double
Dim Bot As Double
Dim aRange As Range

With Sheets("Sheet2")
    .Select
    .Range("A1") = "Name"
    .Range("B1") = "Beg Row #"
    .Range("C1") = "End Row #"
    .Range("D1") = "Patient #"
    .Range("E1") = "Patient Billed Date"
    .Range("F1") = "Primary Billed Date"
    .Range("G1") = "Secondary Billed Date"
    .Range("H1") = "Balance"
End With

'then the rest of your stuff

 

[dgrimm]

Thank you all for your advice and help. I did the sheet name advice and it worked. So thank you....And the cleaning up code advice is very appreciated also.

David