Changing the subtitles stops VBA working and form printing

[GarethG]

Hi, I already have a sheet set up with an embedded form that pulls data from the spreadsheet and prints using a button programmed to print each numbered entry. A second button also saves a pdf copy to the system. However I need to change the subtitles in the spreadsheet and form as they have been updated but I get the "Run Time Error 1004" "Method Range of Object" Global Failed message when trying to print. If I change the title back to its original it prints no problem HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP

 

[steve the fish]

Hi. Can you post the line that it debugs on?

 

[GarethG]

Hi Steve,

Currently the form is set to print and save with GC week number as a column title and i want to change it to "week number" this is the line it highlights

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Range(RangeName(sRngName)).Value = wsData.Cells(r, c)[/FONT]

 

[steve the fish]

Ok that hasnt been as helpful as it could have been. Can you post all your code?

 

[GarethG]

Function RangeName(sName As String) As String
RangeName = Application.Substitute(sName, " ", "_")
End Function
Sub Printselected()
'set up your merge form by naming the merge fields _
with the same name as the data fields you are importing.



Dim wsForm As Worksheet, wsData As Worksheet
Dim sRngName As String, r As Long, c As Integer
Dim selected As String
selected = InputBox("Enter Ref number", "Print Single Sheet")
Set wsForm = Worksheets("Form") 'change to your sheet name of the form
Set wsData = Worksheets("2019") 'change to your sheet name with Data in
With wsData.Cells(1, 1).CurrentRegion
For r = 2 To .Rows.Count

If Not wsData.Cells(r, 1).EntireRow.Hidden Then
For c = 1 To .Columns.Count
sRngName = wsData.Cells(1, c).Value
Range(RangeName(sRngName)).Value = wsData.Cells(r, c)
Next
If wsForm.Cells(6, 8) = selected Then
selected = 0
wsForm.PrintOut
Exit Sub
End If

End If
Next
End With

End Sub
Sub PrintMySelectedRange()
'set up your merge form by naming the merge fields _
with the same name as the data fields you are importing.

Dim wsForm As Worksheet, wsData As Worksheet
Dim sRngName As String, r As Long, c As Integer
Dim selected As String
selected = InputBox("Enter Ref number", "Print Single Sheet")
Set wsForm = Worksheets("Form") 'change to your sheet name of the form
Set wsData = Worksheets("2019") 'change to your sheet name with Data in
With wsData.Cells(1, 1).CurrentRegion
'For r = 2 To Worksheets("2012").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
For r = 2 To wsData.Rows.Count
If Not wsData.Cells(r, 1).EntireRow.Hidden And wsData.Cells(r, 1).Value > 0 Then
For c = 1 To .Columns.Count
sRngName = wsData.Cells(1, c).Value
Range(RangeName(sRngName)).Value = wsData.Cells(r, c)
Next
If wsForm.Cells(6, 8) = selected Then
selected = 0
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:="W:\Kpi\Technical Office\Hold Log\Hold Notices\2019" & Range("H6").Value, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Exit Sub
End With

End If

End If
Next
End With



' MsgBox ("selected range")
' MsgBox (Worksheets("2012").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count)


End Sub

 

[steve the fish]

At the point it fails what is c? Hover over it. Then you need to check that the cell in sheet 2019 (it will be in the top row column number whatever c is) has a value that when spaces are replaced by underscores is a valid named range.

 

[GarethG]

c=4 when I hover over it. in the sheet 2019 column C is titled time. The error occurs when I change column D from GC Week Number to just Week Number as soon as I change it back it works perfectly? Sorry but I'm still very new to all the code stuff I inherited the spreadsheets and I'm attempting to get them working

 

[GarethG]

when its working and i hover over it c=0 its hurting my brain cells

 

[steve the fish]

Look for a named range called "GC_Week_Number". Change its name to "Week_Number" then change the header.

 

[GarethG]

thats just it there isnt a named range with that title within it and the form doesnt even have that column detail in it so not sure why its throwing everything out when i press the button to print or save it gives the same error message