Posts by Royzer

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi. The code in the bottom code window will copy the first sheet of all .xls files that reside in a single folder into a master workbook. For example:

    Fpath = "S:\Accounting\News NOP\
    Fname = Dir(Fpath & "*.xls")

    I need to find a way to get through several different folders on a website to reach different files. An example of the path would be "" This folder would have several files in it but I would need to open and copy the first sheet of the only one that begins with the string "Film" (ie, Film_Source_File_WBFO.xls).

    There would be several folders, all like the one above except with different TV station call letters where that one has WBFO.

    For example:


    The folder at the end of each path would have a source file labeled "Source_File_(station name).xls

    Is there any way to modify this code to do this, or another way to do it? Thanks! :smile:

    Re: If Statement

    Give this a try. It works for me:


    You should be able to add the other 9 cell addresses to the three "OR"'s above (I think).

    Re: Scroll workbook to Hyperlinked cells

    Which Excel version are you on? If it's 2010, this should work. Go to:


    Scroll down to near the bottom of the page where you see "Lotus Compatibility" and check the box beside "Transition Navigation Keys".

    Re: combine 4 if statements

    This may not be very pretty, but it works the way I understand you're looking for. Enter this formula into C3:


    Re: VBA - ignore certain columns when combining worksheets

    Thanks, Pike. The code ran fine. The data in column "K" of the source files pasted into column "J", so we're one column closer to what I am looking for. The ideal result would be for the data from column K in the source files to be pasted to column H of the Master file sheet with that column being the last one with data. That would have the same effect as what I was looking for in the original post, but just phrased differently.

    Basically, I am trying to copy these columns from the source files:

    A - G, K

    and past them to the master file sheet as:

    A - H

    Re: VBA - ignore certain columns when combining worksheets

    Quote from pike;625406

    Hello Royzer

    one way is with VData as union of ranges

    Thanks Pike. I replaced my section of code with yours and this line is showing in Red:

    vData.Copy Destination:=.Cells(nLastRow + i, "A"). ''' change Resize to suit rows and columns required


    This is part of the code that copies a range of cells from each file and pastes it beneath the last row of data in a sheet in the master file. Right now it includes all columns from A:K. I would like for it to ignore H, I, and J, but can't figure out how to do it. I would really appreciate any help I can get with this.

    Re: VBA-file download that will overwrite any existing files

    Quote from LocalPeople;625224

    If the file has the same name, you'll get the same 'overwrite existing file?' dialog that you would if you'd mannually save-as-ed the file with the same name as an existing file. You can suppress this (and all other) warnings with

    application.displayalerts = false

    . Note this will suppress all 'are you sure' messages, and it doesn't auto-reset when the code stops, so make sure to reset it after you've done, and in any error-handling.

    Sorry, just one more thing: where exactly do I need to put this code, at the end?

    I found this code on the web and adapted it to download files from our share point site. Periodically I will have to re-download the same files into the same folder. I would like for them to overwrite the existing files with the same name. Is there something I can add to this code that will do that? Thanks!

    I have a formula that is pulling the substring "OUT" from strings that include it, but the formula is returning a "$" when it looks at a string that does NOT have the substring "OUT".

    For example:

    income from retransmission agreement - 16,381 subs OUT DMA @ $0.38 per sub (returns "OUT")

    income from retransmission agreement - 5,262 subs @ $0.45 per sub (returns "$")

    The formula is:

    =IF(H187=0,"",TRIM(RIGHT(SUBSTITUTE(LEFT(H187,FIND("subs",LOWER(H187)&"subs")+7)," ",REPT(" ",999)),999)))

    Is there a way to return " " for strings that do not contain "OUT"?



    I am using this formula:

    =IF(H23=0,"",--TRIM(RIGHT(SUBSTITUTE(LEFT(H23,FIND("subs",H23&"subs")-1),"- ",REPT(" ",999)),999)))

    to pull the number of subs from strings like this:

    income from retransmission agreement - 684.5 subs

    I'm having a problem though, because even though the string technically is the same, sometimes I run into cells that have the word "subs" in upper case and the formula returns #VALUE.

    income from retransmission agreement - 684.5 subs returns 684.5

    income from retransmission agreement -684.5 SUBS returns#VALUE

    I'd appreciate any help you can give me.


    I have an application that will generate a report each month and export it to Excel. The problem is that there are no options to change the layout of the export and I need to get some of the information into a different format to be reviewed by the "higher-ups". The exported file will have over 2,000 rows each month, so the idea of manually transferring this information is overwhelming (and, I daresay, impossible).

    I prepared a file to upload to show you, but I can't get the site to upload it. Maybe it is too big. Here are screenshots of the two example worksheets: