Posts by pantakos

Sub BuildTemplateINm()
 Dim i As Long, j As Long, nr As Long
 Dim sht As Variant
 Dim cell As Variant, f As Range
 Dim Descript As String
 Dim shtName As Worksheet '<- added
 'Set shtName = ActiveSheet  '<- added
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 Sheets.Add.Name = "NewSheet"
 Worksheets("NewSheet").Range("A1").Value = "Description"
 Worksheets("NewSheet").Range("B1").Value = "Quantity"
 'nr = 2
 sht = Array("D")
 ws = Array("1.Power Distribution - Dimmer", "2.POWER CABLES - ADAPTORS  ", "3.CABLES (OTHER) - CABLE CROSS ")
 Sheets("NewSheet").Range("A2:B150").ClearContents
  'Loop through all sheets in sheets array
    For i = LBound(ws) To UBound(ws)
    Set shtName = ActiveSheet  '<- added
      nr = 2
'       Loop through all columns in the column array
        For c = LBound(sht) To UBound(sht)
'           Find last row in column with data
            Sheets(ws(i)).Activate
            lr = Cells(Rows.Count, sht(c)).End(xlUp).Row
'           Loop through all cells in column
            For Each cell In Range(Cells(1, sht(c)), Cells(lr, sht(c)))
'               Check to see if value is numeric and not 0
                If (IsNumeric(cell.Value)) And (cell.Value <> 0) Then
'                   Copy cells C, D, E to columns A, B, C of main sheet
                    'Range(Cells(cell.Row, "C"), Cells(cell.Row, "E")).Copy Sheets("NewSheet").Cells(nr, "A")
                    
                    Range(Cells(cell.Row, "C"), Cells(cell.Row, "E")).Copy
                    Sheets("NewSheet").Cells(nr, "A").PasteSpecial Paste:=xlPasteValues
                    Application.CutCopyMode = False
'                   Increment nr counter
                    nr = nr + 1
'                   'Check to see if rows are full
                    'If nr > 70 Then
                        'MsgBox "Rows are full"
                        'Exit Sub
                    'End If
                End If
            Next cell
        Next c
   Next i

 Application.ScreenUpdating = False
 Const S = "NewSheet"
 Dim N$
 N = ActiveWorkbook.Path & "\" & shtName.Name & ".xlsx" '<- changed
 Sheets(S).Move
 Application.DisplayAlerts = False
 ActiveWorkbook.SaveAs N, 51
 Application.DisplayAlerts = True

End Sub

Sub BuildTemplateINm()
 Dim i As Long, j As Long, nr As Long
 Dim sht As Variant
 Dim cell As Variant, f As Range
 Dim Descript As String
 Dim shtName As Worksheet '<- added
 Set shtName = ActiveSheet  '<- added
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 Sheets.Add.Name = "NewSheet"
 Worksheets("NewSheet").Range("A1").Value = "Description"
 Worksheets("NewSheet").Range("B1").Value = "Quantity"
 nr = 2
 sht = Array("D")
 ws = Array("1.Power Distribution - Dimmer", "2.POWER CABLES - ADAPTORS  ", "3.CABLES (OTHER) - CABLE CROSS ")
 Sheets("NewSheet").Range("A2:B150").ClearContents
  'Loop through all sheets in sheets array
    For i = LBound(ws) To UBound(ws)
'       Loop through all columns in the column array
        For c = LBound(sht) To UBound(sht)
'           Find last row in column with data
            Sheets(ws(i)).Activate
            lr = Cells(Rows.Count, sht(c)).End(xlUp).Row
'           Loop through all cells in column
            For Each cell In Range(Cells(1, sht(c)), Cells(lr, sht(c)))
'               Check to see if value is numeric and not 0
                If (IsNumeric(cell.Value)) And (cell.Value <> 0) Then
'                   Copy cells C, D, E to columns A, B, C of main sheet
                    Range(Cells(cell.Row, "C"), Cells(cell.Row, "E")).Copy Sheets("NewSheet").Cells(nr, "A")
'                   Increment nr counter
                    nr = nr + 1
'                   'Check to see if rows are full
                    'If nr > 70 Then
                        'MsgBox "Rows are full"
                        'Exit Sub
                    'End If
                End If
            Next cell
        Next c
    Next i
 
 Application.ScreenUpdating = False
 Const S = "NewSheet"
 Dim N$
 N = ActiveWorkbook.Path & "\" & shtName.Name & ".xlsx" '<- changed
 Sheets(S).Move
 Application.DisplayAlerts = False
 ActiveWorkbook.SaveAs N, 51
 Application.DisplayAlerts = True
End Sub

Sub BuildInvoiceAll()
  Dim ws As Variant, arr1 As String, arr2 As String, arr3 As String, arr4 As String, arry As Variant
  Dim i As Long, j As Long, nr As Long
  Dim cell As Range, f As Range
  Dim Descript As String
  Dim all_numeric As Boolean
  all_numeric = True
    
  Application.ScreenUpdating = False
  'Set array of worksheet names to copy from
  ws = Array("AUDIO", "LIGHTS")
  
  'cells to AUDIO sheet
  arr1 = "E:E, J:J"
  'cells to LIGHTS sheet
  arr2 = "E:E, J:J"
  'cells to HOISTS sheet
  'arr3 = "E:E, K:K"
  'cells to DISTRO sheet
  'arr4 = "E:E, K:K"
  arry = Array(arr1, arr2)
  nr = 14
  Sheets("PROFORMA DRYHIRE").Range("A15:C70").ClearContents
  For i = LBound(ws) To UBound(ws)                  'Loop through all shees in the array
    For Each cell In Sheets(ws(i)).Range(arry(i))   'Loop through all cells in the multirange
    If Not IsNumeric(cell) Then 
        all_numeric = False
        Exit For
    End If
Next cell
For Each cell In Sheets(ws(i)).Range(arry(i))
      If cell > 0 Then                              'See if anything entered in pieces
        Descript = cell.Offset(0, -3)               'get description from column B
        With Sheets("PROFORMA DRYHIRE")
          Set f = .Range("A15:A70").Find(Descript, , xlValues, xlWhole)
          If Not f Is Nothing Then
            nr = f.Row
          Else
            nr = nr + 1
            If nr > 70 Then
              MsgBox "Rows are full"
              Exit Sub
            End If
          End If
          .Cells(nr, "A") = Descript                'Populate values in PROFORMA sheet
          .Cells(nr, "B") = cell                    'get pieces from column E
          .Cells(nr, "C") = cell.Offset(0, -1)      'get price p/d from column D
        End With
      End If
    Next cell
  Next i
  Application.ScreenUpdating = False
End Sub

// Δημιουργία Μενού

function onOpen(){
  const ss = SpreadsheetApp.getActive();
  const menuEntries = [];
  menuEntries.push({name: 'Εισαγωγή σε Ημερολόγιο', functionName: 'scheduleShifts'});
  ss.addMenu('Carousel', menuEntries);
}

// Δημιουργία Event

function scheduleShifts() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
    var calendarID = spreadsheet.getRange("B2").getValue();
    var eventCal = CalendarApp.getCalendarById(calendarID);
    const signups = spreadsheet.getRange("A4:D110")
    .getValues()
    .filter(row => row.every(String));

for (x=0; x<signups.length;x++)

{
    var shift = signups[x];
    var startTime = shift[0];
    var action= shift[2];
    var description= shift[1];
    var endTime= shift[3];
    eventCal.createEvent(action, startTime, endTime, { description: shift[1] } ).setColor(4)
}
}