Posts by whazzzzzup17

    Hello, I wrote a macro to copy and paste data from one sheet to another sheet, however, it takes a good 30 seconds.


    Is there any tips on how I can speed this up?



    I have a code that copies many sheets to a new master workbook and then renames the sheets on the master file.


    However, all the cell links from the sheets copied over no longer work, because the page name has changed. How can I go about renaming the hyperlink string for the page name? These are just links from the cell.value to the location at where there are on a table of data.


    For example I have a small data set - lets say A1:A15 that have a name and have links to a table location on the same sheet based on each cells values.


    After copying the sheet to the master workbork and renaming the sheet on the master workbook, the hyperlink for the sheet name doesn't change. If I don't rename the sheet it works fine, but I need to keep the new names..


    Here's what I have so far

    Code
    Sub RenameHyperlinks()
    Dim i as Long
    Dim SheetCount as Long
    Dim ws as worksheet
    SheetCount = Sheets.Count
    i=11 'since I have 10 fix sheets and I add sheets after 10
    For ws = i to SheetCount

    Hello, I have this VBA script that converts the selected cell from a value to a formula, however, I need some help converting it to work on a RANGE of cells rather than a single cell.


    Basically, it looks up the value of the activecell - Lets say "5" within Range("B1:B1500") and outputs =B10 if the number 5 is in cell B10.


    Need to change it if I select 10 cells and it needs to lookup 10 cells individually.


    Trying to finalize a spreadsheet thatVBA code pulls Sheet1 from all files within a folder to my main sheet.


    I would like to modify the code to

    1. When it pulls Sheet1 from each file, it currently moves it to the main, but I'd like to rename it based on what's in cell AC3 for each Sheet1 from the multiple files
    2. If sheet already exists on the main, delete old and replace.

    I’m looking for some help in converting my current code into a range instead of a full sheet


    Instead of merging sheet 1 from multiple workbooks into a master workbook, I only need to copy a specific range on sheet one of each workbook and paste it within the master workbook. I need each sheet in the master workbook to have the original sheet name from sheet1 from the workbooks.


    I can open the file, but of course there's no VBA script in it as it has been saved as .xlsx.


    Correct. I have tried VBA in the past but didn't have luck. I attached the VBA to this forum on what I used. I'm not too experienced with VBA, and couldn't get it to work quick enough.


    I think VBA is the right approach though. I think the best way to solve the INDEX/MATCH/MAX lag delay is to somehow create a UDF that doesn't execute until the user presses a button.


    Other than that, I don't know how to make each cell stop referencing one another to cause the insert row and column lag. Although these 3 functions are not volatile, they still reference each other and any modification causing a delay.


    I'm lost and don't' know where else to turn. Any help will be appreciated.

    Please see attached. I have already created a VBA script, but I'm afraid it slows it down too much. I'm not too experienced with VBA and any help would be appreciated.


    Code
    Sub HSchedule()         For ColNum = 7 To 500 Step 2     Range(Cells(3, ColNum), Cells(1159, ColNum)).Formula = _     "=IF(R19C>100000,"""",IF(RC1=1,WORKDAY(INDEX(R18C[1]:RC[1],RC2),1+INDEX(SchedulesTable,ROW()-ROW(RC5),R5C[1]),Holidays)," & Chr(10) & "IF(RC1=2, MAX(WORKDAY(INDEX(R18C[1]:RC[1],RC2),1+INDEX(SchedulesTable,ROW()-ROW(RC5),R5C[1]),Holidays),WORKDAY(INDEX(R18C[1]:RC[1],RC3),1+INDEX(SchedulesTable,RC5,R5C[1]),Holidays))," & Chr(10) & "MAX(WORKDAY(INDEX(R18C[1]:RC[1],RC2),1+INDEX(SchedulesTable,RC5,R" & _     "5C[1]),Holidays), WORKDAY(INDEX(R18C[1]:RC[1],RC3),1+INDEX(SchedulesTable,RC5,R5C[1]),Holidays), WORKDAY(INDEX(R18C[1]:RC[1],RC4),1+INDEX(SchedulesTable,RC5,R5C[1]),Holidays)))))" & _     ""     Range("G19:G37").Select     Selection.Copy     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Next ColNum End Sub

    I’m in need of some help speeding up my Excel spreadsheet. I have been working on a solution for weeks, and I don’t know what else to do. I hoping someone can help me with a VBA solution as my schedule template doesn't change and is the same for all three tabs

    About

    I track hundreds of project schedules that use the same schedule outline template - meaning the same tasks. Microsoft Project is not a solution, as it doesn't allow me to track multiple schedules efficiently. I would like use Excel, but it lags a lot when I open the spreadsheet, but more importantly when I add and delete columns/rows, because of the amount of index match formulas.

    Spreadsheet

    There are 3 tabs – Actuals, Projections, Baseline.

    Actuals: Input the date a task is started and completed for each project. (This is the only tab that involves input)
    Projections: Calculates the projected start and end dates based on the actual start and completion date. (Index/Match formula with a max formula for each predecessor)
    Baseline: The original schedule projection based on just the start date of the projected. (Index/Match formula with a max formula for each predecessor)


    My speed issues are basically when modifying any columns/rows, because although index/match is not a volatile function, it has to refresh every time I modify Excel to re-focus on the new row and column.

    I have even gone through every formula and removed both Match functions and put in the exact row and column. Although this helped a lot, it wasn’t much help.


    Sample: https://ufile.io/wsalc