Posts by Richard_C

Hi I know this is a long shot , but I have a large data set that requires a vba code to ,

A =insert additional rows based on the value in cell 'J' if greater than '1' if the cell value of J = 1 or less , no additional rows are required.
B = copy and past the original data into the blank rows below it
C = In Column 'F' (Date) of the new rows I would like to +1 from the original date in each of these cells.

Hope this makes sense. I have attached a sheet with a tab showing the original data and how I would like the results to look after (Excluding the colours)

Richard

Perfect thank you

Thank you jl2509 this formula works fine line by line , however the unique job number (column C) often has more than one line. To complicate things further Appointment Results (Column M) needs to be added to the mix. Any unique job number (column C) with an Appointment Results (Column M) of 'Unable' is automatically a failure.

Not any easy request I know

Not the best topic total. I have a list of engineer repair jobs for which I require a formula to calculate which jobs were completed 1st time with no subsequent appointments. This means that the job must have an appointment result of completed and not unable.(Unable mean failure)The jobs can have multiple appointments with the same date as the completed date, this is a 1st time fix. However if the job has multiple appointment dates with different dates to the completed date this is a failure. I have attached the sheet with some examples of 1st time fixes and failures

thanks
Richard

Great works fine
thanks Richard

Hi

I am using a email VBA code from Ron de Bruin's web site. I need the email body to be linked to a cell value 'H23' located in a sheet called 'Table' Hope this makes sense.

Richard

Perfect thank you:idea:

Hi Carim

that's correct each new tab that is created looks at Z2 to obtain the name for which the tab will be named. The reason for this was because I did not know how to get the code to rename the tabs based on the row labels.

Richard

Hi Carim

Good advice. I am using two separate VBA codes. Once to create tabs via a pivot table. The other to rename each tab based on a cell reference with in each tab. I am unsure of the exact code to use and where to add it.

Richard

Hi I require a code that colour's tabs red if they contain a number >0 and colour's the other tabs that contain a number = 0 green. Example: Richard 1 = Red, Paul 0 = Green, Peter 5 = Red, Ozgrid 0 = green.
Finally if possible I have 3 tabs (all named) that I wish NOT to colour.

All of the tabs that I would like coloured would have been created automatically by VBA form a pivot table.

thanks in advance
Richard

Great thank you. Found a code and works fine

I would like to email individually named sheets to the email address located in each of these named sheets Column AM row 2 (AM2) Ideally the I would have a standard subject and body to each of the emails sent. I currently have no code at all to start with so any would be greatly appreciated

Regards

Richard

:whip:Works perfect thank you

Hi Jonathan

that's correct the unnamed sheets have been created with the default names (Sheet1, Sheet2, etc) and P2 is the name they will be populated with. I however have three other sheets already named called 'Tables', 'Data' and 'Lists these are the sheets I would like to code to ignore.

Richard

Thank you

Hi Jonathan

thank you for the quick response. My knowledge of VBA is still basic and the code I provided was already in existence and just edited by me. Wear in the code should I add or what part of my code should I replace with

If ws.Name Like "Sheet*" Then

regards

Richard

Hi
I have a code that Auto rename sheets based on text in cell which works fine (See below). However I would like the code to ignore any sheets that have already been named.

Sub tabname()
' Names each newley created tab with the scps name
Application.ScreenUpdating = False
Worksheets("PivotTables(1)").Activate

Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
If Len(ws.Range("P2")) > 0 Then
ws.Name = Replace(ws.Range("P2").Value, "/", "-")
End If
On Error GoTo 0
If ws.Name <> Replace(ws.Range("P2").Value, "/", "-") Then
MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
End If
Next
End Sub

Thanks

Richard

Hi Ozgrid
I have a pivot table (PivotTable2) I would like a code that creates individual sheets (tabs) populated with the grand total data and are then re-named from the first row of the same pivot table. Hope this makes sense I have attached the excel sheet containing the pivot table.

Richard

Hi I have a master work book with a master sheet. The code I use creates and names individual work sheets. The second code I use save these individual work sheets to a designated folder. The problem I have, is that the VBA stopped saving the sheets with the following message appearing ' An error has occurred. Error number 1004. Error description= copy method of Worksheet class failed' I have attached the code Richard

Re: VBA Date, Time and User Stamp

Perfect works lovely thank you for you help