Run-time error 5 when creating a pivot table

[midoop]

Hello,
I am having problems with my VBA code. First, I can't get the selected cells to convert to number format (font in red). Second, I get run-time error 5 Invalid procedure call or argument when trying to create a pivot table (font in green). Any and all help is greatly appreciated!

Sub MM27_Profile()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim ws6 As Worksheet
Dim x As Integer
Dim ws2rows As Integer
Dim myrows As Integer
Dim i As Integer
Dim sFileName As String
Application.ScreenUpdating = False
Set ws1 = Worksheets("Output Filter Applied")
Set ws2 = Worksheets("Disease Assessment 01")
Set ws3 = Worksheets("Disease Assessment")
Set ws4 = Worksheets("Hematology 01")
Set ws5 = Worksheets("Chemistry 01")
Application.DisplayAlerts = False
ws1.Delete
Application.DisplayAlerts = True
Set ws6 = ActiveWorkbook.Worksheets.Add(After:=ws5)
ws6.Name = "Patient Profile"
    
ws2.Select
Range("A1:F1").Select
Range("A1:F1").Cut Destination:=ws6.Range("A1:F1")
Range("A2:D2").Select
Range("A2:D2").Cut Destination:=ws6.Range("G1:J1")
Rows("1:4").Select
Selection.Delete Shift:=xlUp
[COLOR=#ff0000]myrows = WorksheetFunction.CountA(ws2.Range("A:A"))
For i = 2 To myrows
ws2.Cells(i, 4).NumberFormat = "0"
Next i[/COLOR]
[COLOR=#008000]ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Disease Assessment 01", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="'Patient Profile'!R3C1", TableName:="Immunoglobulins", DefaultVersion _
        :=xlPivotTableVersion14
[/COLOR]        
    ws6.Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Immunoglobulins").PivotFields("LAB")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    With ActiveSheet.PivotTables("Immunoglobulins").PivotFields("Visit Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    ActiveSheet.PivotTables("Immunoglobulins").AddDataField ActiveSheet.PivotTables( _
    "Immunoglobulins").PivotFields("Value"), "Sum of Value", xlSum
    ActiveSheet.PivotTables("Immunoglobulins").ColumnGrand = False
    ActiveSheet.PivotTables("Immunoglobulins").RowGrand = False
    Rows("3:3").Select
    Selection.EntireRow.Hidden = True
End Sub

 

[Andrew Poulsom]

What happens with your number format code? Do you get an error? Note that formatting text as a number has no effect.

For the pivot table code what is "Disease Assessment 01"? SourceData expects a Range object.

 

[midoop]

I get no error message with the number format code, but at the same time, the selection is not converted to number format.
"Disease Assessment 01" is a worksheet. The issue I have with specifying an exact range within that worksheet is that the number of rows is dynamic (e.g. data for one patient may have 10 rows, but data for another patient may have 15 rows, so on and so forth).

 

[Andrew Poulsom]

If you look at the cells' number format in the user interface what is it?

For the pivot table try:

SourceData:=Worksheets("Disease Assessment 01").Range("A1").CurrentRegion

 

[midoop]

It says general, but there is also a little caution/error icon beside each cell (and the cells have a green triangle in the top left corner) that states that the number in this cell is formatted as text.

I will try the above code in a moment. However, I have run into another problem (once again, because the rows of nothing I am working on are fixed).

I am trying to post some data below some data (the data which is already there does not have a fixed number of rows), and using the below code, I get an error stating that the destination range is not the same size as the selected range (Problem area in red).

Sub MM27_Profile()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim ws6 As Worksheet
Dim myrows As Integer
Dim i As Integer
Dim sFileName As String
Application.ScreenUpdating = False
Set ws1 = Worksheets("Output Filter Applied")
Set ws2 = Worksheets("Disease Assessment 01")
Set ws3 = Worksheets("Disease Assessment")
Set ws4 = Worksheets("Hematology 01")
Set ws5 = Worksheets("Chemistry 01")
Application.DisplayAlerts = False
ws1.Delete
Application.DisplayAlerts = True
Set ws6 = ActiveWorkbook.Worksheets.Add(After:=ws5)
ws6.Name = "Patient Profile"
    
ws2.Select
Range("A1:F1").Select
Range("A1:F1").Cut Destination:=ws6.Range("A1:F1")
Range("A2:D2").Select
Range("A2:D2").Cut Destination:=ws6.Range("G1:J1")
Rows("1:4").Select
Selection.Delete Shift:=xlUp
myrows = WorksheetFunction.CountA(ws2.Range("A:A"))
ws2.Range("A1:D" & myrows).Select
ws2.Range("A1:D" & myrows).Cut Destination:=ws6.Range("A3:D" & myrows)
ws3.Select
Rows("1:3").Select
Selection.Delete Shift:=xlUp
herrows = WorksheetFunction.CountA(ws3.Range("A:A"))
ws3.Range("A1:K" & herrows).Select
[COLOR=#ff0000]ws3.Range("A1:K" & herrows).Cut Destination:=ws6.Range("A" & myrows + 1, "K" & herrows)[/COLOR]
End Sub

 

[midoop]

Using the code you suggested for the pivot table, now I am getting Run-time error '1004': The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a Pivottable field, you must type a new name for the field.

 

[Andrew Poulsom]

Formatting only changes how the data appears, not the actual data. You can use Data|Text To Columns to convert the text entries to real numbers.

For the pivot table make sure that there are headings in every cell in row 1 of A1's current region.

 

[midoop]

Both of your suggestions worked! Thank you!

However, I have run into another problem (once again, because the rows of nothing I am working on are fixed).

I am trying to post some data below some data (the data which is already there does not have a fixed number of rows), and using the below code, I get an error stating that the destination range is not the same size as the selected range (Problem area in red).

Sub MM27_Profile()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim ws6 As Worksheet
Dim myrows As Integer
Dim i As Integer
Dim sFileName As String
Application.ScreenUpdating = False
Set ws1 = Worksheets("Output Filter Applied")
Set ws2 = Worksheets("Disease Assessment 01")
Set ws3 = Worksheets("Disease Assessment")
Set ws4 = Worksheets("Hematology 01")
Set ws5 = Worksheets("Chemistry 01")
Application.DisplayAlerts = False
ws1.Delete
Application.DisplayAlerts = True
Set ws6 = ActiveWorkbook.Worksheets.Add(After:=ws5)
ws6.Name = "Patient Profile"

ws2.Select
Range("A1:F1").Select
Range("A1:F1").Cut Destination:=ws6.Range("A1:F1")
Range("A2:D2").Select
Range("A2:D2").Cut Destination:=ws6.Range("G1:J1")
Rows("1:4").Select
Selection.Delete Shift:=xlUp
myrows = WorksheetFunction.CountA(ws2.Range("A:A"))
ws2.Range("A1:D" & myrows).Select
ws2.Range("A1:D" & myrows).Cut Destination:=ws6.Range("A3:D" & myrows)
ws3.Select
Rows("1:3").Select
Selection.Delete Shift:=xlUp
herrows = WorksheetFunction.CountA(ws3.Range("A:A"))
ws3.Range("A1:K" & herrows).Select
[COLOR=#ff0000]ws3.Range("A1:K" & herrows).Cut Destination:=ws6.Range("A" & myrows + 1, "K" & herrows)[/COLOR]
End Sub

 

[Andrew Poulsom]

Try:

ws3.Range("A1:K" & herrows).Cut Destination:=ws6.Range("A" & ws6.Rows.Count).End(xlUp).Offset(1)

 

[midoop]

It worked!!! Thank you so very much!!! And thank you for walking me through the process instead of just writing the code and handing it to me!