Posts by Exceelll

    Hello, as title suggests I need help on implementing a Macro that would autofilter loop trough my files and their sheets in a set folder path. The action it would perform would:

    1. The first step would be to open the file and autofilter a table. The column it would autofilter from is the 11 (Field) column / the last column in the table.
      1. The criteria for the filtering would be this symbol: "-".
      2. The workbooks also contain sheets that don't have any tables so it would probably need also to ignore them or skip trough if no table is found.
    2. Following that it would move to next sheet and perform the same action until done with all the sheets in the workbook. When done move to next workbook until every (Around 50) Excel workbooks are done.

    This is for invoice attachments, so It's important there are no extra "empty rows" and that their filtered. This would save me so much time. I'm a beginner at this and would appreciate any help towards this project . Thank you in advance


    Here's some relevant code that could help. Maybe combing these to solves the problem (I've tested the for each tbl in .Listobjects, but it did not work:


    Code: Autofiltering code
    Sub Autofilter()
    
    ActiveSheet.ListObjects(1).Range.Autofilter Field:=10, Criteria1:="-"
    
    End Sub


    Here:

    Code: Turn total row off for paste.value
    Sub Totalrow()
    
        Range("B2").Select
        ActiveSheet.ListObjects(1).ShowTotals = False
    End sub
    Code: Create Table from range
    Sub ConvertToTable()
    
      
      If ActiveSheet.ListObjects.Count < 1 Then
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("B7:M400"), , xlNo).Name = ActiveSheet.Name
      End If
    
    
    End Sub
    Code: Filter in new table
    Sub Autofilter()
    
    ActiveSheet.ListObjects(1).Range.Autofilter Field:=11, Criteria1:="-"
    
    End Sub
    Code: Select total row after table is done
    Sub Totalrow()
    
        Range("B2").Select
        ActiveSheet.ListObjects(1).ShowTotals = False
    End sub

    Hello, this might be a tough ask, but is it possible to make the code delete the entire row if a row on M:M has this symbol "-" (It appears if there's noting for the formula. There are few expiations, if it is a double line "--" don't delete, only if it's one line "-")?Because now I'm getting crazy amount of empty rows. Before the table did the filtering but the code removes it.


    Or maybe ithe code coule crate a new white table for the active sheet, range B7 to B 400 and then filter with this '-'. I got some good code for filtering and creating table I get back to this.


    Also I'm having trouble with the of xlsm files. The coded seems not to detect them.

    Hello, this work well! The problem I'm facing is with line 38. It works, but only if the workbooks has the sheet name otherwise the code will debug. If I for instance have pricing and Important in the list, but the workbook only has a pricing sheet, the code stops because it could not find a sheet named important. But if I only run the code with pricing in the list, it works well until it comes across a files with no pricing sheet. Hope you get what I'm going after.


    Is there a way to make the code scan trough the names listed (Pricing", "Cover", "Important", "notes). Then delete the ones that are in the workbook and not debug? Because one workbook has a sheet named important and another might have cover.

    line 23 to 28, line 48 + removing the warning when replacing a file with a same name are the changes I want. They don't work but hope this gives you the idea what I was looking for

    Second question was unrelated to this problem. In the second question I meant that I would need the copy files from point a to B with no editing to the files, so that it let's me choose the path. It was demo that was not working (I just removed row deleting and pasting special)


    The main problem was this for the macro u sent me:


    "I'm getting Error 9 - Subscript out of range .


    One thing I noticed is that is this: Because the sheet deleting happens before the pasting special, that will break the formula, right? Can be done so that it would run after all the pasting and deleting? When I removed the error part it worked great!


    I'm also getting errors if file is protected, can that be removed before macro is ran and a protected back on after it's done?"

    I'm getting Error 9 - Subscript out of range .


    One thing I noticed is that is this: Because the sheet deleting happens before the pasting special, that will break the formula, right? Can be done so that it would run after all the pasting and deleting? When I removed the error part it worked great!


    I'm also getting errors if file is protected, can that be removed before macro is ran and a protected back on after it's done?


    ________________________________________________________________________________________________________________________________________________________________


    After that is resolved how can the pasting files from point a to b be separated from all of that? I have another project I could use the call function for it that would be helpful. Is it possible to remove to warning symbol if the file already exists in current folder? Because the action this is going to be used for requires the files the replaced by fresh ones. Is there in a macro like a true or false place I can turn it of and on when the situation suits it?


    Sorry for the delay, I've been a bit busy. I've tested the files now and there's like 5 of 50 that won't work. Otherwise it works like a charm.

    I've tried searching where he error would be and come to a solution and modified the code to it. Here's the explanation:


    The macro didn't work because in the range of M to AA was information to close to the table (text, notes and random numbers like prices etc.). The range that will be deleted. When I removed the text from that range before running the macro it works for the Workbooks that before gave an error. The range that it needs to paste special is From B1 To M500. I believe that because it wasn't a specific range, but a usedrange, the macro got confused)



    - Delete a list of sheet names like (Pricing, Cover, Important, notes)


    Can the code below be modified to be functioned because they don't work / haven't found a solution to it?


    1. Delete all pictures

    -. shapes.delete doesn't work +

    2. Sheet deleting instead of avoiding (Delete Pricing, Cover, Important, notes)

    - I did not find code that would work for the sheet deleting for specific names. Maybe instead pf sheet voiding it would just delete them?


    Here's the modified code





    __________________________________________________________________________________________________________________________________________________________________

    Unrelated to this but would be useful in Call function combined with the previous macro is selecting a folder from point A where the files are and moving them to Point B where it would Paste special.


    I've got this Macro below and it work fine. I just don't know how to modify it. Basically now it requires a folder path hard coded into the code but I would like to work like Paste special macro that makes you select the folder. The only exception here is that it needs to choose from where and where to.

    Hello, I'm trying to implement a macro that would loop trough files in a folder you can select every time the macro is ran. Maybe someone could Help? I am not that familiar with code yet. The main task for it is to paste special the all the sheets (Some don't need) so that the formula disappears and after that delete columns N and forward and then move to the next workbook until it's done


    Task explained:

    I got a file of multiple (50) Excels that should sent to customers. For each one now I have pasted special so that the formula is removed from the cells and after that deleted unnecessary columns with sensitive information like pricing or notes. I would need the macro to first open a workbook in the selected folder. Then paste special (formula removed from whole sheet) and do that for each sheet in workbook with few exceptions. After that repeat the process but for deleting columns from N - AA for each sheet. After that close workbook and repeat for all files in the selected file.


    Here's some macros that I like and could be useful (Maybe combining them and making them compatible with each other would work?):

    Display Less
    Code: Avoiding certain sheet names