Selecting rows in a table based on same cell values in a column and looping through the table

I have a Workbook with 2 Sheets. One Sheet is a table that gets updated every month with invoicing information and one sheet is a preset for an invoice.

I have working VBA code that takes a (manual) selection of rows and then copies the relevant information from these rows to the preset invoice in the second sheet. This then gets automatically exported as a PDF and saved to a folder.

This code/module works flawlessly. The selection of rows is based on a transaction number that is in a column. I select (manually) all rows with the same transaction number.

Now instead of manually going through the rows and selecting the relevant rows based on a transaction number and then starting the module, I’d like to automate this process.

What I have so far:

Sub PrintAll()      Dim i As Long, j As Long     Dim Arr     Dim DB As Worksheet      Set DB = Sheets("Data")      '~~> Set Range here     Arr = DB.Range("A2:X400").Value      For i = 1 To UBound(Arr, 1)         For j = 2 To UBound(Arr, 2)              If Arr(i, 7) = Arr(i + 1, 7) Then                 Arr(i, j).Select                 Call InvoiceCreator.InvoiceCreator             End If          Next j     Next i End Sub ```  The idea is to select an array based on an if statement that the values in column 7 are the same and then running the module based on the selected array. This might be completely wrong so I'm asking you guys for your experience regarding this. 

How to change the text line height in one cell?

I could not find an option in the recent version of Google Sheets to change the text line height (the spacing between lines in a single cell).

You realize the problem even more when exporting the spreadsheet as PDF, the lines are very close to each other which makes it uncomfortable to read.

Summed up, it would be helpful to adjust line heights. I like to give the text lines some spacing to each other (one long line, no splitting of the text over different cells).

Email sheets by cell value

I have 4 sheets that feed off the responses, they are time sheets. When the 1st 16 rows of job descriptions are filled in sheet 2 takes over etc. Is there a script that lets me email only the sheets with certain cells filled?

I want to save sending empty template sheets out.


App Script to move a row of data from one google sheet to a different one (different sheet id’s) based on a cell entry

I’m currently trying to adjust a script I found that copies the contents of a row from one tab of a google sheet, pastes it into the next descending empty row in a different tab and then deletes the original row.

function onEdit() {    var sheetNameToWatch = "Original_Sheet";    var columnNumberToWatch = 3;   var valueToWatch = "ValueToLookFor";   var sheetNameToMoveTheRowTo = "Target_Sheet";    var ss = SpreadsheetApp.getActiveSpreadsheet();   var sheet = SpreadsheetApp.getActiveSheet();   var range = sheet.getActiveCell();     if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {      var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);     var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);     sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);     sheet.deleteRow(range.getRow());   } } 

What I want to do from here is be able to run the same script but with the target sheet to be a sheet located on a completely different google sheet instead of from tab to tab within the same google sheet.

I have next to zero coding knowledge so unsure how I would edit it from here. Thank you in advance!

Tap to overwrite cell Google Sheets on Android?

I have a Google Sheets golf scorecard which I use on my Android 7 phone. I use it to capture my scores at the end of the round, to calculate stableford points and adjust my handicap. Each time I want to quickly enter a score for each hole, overwriting the previous entry in each cell. However when I tap on a cell which contains an existing number and start typing a number the new number gets appended to the existing one, rather than overwriting it. So for example if a cell contains a 4, and I tap on it and enter a 5, the cell value becomes 45 rather than 5. Obviously I can use the backspace key before entering each value, but that’s a bit tedious when I want to quickly enter 18 values. Is there a way of configuring the Sheets app to overwrite existing values when I tap on a cell and start typing?


I’m not very familiar with Google Sheets and have a question that may be very simple but I cant seem to find an answer. I am trying to pull information from one same cell example, H47 from multiple sheets. If I type ‘1’!H47, is there a way for this sequence to follow? When I highlight the ‘1’!H47 and drag, it keeps the sheet number and changes the cell. How can i set it up for the sheet to change but not the cell?

Thank you!


Assert cell style onto text pasted into that cell

I’m not quite sure how to word this so I couldn’t find any answers to my question after some searching.

I am trying to copy and paste values into my google sheet. These values are copied from another website which has a different font and font size than that which I have in my sheet. However, when these values are pasted they appear in the style that is on the website. Is there a way to pre-set a cell’s style so that any value pasted into it conforms to the cell’s preset style?

I am aware that I could set up multiple sheets, one as an input and another as an output, in order to solve this problem, although I’d rather not do this.

Copying data from one cell in another based on date

I have a spreadsheet, using google sheets, that I have formed into the following metrics:

Column A is the dates of the month
Column B is the data

I want to be able to copy the data from Column B into Column D Cell 1 based on today’s date and have Column D Cell 1 update every day based on the day of the month it is.

Today’s date 6/6/2018

Data is pulled from Column B using the defined date of today, and posted into D:1, and each new day the column is automatically updated.

Any help is appreciated.

How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters.

I have this formula that only works for any types of duplicate words:

=join(" ",unique(transpose(split(A1,", ")))) 

In this case, if a cell contains:

W3-X500 Samsung Galaxy W3-X500 5 inches and 5 different colors 

It returns me:

W3-X500 Samsung Galaxy 5 inches and different colors 

I will lose the second 5 character that instead I need.

How can I do it?

Timestamp based on cell value

I’ve created a script that inserts a timestamp based on any update to a specific cell; however I’m trying to insert a timestamp on a specific condition: ‘Complete’.

Would anyone know how to modify this script?

function onEdit() {   var s = SpreadsheetApp.getActiveSheet();   if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet     var r = s.getActiveCell();      if( r.getColumn() == 1 ) { //checks the column        var nextCell = r.offset(0, 1);       //if( nextCell.getValue() !== '' ) //is empty?       nextCell.setValue(new Date()).setNumberFormat('dd-MM-yyyy HH:mm:ss');     }      if( r.getColumn() == 2 ) { //checks the column       var nextCell = r.offset(0, 1);       //if( nextCell.getValue() !== '' ) //is empty?       nextCell.setValue(new Date()).setNumberFormat('dd-MM-yyyy HH:mm:ss');     }      if( r.getColumn() == 3 ) { //checks the column - Complete       var nextCell = r.offset(0, 1);       //if( nextCell.getValue() !== '' ) //is empty?       nextCell.setValue(new Date()).setNumberFormat('dd-MM-yyyy HH:mm:ss');      }   } }