1

Topic: Method Pastespecial from class Range falls with error Run-time error 91

Good afternoon!
Absolutely the beginner in vba-programming and in macroes excel.
I will describe a problem:
There is a report in oracle bi analytics. Besides display in bi there is a possibility to generate a.xltx-template on the basis of the displayed data in bi. As it should work:
1) At button click "to Generate the report" the script vba is launched.
2) the Script vba transfers the data xls where the macro which fills a template xltx on the basis of the transferred data is described.
At an attempt stage to fill xltx a file the error "Run-time error 91 Object variable or With block variable not set" falls out and specifies in a current piece of the code:.header.format (rangeData).Copy: .header.group (rangeData).PasteSpecial xlPasteFormats: .header.group (rangeData).PasteSpecial xlPasteColumnWidths
Prompt, what can be not so? Just in case I result the necessary pieces of the code:

Public Sub Report_Passive (Report_Param As String)
Call Create_Report (Report_Path, Report_Param, _
".xltx")
ranges.total.group (rangeDescription).Value = "Total amount" and Chr (10) and "including:"
Dim c As Long, col As Range
For Each col In ranges.header.group (rangeData).Columns
If col. Rows (1).Value <> "" Then
col. Rows (1).Value = format (col. Rows (1).Value, "dd.mm.yyyy") and
col. Rows (2).Value = all"
col. Rows (2).Offset (1).Value = "involvement": col. Rows (2).Offset (2).Value = "Debt"
End If
Next
End Sub
Private Sub Create_Report (Path As String, Report_Param As String, Template_Name As String)
' Determination of formats
Set wsFormats = Application. ThisWorkbook. ActiveSheet
With ranges
Set.header = New clsRanges:.header. SetFormats wsFormats. Range ("B4:B5"), wsFormats. Range ("D4:F5")
Set.total = New clsRanges:.total. SetFormats wsFormats. Range ("B7"), wsFormats. Range ("D7:F7")
Set.detail = New clsRanges:.detail. SetFormats wsFormats. Range ("B11"), wsFormats. Range ("D11:F11")
End With
Set ws = Application. Workbooks. Add (TemplateFolder AND Template_Name).ActiveSheet
ws. Activate
Application. ScreenUpdating = False
' Application. Calculation = xlCalculationManual
' Loading of the data
Call GetData (Path, Report_Param, ws. Range ("A7"))
' report Formatting
Call ParseData
Call FormatHeaders
Call FormatData
Call AdvanceConfidentiality
ws. Range ("A2").Value = ws. Range ("A2").Value and minYear and "-" and maxYear and
ws. Range ("A1").Select
Application. Calculation = xlCalculationAutomatic
Application. Calculate
Application. ScreenUpdating = True
Application. StatusBar = ""
End Sub
Private Sub FormatData ()
' Formatting
With ranges
' Removal of superfluous lines
ws. Range ("A7:A9").EntireRow. Delete
' ws. Range (.detail.group (rangeDescription).Areas (.detail.group (rangeDescription).Areas. Count - 1).detail.group (rangeDescription).Areas (.detail.group (rangeDescription).Areas. Count)).EntireRow. Delete
' Title
ws. Range ("A2").Resize (ws. Range ("A1").SpecialCells (xlCellTypeLastCell).Column).Merge
ws. Range ("A3").Resize (ws. Range ("A1").SpecialCells (xlCellTypeLastCell).Column).Merge
ws. Range ("A4").Resize (ws. Range ("A1").SpecialCells (xlCellTypeLastCell).Column).Merge
.header.group (rangeDescription).ColumnWidth =.header.format (rangeDescription).ColumnWidth
.header.format (rangeData).Copy:.header.group (rangeData).PasteSpecial xlPasteFormats:.header.group (rangeData).PasteSpecial xlPasteColumnWidths
' the Data
.detail. ApplyFormatsToGroup rangeDescription ':.detail. ApplyFormatsToGroup rangeData
.detail.format (rangeData).Copy:.detail.group (rangeData).PasteSpecial xlPasteFormats
.detail.group (rangeDescription).Borders (xlEdgeBottom).Weight = xlMedium:.detail.group (rangeData).Borders (xlEdgeBottom).Weight = xlMedium
.total. ApplyFormatsToGroup rangeDescription ':.total. ApplyFormatsToGroup rangeData
.total.format (rangeData).Copy:.total.group (rangeData).PasteSpecial xlPasteFormats
' the Remark
ws. Shapes ("Description").Top = ws.cells (.detail.group (rangeDescription).Areas (.detail.group (rangeDescription).Areas. Count).row +.detail.group (rangeDescription).Areas (.detail.group (rangeDescription).Areas. Count).Count + 2, 1).Top
ws.cells (.detail.group (rangeDescription).cells (.detail.group (rangeDescription).Rows. Count).row + 1, 1).EntireRow. Delete
' In addition
' With ws. Range ("D3")
'.Copy ws.cells (.row, ranges.detail.group (rangeData).Areas (1).Columns. Count + 1)
'.Value = ""
' End With
ws.cells. Font. Name = "Times New Roman"
End With
End Sub

the Moderator: we Learn to use tags of design of the code - FAQ

2

Re: Method Pastespecial from class Range falls with error Run-time error 91

zorlo;
.header.group - That it should mean your way?  header.format?
I to what - method Group concern the pivot table, and that such header object Range at all does not know.
From here a question: what for type at object ranges, to properties and which methods you there address?

3

Re: Method Pastespecial from class Range falls with error Run-time error 91

Private Type udtRanges
header As clsRanges
total As clsRanges
detail As clsRanges
End Type
Private ranges As udtRanges

4

Re: Method Pastespecial from class Range falls with error Run-time error 91

zorlo;
Well and how after that something to prompt? Without clsRanges unit there is nothing to tell, since final formation of object happens there.

5

Re: Method Pastespecial from class Range falls with error Run-time error 91

Objects ranges and ws are defined in the code beginning:

Private Type udtRanges
header As clsRanges
total As clsRanges
detail As clsRanges
End Type
Private ranges As udtRanges, minYear As Integer, maxYear As Integer
Private ws As Worksheet, wsFormats As Worksheet

It is everything that is about this object...
There are two more pieces of the code, but do not think that they will be involved:

Private Sub ParseData ()
Dim r As Range, row As Integer, col As Integer, level As Integer
Dim col_step As Integer, tmpRanges As clsRanges
col_step = ranges.header.format (rangeData).Columns. Count
' Removal of superfluous columns
ws. Range ("B1").EntireColumn. Delete xlShiftToLeft
row = 9
col = 3
Do Until col> = ws. Range ("A1").SpecialCells (xlCellTypeLastCell).Column
' Removal of superfluous cells
ws.cells (row - 1, col).Delete xlShiftToLeft
ws.cells (row, col).Delete xlShiftToLeft
DoEvents
col = col + 1
Loop
row = 10
Do Until row> ws. Range ("A1").SpecialCells (xlCellTypeLastCell).row
' Removal of superfluous cells
If ws.cells (row, 1).Value = "including" Then ws.cells (row, 1).EntireRow. Delete xlShiftUp
col = 2
Do Until col> = ws. Range ("A1").SpecialCells (xlCellTypeLastCell).Column
ws.cells (row, col).Delete xlShiftToLeft
col = col + 1
Loop
DoEvents
row = row + 1
Loop
ws. UsedRange. Select
row = 10
Do Until row> ws. Range ("A1").SpecialCells (xlCellTypeLastCell).row
' Handling
Set tmpRanges = Nothing
Set r = ws.cells (row, 1)
Select Case GetLevelName (r. Value)
Case "Total": level = 0: r. IndentLevel = level: Set tmpRanges = ranges.total
Case "Details": r. IndentLevel = level + 1: Set tmpRanges = ranges.detail
End Select
If Not tmpRanges Is Nothing Then
tmpRanges. AddRangeToGroup rangeDescription, r
For col = 1 To ws. Range ("A1").SpecialCells (xlCellTypeLastCell).Column - 1 Step col_step
tmpRanges. AddRangeToGroup rangeData, r. Offset (col).Resize (col_step)
Next
End If
Application. StatusBar = "handling:" and FormatPercent (row / ws. Range ("A1").SpecialCells (xlCellTypeLastCell).row, 2)
DoEvents
row = row + 1
Loop
End Sub
Private Sub FormatHeaders ()
Dim col As Integer, d As Date
Dim col_step As Integer
col_step = ranges.header.format (rangeData).Columns. Count
ranges.header. AddRangeToGroup rangeDescription, ws. Range ("A5:A6")
For col = 2 To ws. Range ("A1").SpecialCells (xlCellTypeLastCell).Column Step col_step
If ws.cells (8, col).Value Like "#### year # quarter *" Then
d = DateSerial (Left (ws.cells (8, col).Value, 4), Mid (ws.cells (8, col).Value, 10, 1) * 3, 1)
If d = #3/1/2015# Then d = DateAdd ("m",-2, d) Else d = DateAdd ("m", 1, d)
ws.cells (5, col).Value = d
' ws.cells (5, col).Value = format (d, "dd.mm.yyyy") and
' ws.cells (6, col).Value = All": ws.cells (6, col + 1).Value ="Involvement": ws.cells (6, col + 2).Value ="Debts"
ranges.header. AddRangeToGroup rangeData, ws. Range (ws.cells (5, col), ws.cells (6, col + 2))
If minYear = 0 Then minYear = Year (d)
If Year (d)> maxYear Then maxYear = Year (d)
End If
Next
End Sub

6

Re: Method Pastespecial from class Range falls with error Run-time error 91

It is possible by mail for you to send a class? It not the small

7

Re: Method Pastespecial from class Range falls with error Run-time error 91

zorlo wrote:

It not small

publish in a spoiler

8

Re: Method Pastespecial from class Range falls with error Run-time error 91

Class
[spoiler]

Option Explicit
Public Enum enmType
rangeDescription = 1
rangeData = 2
End Enum
Private rangeGroupDescription As Range, rangeGroupData As Range
Private rangeFormatDescription As Range, rangeFormatData As Range
Public Property Get group (rangeType As enmType) As Range
Select Case rangeType
Case rangeDescription: Set group = rangeGroupDescription
Case rangeData: Set group = rangeGroupData
End Select
End Property
Public Property Set group (rangeType As enmType, ByRef r As Range)
Select Case rangeType
Case rangeDescription: Set rangeGroupDescription = r
Case rangeData: Set rangeGroupData = r
End Select
End Property
Public Property Get format (rangeType As enmType) As Range
Select Case rangeType
Case rangeDescription: Set format = rangeFormatDescription
Case rangeData: Set format = rangeFormatData
End Select
End Property
Public Property Set format (rangeType As enmType, ByRef r As Range)
Select Case rangeType
Case rangeDescription: Set rangeFormatDescription = r
Case rangeData: Set rangeFormatData = r
End Select
End Property
Public Sub AddRangeToGroup (rangeType As enmType, ByRef r As Range)
Select Case rangeType
Case rangeDescription
If rangeGroupDescription Is Nothing Then Set rangeGroupDescription = r Else Set rangeGroupDescription = Union (rangeGroupDescription, r)
Case rangeData
If rangeGroupData Is Nothing Then Set rangeGroupData = r Else Set rangeGroupData = Union (rangeGroupData, r)
End Select
End Sub
Public Sub SetFormats (ByRef rangeDescriptionFormats As Range, ByRef rangeDataFormats As Range)
Set format (rangeDescription) = rangeDescriptionFormats
Set format (rangeData) = rangeDataFormats
End Sub
Public Sub ApplyFormatsToGroup (rangeType As enmType)
Select Case rangeType
Case rangeDescription: Call ApplyFormat (rangeGroupDescription, rangeFormatDescription)
Case rangeData: Call ApplyFormat (rangeGroupData, rangeFormatData)
End Select
End Sub
Private Sub ApplyFormat (ByRef r As Range, ByRef rFormat As Range)
On Error Resume Next
If Not r Is Nothing Then
' rFormat. Copy: r. PasteSpecial xlPasteFormats, xlNone, False, False
With rFormat
Dim xlIndex As XlBordersIndex
For xlIndex = xlEdgeLeft To xlInsideHorizontal
With.Borders (xlIndex)
r. Borders (xlIndex).Color =.Color
r. Borders (xlIndex).ColorIndex =.ColorIndex
r. Borders (xlIndex).LineStyle =.LineStyle
r. Borders (xlIndex).TintAndShade =.TintAndShade
r. Borders (xlIndex).Weight =.Weight
If xlIndex = xlInsideHorizontal Then r. Borders (xlIndex).Weight = xlHairline Else r. Borders (xlIndex).Weight =.Weight
End With
Next
With.Font
r. Font. Background =.Background
r. Font. Bold =.Bold
r. Font. Color =.Color
r. Font. ColorIndex =.ColorIndex
r. Font. FontStyle =.FontStyle
r. Font. Italic =.Italic
r. Font. Name =.Name
r. Font. Size =.Size
r. Font. Strikethrough =.Strikethrough
r. Font. Subscript =.Subscript
r. Font. Superscript =.Superscript
r. Font. ThemeColor =.ThemeColor
r. Font. ThemeFont =.ThemeFont
r. Font. TintAndShade =.TintAndShade
r. Font. Underline =.Underline
End With
r. FormulaHidden =.FormulaHidden
r. HorizontalAlignment =.HorizontalAlignment
r. VerticalAlignment =.VerticalAlignment
With.Interior
r. Interior. Color =.Color
r. Interior. ColorIndex =.ColorIndex
r. Interior. Pattern =.Pattern
r. Interior. PatternColor =.PatternColor
r. Interior. PatternColorIndex =.PatternColorIndex
r. Interior. PatternTintAndShade =.PatternTintAndShade
r. Interior. ThemeColor =.ThemeColor
r. Interior. TintAndShade =.TintAndShade
End With
r. MergeCells =.MergeCells
r. NumberFormat =.NumberFormat
r. NumberFormatLocal =.NumberFormatLocal
r. Orientation =.Orientation
r. ShrinkToFit =.ShrinkToFit
r. VerticalAlignment =.VerticalAlignment
r. WrapText =.WrapText
r. FormatConditions. Delete
Dim fc As FormatCondition, fc_r As FormatCondition
For Each fc In.FormatConditions
Set fc_r = r. FormatConditions. Add (fc. Type, fc. Operator, fc. Formula1, fc. Formula2)
fc_r. NumberFormat = fc. NumberFormat
fc_r. StopIfTrue = False
Next
End With
End If
End Sub

[/spoiler]
The moderator: the Publication in a spoiler does not cancel necessity SRC. Corrected....

9

Re: Method Pastespecial from class Range falls with error Run-time error 91

If there are any thoughts, ask, please, prompt, in what direction to move further?
In advance many thanks!!!

10

Re: Method Pastespecial from class Range falls with error Run-time error 91

zorlo wrote:

in what direction to move further?

In it: Catching of errors and debugging of code VBA
Because to understand in kilometer tangled (it is not clear for what, by the way) the codes not such the fast task.
Most simple that it is possible to assume by mistake, it:
The Variant 1.

.header.group (rangeData) 

returns Nothing. Such can be, when most rangeGroupe value simply was not assigned by this. And that is why - on this question only debugging on a real file can prompt.
The Variant 2.

.header.group (rangeData) 

contains determination for untied ranges. And custom insert cannot be fulfilled in untied ranges. Try to write down so:

.header.format (rangeData).Copy:.header.group (rangeData).Cells (1).PasteSpecial xlPasteFormats:.header.group (rangeData).Cells (1).PasteSpecial xlPasteColumnWidths

If works - means in it and business.