method

  1. N

    Todays little conundrum copy worksheet.

    Morning all Using what I have learned, today I managed to copy a range of one sheet to a new sheet preserving formatting using the paste special method. This sheet is then copied to a new workbook that opens, saves and closes using the below code. This method although very simple does not seem...
  2. sharky12345

    Efficient method to add hyperlink

    Afternoon guys, I'm trying to find an efficient method to add a hyperlink to a range of cells but what I have so far keeps generating an 'Out of memory' message. The code itself runs fine, but once it's complete I get the message. This is what I have so far: LastImportRow =...
  3. S

    "Paste method of Worksheet class failed" but only on alternate executions

    Hi folks, can anyone tell me why this works every alternate time it's run? Run it and it works fine. Run it again "Paste method of Worksheet class failed" Run it again it works fine. Run it again "Paste method of Worksheet class failed" etc..... Thanks. Sub CopyRows() Dim RowNum As Integer Dim...
  4. I

    Copy columns based on header and array

    Hello, I have data in Sheet1 that I'd like to copy into Sheet2 based on the header. I'd like to create some kind of text array that I can update in the code to include new columns if they become relevant. I'd like the relevant headers array to be something like sWords = {"Date", "Qty"...
  5. O

    How to take values and populate a matrix using Find Method

    I'm not too familiar with the Find method as I've always used For loops, which take way too much time. What I'm trying to do is go down a list of over 400,000 values in four columns and then take three of those values to determine where in a matrix the fourth value should be placed. For...
  6. D

    Uses for the range.find method

    Can you use the range.find method to find the lowest row that the contents of a text box have expanded down to cover? For instance, if I have a regular text box with resize shape to fit text selected. I put the textbox in row 2, type "a" + hit enter, type "a" + hit enter, type "a" + hit enter...
  7. F

    SendKeys Method Hide Screen While executing

    Hi, I am executing a coding using sendkeys method. I wouldnt like to show the pages in the internet that I am using until the code delivers the resultant. The code below opens the google translator and write something, then it plays the audio from google translator audio play. How to hide the...
  8. D

    How to change "dates" to clean data

    https://1drv.ms/x/s!AvjBsEPEq12ngUI4aV0PG-l-4gVM?e=pByejI How do I convert the data in A1:B7 to the output in rows 12 and below (happy to use any method, pivot tables, power query etc) (Purpose is so that I can do YTD calculations using the output)
  9. B

    method value of object metaproperty failed

    I am running a macro that saves a certain cell data to a property in sharepoint metadata. It works great. But, there's always a but, there are times that the string has a comma, then an intentional space at the end. When that is the case, I receive the following error: "method value of object...
  10. S

    Method 'range' of object '_worksheet failed error on a simple formula and code

    Hi, In a module, I have the following: IF Sheet4.Range("TheList_CountBlanks").Value > 0 Then Msgbox "Helpful message." Exit Sub End If That range name uses the function COUNTBLANK on a simple range and is successfully used in formulas. But that code keeps blowing up with that error. What...
  11. S

    Method Range of Object Global Failed Error in With statement

    Greetings, I recently lost a bunch of macros to a PERSONAL file saved in the binary extension format (users caution/beware!) and am having to rebuild several macros. I am running into a method range of object global failed error including the following lines of code. The macro to which these...
  12. S

    Method Range of Object Global Failed Error in VBA macro

    Greetings, I am running into a Method 'Range' of object'_Global' failed error in my macro below Dim AA as String: AA = ActiveSheet.Name ActiveSheet.Activate Range("A4:K300").Select ActiveSheet.Shapes.AddChart2(227,xlLine).Select ActiveChart.SetSourceData Source:=Range("AA!$A$4:$K$300")...
  13. D

    Running Macro using Task Scheduler- Getting Run Time Error

    I keep getting Run-time error '-2147467259 (80004005)': Method 'MailEnvelope' of object '_Worksheet' failed when trying to run the a batch file with this script wscript.exe "U:\Programming\Scripts\VBS Scripts\SendEmails.vbs" SendEmail.vbs Option Explicit Sub ExcelMacroExample() Dim xlApp...
  14. T

    Loop of Find

    Does anybody know if it's faster to loop through a column of data or use the Find method to look for a particular value?
  15. X

    Advanced filter automatically

    Hi, I'm trying to set up an advanced filter that updates automatically and here's my code: Sub advancedfilter() Sheets("Sheet1").Range("A9:N151").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Sheet1").Range("K1:K2"), _ CopyToRange:=Sheets("Sheet2").Range("A8:N8") _...
  16. T

    File Properties

    Does anyone know if it is possible to prevent users from clicking on File -> Properties-> Advanced Properties? I have written code to hide named ranges, so don't want them "revealed" via the above method. Thanks
  17. I

    Dynamic List Sheet simplifying

    Hello I have a dynamic sheet that I am working on that requires a drop down list for multiple cells. I have a table for each drop down list to make the main list searchable as demonstrated here https://www.extendoffice.com/documents/excel/2439-excel-drop-down-list-search.html. The problem is...
  18. S

    Looking for a formula to insert column header text

    I must compile over 60 multipage paper surveys. The 1st question has 20 possible responses and the users marked all that applied. The easiest method I can come up with is to "x" the appropriate box and excel replace the "x" with the column title. But I can't get it to work. Can you suggest a...
  19. I

    Need to know expired qty based on my consumption data

    Dear Team, I have problem in finding expiry stock, I have a file contain two sheets, First sheet shows the batch wise stock with expiry dates and the second sheet shows month wise consumption, for example I have first batch expiry in June-2019 with the qty of 1000, and my stock consumption in...
  20. D

    method open of object workbooks failed

    While working on a spreadsheet at work, I was getting the error "method open of object workbooks failed", but when I got home, the same file was not giving me any errors. Any ideas why?

Some videos you may like

This Week's Hot Topics

Top