Hey All,
I got a macros code made by someone and work okay, not the way I want it to tho. Now that I inherited it I would like to learn how its programmed and be able to make changes to it.
I don't expect anyone to do a line by line explanation but an overview of the program or perhaps a way to understand it on my own would be helpful.
As a start, I would like to change the column QTY, I want a value of 1 (fixed value) in every row. when the new workbook is created.
Thanks for any input,
Sam
I got a macros code made by someone and work okay, not the way I want it to tho. Now that I inherited it I would like to learn how its programmed and be able to make changes to it.
I don't expect anyone to do a line by line explanation but an overview of the program or perhaps a way to understand it on my own would be helpful.
As a start, I would like to change the column QTY, I want a value of 1 (fixed value) in every row. when the new workbook is created.
Thanks for any input,
Sam
Code:
Sub ebtopartslist()
Dim name As String
Dim lastColumn As Integer
Dim lastRow As Long
Dim lastRowcolumn As Long
Application.ScreenUpdating = False
Workbooks.Add
name = "Electricals" & Format(Now(), "ss") & ".xlsx"
ActiveWorkbook.SaveAs (Application.ThisWorkbook.path & "\" & name)
ActiveWorkbook.Sheets(1).Activate
ActiveSheet.Rows("1").RowHeight = 36
ActiveSheet.Rows("4").RowHeight = 10
ActiveSheet.Columns("A").ColumnWidth = 25
ActiveSheet.Columns("B").ColumnWidth = 100
ActiveSheet.Columns("D").ColumnWidth = 25
ActiveSheet.Cells(1, 1).Value = "Company logo"
ActiveSheet.Cells(1, 1).Font.Bold = True
ActiveSheet.Cells(1, 1).Font.Size = 11
ActiveSheet.Cells(1, 2).Value = "Electrical Parts List"
ActiveSheet.Cells(1, 2).Font.Bold = True
ActiveSheet.Cells(1, 2).Font.Size = 24
ActiveSheet.Cells(1, 2).Font.name = "Verdana"
ActiveSheet.Cells(1, 4).Value = "Date:" & Format(Date, "mmmm") & " " & Int(Format(Date, "mm")) & "," & Format(Date, "yyyy")
ActiveSheet.Cells(1, 4).Font.Size = 11
ActiveSheet.Cells(1, 4).Font.name = "Verdana"
ActiveSheet.Cells(2, 1).Value = "Job number:" & vbCrLf & "Machine Modle:" & vbCrLf & "Customer:" & vbCrLf & "Commission:" & vbCrLf
ActiveSheet.Cells(2, 1).Font.Size = 10
ActiveSheet.Cells(2, 1).Font.name = "Verdana"
ActiveSheet.Cells(2, 1).HorizontalAlignment = xlRight
ActiveSheet.Rows("2").AutoFit
ActiveSheet.Cells(2, 2).Value = "Schematic:"
ActiveSheet.Cells(2, 2).HorizontalAlignment = xlCenter
ActiveSheet.Cells(2, 2).VerticalAlignment = xlTop
ActiveSheet.Cells(3, 1).Value = "Part No."
ActiveSheet.Cells(3, 1).Font.Size = 14
ActiveSheet.Cells(3, 1).Font.name = "Verdana"
ActiveSheet.Cells(3, 1).Font.Bold = True
ActiveSheet.Cells(3, 2).Value = "Description"
ActiveSheet.Cells(3, 2).Font.Size = 14
ActiveSheet.Cells(3, 2).Font.name = "Verdana"
ActiveSheet.Cells(3, 2).Font.Bold = True
ActiveSheet.Cells(3, 3).Value = "Qty"
ActiveSheet.Cells(3, 3).Font.Size = 14
ActiveSheet.Cells(3, 3).Font.name = "Verdana"
ActiveSheet.Cells(3, 3).Font.Bold = True
ActiveSheet.Cells(3, 4).Value = "Designation"
ActiveSheet.Cells(3, 4).Font.Size = 14
ActiveSheet.Cells(3, 4).Font.name = "Verdana"
ActiveSheet.Cells(3, 4).Font.Bold = True
Workbooks("fiverrElectronics.xlsm").Activate
ActiveSheet.Cells.Borders.LineStyle = xlLineStyleNone
desc = Application.WorksheetFunction.Match("Material", Rows("1:1"), 0)
cst = Application.WorksheetFunction.Match("Description", Rows("1:1"), 0)
partno = Application.WorksheetFunction.Match("Designation", Rows("1:1"), 0)
Manufacture = Application.WorksheetFunction.Match("Manufacturer", Rows("1:1"), 0)
Workbooks(name).Sheets(1).Cells.WrapText = True
Workbooks("fiverrElectronics.xlsm").Sheets(1).Range(Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(2, desc), Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(Rows.Count, desc).End(xlUp)).Copy Destination:=Workbooks(name).Sheets(1).Range("A5")
Workbooks("fiverrElectronics.xlsm").Sheets(1).Range(Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(2, cst), Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(Rows.Count, cst).End(xlUp)).Copy Destination:=Workbooks(name).Sheets(1).Range("B5")
Workbooks("fiverrElectronics.xlsm").Sheets(1).Range(Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(2, partno), Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(Rows.Count, partno).End(xlUp)).Copy Destination:=Workbooks(name).Sheets(1).Range("D5")
lastRow = Workbooks(name).Sheets(1).UsedRange.Rows.Count
For y = 5 To lastRow
Workbooks(name).Sheets(1).Cells(y, 2).Value = Workbooks(name).Sheets(1).Cells(y, 2).Value & vbCrLf & Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(y - 3, Manufacture).Text
Next y
lastColumn = Workbooks(name).Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
lastRow = Workbooks(name).Sheets(1).UsedRange.Rows.Count
For i = 5 To lastRow
Workbooks(name).Sheets(1).Rows(i).RowHeight = 43
If i Mod 2 = o Then
For j = 1 To lastColumn
Workbooks(name).Sheets(1).Cells(i, j).Interior.Color = RGB(192, 192, 192)
Next j
End If
Next i
Workbooks(name).Activate
ActiveSheet.Rows("4:4").Select
ActiveWindow.FreezePanes = True
Workbooks(name).Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub