Hello excel masters!
I've written some module code for a button that when clicked, the contents of particular cells are transferred to another workbook, however it seems that when the contents of the original cells are not numbers the code doesn't work. One of the cells I'd like to send from contains a non-numerical data. I've provided the code below
Sub Complete_Click()
Dim itemDate As String
Dim itemQXRB As Single
Dim itemQXRB1 As Single
Dim itemQXRB2 As Single
Dim itemQXRB3 As Single
Dim itemQXRT As Single
Dim itemQXRT1 As Single
Dim itemQXRT2 As Single
Dim itemQXRT3 As Single
Dim itemPA As Single
Dim itemPA1 As Single
Dim itemPA2 As Single
Dim itemPA3 As Single
Dim itemSCH As Single
Dim itemSCH1 As Single
Dim itemSCH2 As Single
Dim itemSCH3 As Single
Dim itemCPH As Single
Dim itemCPH1 As Single
Dim itemCPH2 As Single
Dim itemCPH3 As Single
Dim itemWMI As Single
Dim itemWMI1 As Single
Dim itemWMI2 As Single
Dim itemWMI3 As Single
Dim itemSCR As Single
Dim itemSCR1 As Single
Dim itemSCR2 As Single
Dim itemSCR3 As Single
Dim itemTVH As Single
Dim itemTVH1 As Single
Dim itemTVH2 As Single
Dim itemTVH3 As Single
Dim itemGCUH As Single
Dim itemGCUH1 As Single
Dim itemGCUH2 As Single
Dim itemGCUH3 As Single
Dim itemLH As Single
Dim itemLH1 As Single
Dim itemLH2 As Single
Dim itemLH3 As Single
Dim itemRUN1 As Single
Dim itemRUN2 As Single
Dim itemRUN3 As Single
Dim myData As Workbook
Worksheets("Production Summary").Select
itemDate = Range("K2")
itemRUN1 = Range("P9")
itemRUN2 = Range("P10")
itemRUN3 = Range("P11")
Worksheets("QXR - Bris & Tville").Select
itemQXRB = Range("D6")
itemQXRB1 = Range("D7")
itemQXRB2 = Range("D8")
itemQXRB3 = Range("D9")
itemQXRT = Range("L6")
itemQXRT1 = Range("L7")
itemQXRT2 = Range("L8")
itemQXRT3 = Range("L9")
Worksheets("PA & SCH").Select
itemPA = Range("D6")
itemPA1 = Range("D7")
itemPA2 = Range("D8")
itemPA3 = Range("D9")
itemSCH = Range("L6")
itemSCH1 = Range("L7")
itemSCH2 = Range("L8")
itemSCH3 = Range("L9")
Worksheets("CPH & WMI").Select
itemCPH = Range("D6")
itemCPH1 = Range("D7")
itemCPH2 = Range("D8")
itemCPH3 = Range("D9")
itemWMI = Range("L6")
itemWMI1 = Range("L7")
itemWMI2 = Range("L8")
itemWMI3 = Range("L9")
Worksheets("SCR & Tville Hospital").Select
itemSCR = Range("D6")
itemSCR1 = Range("D7")
itemSCR2 = Range("D8")
itemSCR3 = Range("D9")
itemTVH = Range("L6")
itemTVH1 = Range("L7")
itemTVH2 = Range("L8")
itemTVH3 = Range("L9")
Worksheets("GCU & Lismore Hospitals").Select
itemGCUH = Range("D6")
itemGCUH1 = Range("D7")
itemGCUH2 = Range("D8")
itemGCUH3 = Range("D9")
itemLH = Range("L6")
itemLH1 = Range("L7")
itemLH2 = Range("L8")
itemLH3 = Range("L9")
Set myData = Workbooks.Open("N:\CAPL - NEW SERVER\15. Staff Folders\Josh Two\Sales trending sheet.xlsx")
Worksheets("Sales Tracker").Select
Worksheets("Sales Tracker").Range("A6").Select
RowCount = Worksheets("Sales Tracker").Range("A6").CurrentRegion.Rows.Count
With Worksheets("Sales Tracker").Range("A6")
.Offset(RowCount, 0) = itemDate
.Offset(RowCount + 1, 0) = itemDate
.Offset(RowCount + 2, 0) = itemDate
.Offset(RowCount + 3, 0) = itemDate
.Offset(RowCount, 1) = itemRUN1
.Offset(RowCount + 1, 1) = itemRUN2
.Offset(RowCount + 2, 1) = itemRUN3
.Offset(RowCount + 3, 1) = "Summary"
.Offset(RowCount + 3, 2) = itemQXRB
.Offset(RowCount, 2) = itemQXRB1
.Offset(RowCount + 1, 2) = itemQXRB2
.Offset(RowCount + 2, 2) = itemQXRB3
.Offset(RowCount, 3) = itemQXRT1
.Offset(RowCount + 1, 3) = itemQXRT2
.Offset(RowCount + 2, 3) = itemQXRT3
.Offset(RowCount + 3, 3) = itemQXRT
.Offset(RowCount + 3, 4) = itemPA
.Offset(RowCount, 4) = itemPA1
.Offset(RowCount + 1, 4) = itemPA2
.Offset(RowCount + 2, 4) = itemPA3
.Offset(RowCount + 3, 5) = itemSCH
.Offset(RowCount, 5) = itemSCH1
.Offset(RowCount + 1, 5) = itemSCH2
.Offset(RowCount + 2, 5) = itemSCH3
.Offset(RowCount + 3, 6) = itemCPH
.Offset(RowCount, 6) = itemCPH1
.Offset(RowCount + 1, 6) = itemCPH2
.Offset(RowCount + 2, 6) = itemCPH3
.Offset(RowCount + 3, 7) = itemWMI
.Offset(RowCount, 7) = itemWMI1
.Offset(RowCount + 1, 7) = itemWMI2
.Offset(RowCount + 2, 7) = itemWMI3
.Offset(RowCount + 3, 8) = itemSCR
.Offset(RowCount, 8) = itemSCR1
.Offset(RowCount + 1, 8) = itemSCR2
.Offset(RowCount + 2, 8) = itemSCR3
.Offset(RowCount + 3, 9) = itemTVH
.Offset(RowCount, 9) = itemTVH1
.Offset(RowCount + 1, 9) = itemTVH2
.Offset(RowCount + 2, 9) = itemTVH3
.Offset(RowCount + 3, 10) = itemGCUH
.Offset(RowCount, 10) = itemGCUH1
.Offset(RowCount + 1, 10) = itemGCUH2
.Offset(RowCount + 2, 10) = itemGCUH3
.Offset(RowCount + 3, 11) = itemLH
.Offset(RowCount, 11) = itemLH1
.Offset(RowCount + 1, 11) = itemLH2
.Offset(RowCount + 2, 11) = itemLH3
End With
myData.Save
End Sub
Is there any reason why it wouldn't like non-numerical inputs?
Thank you!
I've written some module code for a button that when clicked, the contents of particular cells are transferred to another workbook, however it seems that when the contents of the original cells are not numbers the code doesn't work. One of the cells I'd like to send from contains a non-numerical data. I've provided the code below
Sub Complete_Click()
Dim itemDate As String
Dim itemQXRB As Single
Dim itemQXRB1 As Single
Dim itemQXRB2 As Single
Dim itemQXRB3 As Single
Dim itemQXRT As Single
Dim itemQXRT1 As Single
Dim itemQXRT2 As Single
Dim itemQXRT3 As Single
Dim itemPA As Single
Dim itemPA1 As Single
Dim itemPA2 As Single
Dim itemPA3 As Single
Dim itemSCH As Single
Dim itemSCH1 As Single
Dim itemSCH2 As Single
Dim itemSCH3 As Single
Dim itemCPH As Single
Dim itemCPH1 As Single
Dim itemCPH2 As Single
Dim itemCPH3 As Single
Dim itemWMI As Single
Dim itemWMI1 As Single
Dim itemWMI2 As Single
Dim itemWMI3 As Single
Dim itemSCR As Single
Dim itemSCR1 As Single
Dim itemSCR2 As Single
Dim itemSCR3 As Single
Dim itemTVH As Single
Dim itemTVH1 As Single
Dim itemTVH2 As Single
Dim itemTVH3 As Single
Dim itemGCUH As Single
Dim itemGCUH1 As Single
Dim itemGCUH2 As Single
Dim itemGCUH3 As Single
Dim itemLH As Single
Dim itemLH1 As Single
Dim itemLH2 As Single
Dim itemLH3 As Single
Dim itemRUN1 As Single
Dim itemRUN2 As Single
Dim itemRUN3 As Single
Dim myData As Workbook
Worksheets("Production Summary").Select
itemDate = Range("K2")
itemRUN1 = Range("P9")
itemRUN2 = Range("P10")
itemRUN3 = Range("P11")
Worksheets("QXR - Bris & Tville").Select
itemQXRB = Range("D6")
itemQXRB1 = Range("D7")
itemQXRB2 = Range("D8")
itemQXRB3 = Range("D9")
itemQXRT = Range("L6")
itemQXRT1 = Range("L7")
itemQXRT2 = Range("L8")
itemQXRT3 = Range("L9")
Worksheets("PA & SCH").Select
itemPA = Range("D6")
itemPA1 = Range("D7")
itemPA2 = Range("D8")
itemPA3 = Range("D9")
itemSCH = Range("L6")
itemSCH1 = Range("L7")
itemSCH2 = Range("L8")
itemSCH3 = Range("L9")
Worksheets("CPH & WMI").Select
itemCPH = Range("D6")
itemCPH1 = Range("D7")
itemCPH2 = Range("D8")
itemCPH3 = Range("D9")
itemWMI = Range("L6")
itemWMI1 = Range("L7")
itemWMI2 = Range("L8")
itemWMI3 = Range("L9")
Worksheets("SCR & Tville Hospital").Select
itemSCR = Range("D6")
itemSCR1 = Range("D7")
itemSCR2 = Range("D8")
itemSCR3 = Range("D9")
itemTVH = Range("L6")
itemTVH1 = Range("L7")
itemTVH2 = Range("L8")
itemTVH3 = Range("L9")
Worksheets("GCU & Lismore Hospitals").Select
itemGCUH = Range("D6")
itemGCUH1 = Range("D7")
itemGCUH2 = Range("D8")
itemGCUH3 = Range("D9")
itemLH = Range("L6")
itemLH1 = Range("L7")
itemLH2 = Range("L8")
itemLH3 = Range("L9")
Set myData = Workbooks.Open("N:\CAPL - NEW SERVER\15. Staff Folders\Josh Two\Sales trending sheet.xlsx")
Worksheets("Sales Tracker").Select
Worksheets("Sales Tracker").Range("A6").Select
RowCount = Worksheets("Sales Tracker").Range("A6").CurrentRegion.Rows.Count
With Worksheets("Sales Tracker").Range("A6")
.Offset(RowCount, 0) = itemDate
.Offset(RowCount + 1, 0) = itemDate
.Offset(RowCount + 2, 0) = itemDate
.Offset(RowCount + 3, 0) = itemDate
.Offset(RowCount, 1) = itemRUN1
.Offset(RowCount + 1, 1) = itemRUN2
.Offset(RowCount + 2, 1) = itemRUN3
.Offset(RowCount + 3, 1) = "Summary"
.Offset(RowCount + 3, 2) = itemQXRB
.Offset(RowCount, 2) = itemQXRB1
.Offset(RowCount + 1, 2) = itemQXRB2
.Offset(RowCount + 2, 2) = itemQXRB3
.Offset(RowCount, 3) = itemQXRT1
.Offset(RowCount + 1, 3) = itemQXRT2
.Offset(RowCount + 2, 3) = itemQXRT3
.Offset(RowCount + 3, 3) = itemQXRT
.Offset(RowCount + 3, 4) = itemPA
.Offset(RowCount, 4) = itemPA1
.Offset(RowCount + 1, 4) = itemPA2
.Offset(RowCount + 2, 4) = itemPA3
.Offset(RowCount + 3, 5) = itemSCH
.Offset(RowCount, 5) = itemSCH1
.Offset(RowCount + 1, 5) = itemSCH2
.Offset(RowCount + 2, 5) = itemSCH3
.Offset(RowCount + 3, 6) = itemCPH
.Offset(RowCount, 6) = itemCPH1
.Offset(RowCount + 1, 6) = itemCPH2
.Offset(RowCount + 2, 6) = itemCPH3
.Offset(RowCount + 3, 7) = itemWMI
.Offset(RowCount, 7) = itemWMI1
.Offset(RowCount + 1, 7) = itemWMI2
.Offset(RowCount + 2, 7) = itemWMI3
.Offset(RowCount + 3, 8) = itemSCR
.Offset(RowCount, 8) = itemSCR1
.Offset(RowCount + 1, 8) = itemSCR2
.Offset(RowCount + 2, 8) = itemSCR3
.Offset(RowCount + 3, 9) = itemTVH
.Offset(RowCount, 9) = itemTVH1
.Offset(RowCount + 1, 9) = itemTVH2
.Offset(RowCount + 2, 9) = itemTVH3
.Offset(RowCount + 3, 10) = itemGCUH
.Offset(RowCount, 10) = itemGCUH1
.Offset(RowCount + 1, 10) = itemGCUH2
.Offset(RowCount + 2, 10) = itemGCUH3
.Offset(RowCount + 3, 11) = itemLH
.Offset(RowCount, 11) = itemLH1
.Offset(RowCount + 1, 11) = itemLH2
.Offset(RowCount + 2, 11) = itemLH3
End With
myData.Save
End Sub
Is there any reason why it wouldn't like non-numerical inputs?
Thank you!
