With oXLBook
.Sheets.Add(After:=.Sheets(.Sheets.count)).Name = "PriceList"
End With
Set oXLSheet = oXLBook.Worksheets("PriceList")
oXLSheet.Cells(1, 1).Value = "Brand"
oXLSheet.Cells(1, 2).Value = "Pre Code"
oXLSheet.Cells(1, 3).Value = "Model"
oXLSheet.Cells(1, 4).Value = "Size"
oXLSheet.Cells(1, 5).Value = "Colour Code"
oXLSheet.Cells(1, 6).Value = "Sell For"
oXLSheet.Cells(1, 7).Value = "Over NHS Voucher"
oXLSheet.Cells(1, 9).Value = "Cost Price"
oXLSheet.Range("A1:I1").Font.Bold = True
'brand, precode, if sale
one = "M Kors,MK,yes"
two = "M Kors Sun,MK,no"
three = "Ray-Ban Adult,RB,yes"
four = "Ray-Ban Kids,RB,no"
five = "Ray-Ban Sun,RB,no"
six = "Armani,EA,yes"
seven = "Vogue,VO,yes"
eight = "Mango,MNG,yes"
Dim brand As String
Dim precode As String
overallcount = 1
Dim firstpass As Boolean
firstpass = True
Dim firstdata As Boolean
Dim v As Variant
Dim sale As String
Dim sale1 As Integer
Dim sale2 As Integer
For Each st In Array(one, two, three, four, five, six, seven, eight)
v = Split(st, ",")
brand = v(0)
precode = v(1)
sale = v(2)
Ret = False
For Each oXLSheet In oXLBook.Sheets
If UCase(oXLSheet.Name) = brand Then
Ret = True
Exit For
End If
Next
firstdata = True
If Ret = True Then
Set oXLSheet = oXLBook.Worksheets(brand)
LastRow = oXLSheet.UsedRange.Rows.count + 1
'sort ascending
oXLBook.Worksheets(brand).Sort.SortFields.Clear
oXLBook.Worksheets(brand).Sort.SortFields.add2 Key:=Range( _
"F3:F" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With oXLBook.Worksheets(brand).Sort
.SetRange Range("A3:AP" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For counter = 3 To LastRow
Set oXLSheet = oXLBook.Worksheets(brand)
If oXLSheet.Cells(counter, 22).Value = "" And oXLSheet.Cells(counter, 23).Value = "" And Not oXLSheet.Cells(counter, 5).Value Like "" Then
If firstdata = True Then
firstdata = False
If firstpass = True Then
firstpass = False
overallcount = overallcount + 1
Else
overallcount = overallcount + 2
End If
Else
overallcount = overallcount + 1
End If
For x = 4 To 6 'brand, pre code and model
Set oXLSheet = oXLBook.Worksheets(brand)
inputoutput = oXLSheet.Cells(counter, x).Value
Set oXLSheet = oXLBook.Worksheets("PriceList")
oXLSheet.Cells(overallcount, x - 3).Value = inputoutput
Next x
oXLSheet.Cells(overallcount, 2).Value = precode ' change pre code to my version
Set oXLSheet = oXLBook.Worksheets(brand) 'size
inputoutput = oXLSheet.Cells(counter, 8).Value
Set oXLSheet = oXLBook.Worksheets("PriceList")
oXLSheet.Cells(overallcount, 4).Value = inputoutput
Set oXLSheet = oXLBook.Worksheets(brand) 'colour
inputoutput = oXLSheet.Cells(counter, 9).Value
Set oXLSheet = oXLBook.Worksheets("PriceList")
oXLSheet.Cells(overallcount, 5).Value = inputoutput
If PriceListSale = True And sale Like "yes" Then
sale1 = 28
sale2 = 30
Else
sale1 = 17
sale2 = 19
End If
Set oXLSheet = oXLBook.Worksheets(brand) '17 = sell for, sale = 28
inputoutput = oXLSheet.Cells(counter, sale1).Value
Set oXLSheet = oXLBook.Worksheets("PriceList")
oXLSheet.Cells(overallcount, 6).Value = inputoutput
Set oXLSheet = oXLBook.Worksheets(brand) '19 = over voucher, sale = 30
inputoutput = oXLSheet.Cells(counter, sale2).Value
Set oXLSheet = oXLBook.Worksheets("PriceList")
oXLSheet.Cells(overallcount, 7).Value = inputoutput
Set oXLSheet = oXLBook.Worksheets(brand) 'cost price not inc glazing
inputoutput = oXLSheet.Cells(counter, 12).Value
Set oXLSheet = oXLBook.Worksheets("PriceList")
oXLSheet.Cells(overallcount, 9).Value = inputoutput
'Debug.Print Counter & "____" & LastRow
End If
Next counter
oXLBook.Worksheets(brand).Sort.SortFields.Clear
oXLBook.Worksheets(brand).Sort.SortFields.add2 Key:=Range( _
"U3:U" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With oXLBook.Worksheets(brand).Sort
.SetRange Range("A3:AP" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
oXLBook.Worksheets(brand).Sort.SortFields.Clear
oXLBook.Worksheets(brand).Sort.SortFields.add2 Key:=Range( _
"V3:V" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With oXLBook.Worksheets(brand).Sort
.SetRange Range("A3:AP" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
oXLBook.Worksheets(brand).Sort.SortFields.Clear
oXLBook.Worksheets(brand).Sort.SortFields.add2 Key:=Range( _
"W3:W" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With oXLBook.Worksheets(brand).Sort
.SetRange Range("A3:AP" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'MsgBox oXLSheet("Test").Cells(1, 4).Value
Else
MsgBox brand & " not there"
End If
Next st
oXLApp.DisplayAlerts = False
'put word code here
Set oXLSheet = oXLBook.Worksheets("PriceList")
Dim objWord
Dim objDoc
Dim objRange
Dim objTable
Set objWord = CreateObject("Word.Application")
If PriceListSale = True Then
Set objDoc = objWord.Documents.Open(gdrive & "Designer Frame Pricing SALE.docx", Visible:=False)
Else
Set objDoc = objWord.Documents.Open(gdrive & "Designer Frame Pricing.docx", Visible:=False)
End If
objWord.Visible = True
' Set objDoc = objWord.Documents.Add
Set objRange = objDoc.Range
If objDoc.Tables.count <> 0 Then
objDoc.Tables(1).Delete
End If
Set objRange = objDoc.Range.Bookmarks("tablehere").Range
objDoc.Tables.Add objRange, overallcount, 7
Set objTable = objDoc.Tables(1)
For i = 1 To overallcount
For j = 1 To 7
objTable.Cell(i, j).Range.Text = oXLSheet.Cells(i, j).Value
Next
Next
With objTable
.Borders.Enable = True
.Columns(1).Width = CentimetersToPoints(4)
' .Columns(2).Width = CentimetersToPoints(4.24)
.Columns(2).Width = CentimetersToPoints(2)
.Columns(3).Width = CentimetersToPoints(2)
.Columns(4).Width = CentimetersToPoints(2)
.Columns(5).Width = CentimetersToPoints(2.34)
.Columns(6).Width = CentimetersToPoints(2)
.Columns(7).Width = CentimetersToPoints(2.75)
'.Rows.Height = CentimetersToPoints(0.4)
.Rows.HeightRule = wdRowHeightAuto
.Range.Font.Size = 14
.Range.Font.Name = "Trebuchet MS"
.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
' .Range.Font.Bold = True
End With
'objDoc.saveas filename:="C:\Users\mypc\Desktop\price list fully auto\Designer Frame Pricing.docx"
If PriceListSale = True Then
objDoc.saveas filename:=gdrive & "Designer Frame Pricing SALE.docx"
Else
objDoc.saveas filename:=gdrive & "Designer Frame Pricing.docx"
End If
objWord.Quit
Set objWord = Nothing
'end word code
On Error Resume Next
If PriceListSale = True Then
If (Environ$("Username")) = "Admin" Then oXLBook.saveas ("C:\Users\Admin\OneDrive\PriceListSALE.xlsx")
If (Environ$("Username")) = "mypc" Then oXLBook.saveas ("C:\Users\mypc\OneDrive\PriceListSALE.xlsx")
Else
If (Environ$("Username")) = "Admin" Then oXLBook.saveas ("C:\Users\Admin\OneDrive\PriceListNORMAL.xlsx")
If (Environ$("Username")) = "mypc" Then oXLBook.saveas ("C:\Users\mypc\OneDrive\PriceListNORMAL.xlsx")
End If