sht

  1. R

    VBA Userform - Find & Replace

    Found this code to Find and Replace; Sub FindReplaceAll() 'PURPOSE: Find & Replace text/values throughout entire workbook 'SOURCE: www.TheSpreadsheetGuru.com Dim sht As Worksheet Dim fnd As Variant Dim rplc As Variant fnd = "April" rplc = "May" For Each sht In ActiveWorkbook.Worksheets...
  2. E

    combining VBA code creating bugs

    I use several subs for the file I maintain and an odd bug has started happening despite no change to the code. The rows that have data in them are supposed to be underlined as there are 10 columns of data and when it's printed out it is easier to read. The issue is that there are blank rows...
  3. A

    save sheets as PDF

    I have a workbook with 20 sheets with names starting with "LP", so LP01, LP02, LP03 ...LP20. There are also 4 other sheets with other names, not in the LP range. I have the code below to save all sheets as PDF with names as follows: "B-II [18_05] LP01. John.pdf", "B-II [18_05] LP02. Peter.pdf"...
  4. L

    Creating an auto-refreshing webpage from spreadsheet

    Below is my code to produce an HTML page. It works but I would like to add some code that would make the page auto-refresh in the browser that it is opened in because the file may get updated. How would I make that happen? Sub DataToHTM() Dim r As Excel.Range, rHTM As Excel.Range, wbk As...
  5. S

    Copy filtered rows along with specific columns

    Hello there, Hope you are creating magic in excel every day. Below are the codes I have got, which creates a new worksheet (consolidate) and get data into the sheet from other worksheets. It only fetches filtered rows and all the columns. Sub copyfrmworksheet() Dim wrk As Workbook Dim sht As...
  6. Johnny Thunder

    VBA Help - Set Lastrow based on specific text?

    Hi guys - working on a project to set some formulas to the lastrow. normally I would use the "Cells(Rows.Count, 1).End(xlUp).Row" type method because it works great for me. But for this worksheet I am unable to use this method so I spotted a column that defines the last row based on a cells text...
  7. J

    Macro that work from another sheet when my worksheet is hidden

    Hi I need my macro to work from another sheet when my worksheet is hidden Can anyone help and explain how to do it, Sub Date_Change_Weekly()' ' Date_Change_Weekly Macro ' Dim Sht As Worksheet Set Sht = ActiveWorkbook.Sheets("Pipeline") Sht.Cells.FormulaR1C1 = "=TODAY()"...
  8. A

    How to prevent only certain items in dropdown list from triggering action

    Hi guys, I have two dropdown lists, one for Status (column G), and one for Priority (column H) in one sheet. Status list items: Avvakta, Underlag saknas, Utgår, Pågår, Klart Priority list items: Prio 1, Prio 2, Prio 3, Övrigt Now I've got a code that needs some modification in order to...
  9. R

    Insert copied data before Totals line

    Hi all, I have this code (thank you Fluff) where it copies and pastes data in the last empty line in a worksheet. I would need to improve on that by ensuring that this code takes into consideration that the last non empty line is my Totals line and therefore it has to insert the copied data...
  10. D

    Used Range in Vba taking all cells not just used cells

    I have a code in vba to clear all unlocked cells in the used range on every sheet. It takes 40 minutes to execute this code. In order to figure out why it was taking so long, I used vba to select the used range. I do not use any columns past column S. But currently, it has all the way to...
  11. G

    Delete a Cell, Not a Row using Last Row Method

    LastRow = Range("B:D").Cells.Find("*", _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row MsgBox "The Last Non-Blank Row Is " & LastRow For Each sht In ActiveWorkbook.Worksheets If sht.Visible And (sht.Name = "Growth") Then sht.Activate For r = Cells(Rows.Count...
  12. G

    Delete Specific Sheets in a Workbook

    For Each sht In ActiveWorkbook.Worksheets If sht.Visible And (sht.Name = "4-0") _ And (sht.Name = "4-1") _ And (sht.Name = "4-2") Then sht.Delete End If Next sht Hello all I thought the code above would loop through the workbook and delete the specific sheets listed...
  13. G

    Else If Question

    Hello All For Each sht In ActiveWorkbook.Worksheets If sht.Visible And (sht.Name = "Alignment") Then sht.Delete Sheets.Add.Name = "Alignment" sht.Activate Cells.Select With Selection .Clear .RowHeight = 20.01 .ColumnWidth = 8.43...
  14. Z

    Run time error 1004

    Hi I'm am trying to run this down below, but I get error its basically replacing an existing sheet and want to see if there is anyway around this. Error message Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns that the source workbook. To...
  15. B

    VBA: 'application.username' with multiple usernames

    Hi Current code; Private Sub Workbook_Open() Dim sh As Worksheet For Each sh In Worksheets sh.Visible = True Next Dim sht As Worksheet If Application.UserName = "Name 1", "Name 2", "Name 3" Then Exit Sub For Each sht In ThisWorkbook.Sheets If sht.Name <>...
  16. D

    VBA to find and replace

    Sub FindReplaceAll() 'PURPOSE: Find & Replace text/values throughout entire workbook 'SOURCE: www.TheSpreadsheetGuru.com Dim sht As Worksheet Dim fnd As Variant Dim rplc As Variant fnd = "17-" rplc = "18-" For Each sht In ActiveWorkbook.Worksheets sht.Cells.Replace what:=fnd...
  17. G

    Horizontal / Vertical Alignment not Working

    For Each sht In ActiveWorkbook.Worksheets If (sht.Name = "Recovery") And (sht.Name = "Revisions") Then sht.Activate With Range("A7:AV3000") .Clear .NumberFormat = "General" .Interior.Pattern = xlNone Range("B6") = "Document Number"...
  18. J

    Copy and paste data from one sheet to another

    Hi guys, I tried to make this macros but I get the red code highlited in yellow when I run it, Do someone know why? Thanks for the help Sub CopyData() Dim LastRow As Long Dim Sht As Worksheet Worksheets("Datadump").Range("E:G,S:S,AW:AW,AY:AY,BE:BE,CC:CC,CF:CV").Copy Set Sht =...
  19. T

    What Tab is my Macro Crashing on?

    Sorry for the spelling error ^ Hi Everyone, I am using the following macro to hide rows throughout my workbook. In the pat it had worked perfectly, but just started crashing. is there a way to see what tab the macro crashes on? Maybe there is something you can add to the code. Thank you! Sub...
  20. T

    Marco Works... then does not.

    Hello Everyone, Question for you. I have been using the following code to hide rows based on a cell value. The macro has worked wonderfully, but today it crashes. Any thoughts would be much appreciated! Sub Hide_All_Rows() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In...

Some videos you may like

This Week's Hot Topics

Top