Posts by pantakos

    @rollis13 Unfortenetly I cant make it work

    The code is this one now


    @rollis13 One more LAST help. (As you can understand I am newbie and trying to understand and learn). The code is working fine (extremely fine) but when I am trying to create a new Template, it all what it needs to do, but when I create the first template from sheet 1 (1.Power Distribution - Dimmer) working fine. When I am trying to create an other template (lets say from sheet 2.POWER CABLES - ADAPTORS) it appends the result to NewSheet (I dont know where finds it as it has been rename and moved) and creates the new Template with data from both sheets (append the results from both sheets, or all sheets that have data at column D).

    I cant figure out why this happens, Maybe needs to make a "refresh" and delete all the other data from memory. I dont know. I am trying all night to figure out. ?(

    I attach the example for you. Thank you !

    @rollis13 is it possible to ask for something else for the same code?

    I have change the current code to working only for column D , and copy values but also copy formatting. I am stuck! Can only copy values? not formatting.

    I think I have to change code line

    Range(Cells(cell.Row, "C"), Cells(cell.Row, "E")).Copy Sheets("NewSheet").Cells(nr, "A")

    I think I have to add

    PasteSpecial Paste:=xlPasteValues

    But I am confused...

    Thank you !

    rolls13. Thank you for your answer.

    I havent test the solution but I need name to be the active worksheet, the one I am retrieving the data from. It may be 1.Power Distribution - Dimmer or 2.PowerCycle2 or 3.Dimmers . the newly created sheet take the name of the sheet from the data is retrieved.

    Hello ,

    The code below is working fine and creates a new sheet.

    The newly created sheet is saved by a constant name.

    Can be saved with the name of the sheet whEre the new sheet created plus a number (or no number just the name).

    (i,e. the name of the sheet that will get data and create a new sheet named 1.Power Distribution - Dimmer, so the new workgroup saved as this name)

    I have uploaded example. The module need to be changed is Module5


    Hello RoyUK. Thank you for your answer and effort. What I am trying to do is not to enter all the ranges i.e. E2:E45, K3:K88 etc but instead this to just enter K:K, E:E to check all the column. And furthermore, into E column there is text, and because I only need numeric values to do mathematical equation, I need to choose only numeric. I hope you understand. Thank you

    Also posted at…5605/isnumeric-with-range

    Hello all,

    I have the following code that works great. Because it will be big enough I want to choose only the numbers from E or J etc and not have to make choise (ie E34:E66. J15:J33)

    VBA Code:

    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      Application.ScreenUpdating = False  'Set array of worksheet names to copy from  ws = Array("AUDIO", "LIGHTS", "HOISTS - TRUSS - DRAPES", "DISTRO - CABLES - MISC")    'cells to AUDIO sheet  arr1 = "E:E, J:J"  'cells to LIGHTS sheet  arr2 = "E13:E34, J13:J59, E36:E59, E73:E89, J73:J82, J84:J91, E91:E98, J93:J101, E100:E109, J103:J113"  'cells to HOISTS sheet  arr3 = "E13:E28, K13:K37, E30:E40, E42:E52, E67:E91, K67:K85, E106:E123, K106:K119, K121:K129, E127:E137"  'cells to DISTRO sheet  arr4 = "E13:E35, K13:K50, E37:E50, E64:E116, K64:K88, K92:K108, K111:K120, E131:E148, K131:K148, K150:K159," & _         "E152:E180 , K163:K188 , K190:K203 , E184:E216 , K207:K238 , K240:K249 "  arry = Array(arr1, arr2, arr3, arr4)  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 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

    Can this be done?

    Thank you!

    Note! This is also posted on mrexcel Forum…meric-with-range.1196052/

    Hello all,

    I want to create an alert when I create an event based on a google spreadsheet.

    The code is

    And the sample is here

    Sample File

    Thank you in advance,


    royUK Yes! Finally with a lot of search and your valuable help I finally did it!

    I can bring monthly sum for every aspect.

    Now just I have to figure out about the users and the color (change color when one day is finished)

    Users I am good, the only problem is the color. Can you help me again?

    Change Color: At calendar lets say that the day is 29-6-2021 and is highlighted yellow because is the today(). Is it possible when day ends to change color to Red? If it is not, then I am ok, I am good!


    Thank you!

    You see , as you have understand I am not a heavy excell user. I am an IT but at the other side, hardware, switches, networks, cabling etc. So, I cant built a database, like sql I suppose, thats why I stick to excel, in order to help the company I am working for, to do the job. I am really greatfull that you answered my posts. Thank you. I will stick with what I gave so far. I will calculate months by hand. Thank you again!!!