vba & excel 2010

Hi, I am trying to loop through a dynamic array B2:G and results on M:V and this is what I tryed. Sub Monte_carlo() With Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row) .FormulaR1C1 = "=IF(RC[-11]="""","""",IF(MOD(RC[-11],12)=0,""E"",""P""))" .Value = .Value End With With Range("N2:N"...

Is there any way, using VBA, to set the file name in the save as dialog box when the user exits the workbook or selects the save/save as via the File menu, The workbook is protected as read only and the save as dialog box automatically pops up as a result; however, I want to have a filename...

Hi there, I have the following code which runs on every sheet of my spreadsheet and there are 167 sheets in total; Sheets(Range("A164").Value).Select ClientNarrative HideRows PrintSetup Sheets(Range("A165").Value).Select ClientNarrative HideRows PrintSetup Sheets(Range("A166").Value).Select...

Hello, I'm wondering, is it possible for a VBA code to automatically sort the tabs by alphabetical order? It is quite a large spreadsheet, with 42 tabs. The first 3 tabs shouldn't be included when they are sorted. These tabs are called; - Dashboard - Invoice Data - Client Details The tabs...

Hi i have a code below and i want manually selected rows all at once to delete however if i have the 1st msgbox then it just says no rows selected Sub deleterows() Dim i As Long, irow As Long Dim oSel As Range Dim answer As String Set oSel = Selection If oSel.Areas.Count <= 0 Then 'if i...

Hello I have an workbook whereby it has all the expiry dates and you can filter the each department which is on sheet2 filtered from sheet1 and show you any due dates for the selected department if there are any due dates and can send email based on filtered dates however problem is that...

Hello i have got code whereby i get can error overflow on the line If i = 100 Or i = r.count Then Exit For and if i change to this If i = 100 Or i = r.CountLarge Then Exit For then it works however then it is only copying the first row which is the title only. can someone help me please BTW...

Hello everyone i have got the code whereby it copies from Sheet 2 and then pastes onto the sheet 3 blanks rows however problem is when there is a value e.g. 4 rows as got value it will overwrite on the last used row which is 4th row instead of pasting on to next available blank row. can someone...

Hi i have got userform whereby user select the on the image and it changes to hyperlink and add the hyperlink to a cell however when tried opening the hyerlink it wort open, can someone point me in the right direction please. Private Sub CommandButton3_Click() On Error GoTo errHandler...

Hi i have user form whereby code below placed in command button whereby user can select the pictures from their driver and and it will upload a hyperlink on the sheet Column "I" however when try to open the hyperlink it says "Cannot open the Specific file" can someone please help me where m i...

Hi I have an staff training record whereby in column F-Z starting from row 9 with the expiry date and in column A9 onward all the staff name and type of training in columns F9-Z9. i want macro to send email with the all the Due date in each column to One send email with the name, due date and...

Hi i have userform with the listbox and is it possible to change the color of the value if found in listbox below it is the code of the list values. i want Next .AddItem .List(counter, 0) = sourceCell.Offset(0, 0).Value...

Hello i have userform and textbox 14 you type a date e.g. 29/12/2020 and when it adds on to the sheet it is keep changing it to 12/29/2020. i even set the cells as short date also using the custom format change to DD/MM/YYYY however when userform adds the data its keep changing to MM/DD/YYYY...

Hi i have userform of which there is two textboxes of you enter the two between dates and combobox to select name from column H and user finds the matched date in Column against name in column H however it wont add any data to listbox to show all the data matched with the date between two dates...

Respected gurus, experts and MVPs, I have written the following code for the pivot table and it is generating pi.Visible <Type Mismatch> error. Sub piVisibleMismatch() Dim lastrow As Long, endrow As Long Dim Psheet As Worksheet, Dsheet As Worksheet Dim Pcache As PivotCache, Ptable As...

Hi, I have the below code which is returning an error, I believe near the bottom. It says I need to "End With" but when I put "End With" before "End Sub" it says it wasn't expecting "End With" :banghead: I'd really like this to run without any problems. Can someone please show me where I am...

Hi, I have a set of data which is exported into excel and I would like to automatically clean it up, so that it can be analysed. Once I select the entire data and sort it by column A, smallest to largest, it is then sorted by ticket number. I would then like to delete the rubbish on the...

Hey there, I am trying to create a macro that will "read" the range from a cell such as "02" (A1:M39) and export that range as a PDF, however I have only been able to set the range directly in the VBA code, is it even possible to have it read a range that changes dynamically? For comparison...

Hi, I found this neat VBA code to protect / unprotect all worksheets without having to do each one manually. See below. The only problem is, it only allows; - Select Locked Cells - Select Unlocked Cells Whereas I want another 2 options to be included as well; - Format columns - Format rows Does...

Hi, I nearly have this working. I want sheet 12 (called Journal) to be copied and saved into a new macro-enabled workbook. It's getting stuck at the save part, saying "The following features cannot be saved in macro-free workbooks: VB project" Sub New_Journal() Dim wb As Workbook Set...