Posts by Halvar

    Re: Copy Data from Multiple Sheets to single Summary Sheet, based on criteria


    Jim, Alan is right in saying that it's somewhat hard to come up with the solution not knowing all the variables, but the code below might be a start. The code is looping through all the worksheets that are not named "Summary", copy cells A1:E1 and paste them into the first empty row on the Summary tab.




    Hope this helps!

    Re: Best PC Specs to Speed up Excel/VBA Calculation


    Quote from S O;728294

    Derk is absolutely right, I've seen code on this forum alone that has improved from about 1.5h runtime to just under 5 mins, using the HIRE HELP forum will be cheaper still than purchasing a new machine.


    Only advice I've ever had in terms of spec for Excel is to get an intel processor - apparently they're better at "number crunching" and more suited to spreadsheets and/or code that is running complex calculations.


    Hope that helps :)


    Thank you both for a speedy reply! Unfortunately, I believe the code is as efficient as it can get - it's mostly the size of the files and the nature of the work. For instance, some of the code needs to make a simple update, but it affects a lot of files, or needs to consolidate a lot of workbooks into one or push the data back to multiple files, or just deals with a massive amounts of lookup formulas etc. I am not sure there's anything that we can improve in that department - unless there's an option to pull or write data in Excel without opening the file that I'm not aware of? And thanks for the suggestion, I will look into intel processors for sure!

    Hello,


    Apologies if this is not the right place to post this question, but I couldn't find a better suited sub-forum. I deal with fairly large (sometimes up to 150 Mb) and complex Excel spreadsheets on a daily basis. This data is manipulated using both built-in formulas and VBA routines. Excel is my company's default tool because that's what the clients prefer to see and use, but we also use Access and occasionally SQL Server if necessary to do the work.


    Unfortunately, some of our VBA routines are starting to take forever to run in the new Windows 8/Excel 2013 environment, and with the source Excel files constantly growing in size it was decided to purchase a new desktop machine that we can use to tackle especially large files or complex models. Would anyone have suggestions as to the PC I should be looking into, which processor/memory are the best? The company is ready to make a pretty significant investment into this, so I am natually interested in what's best on the market.


    Thanks in advance!

    So I'm trying to change footers on all sheets in my workbook, set them to sheet name and make the font red. But, here's the catch: I don't need the whole name of the worksheet, just the part of it before space (" "). So, if the worksheet is named "1. Something" I just need the "1." part.


    The code I've got so far is below:




    So far I can either make random text red:

    Code
    WS.PageSetup.LeftFooter = "&KFF0000Text"


    OR I can set the footer to be the value of str variable:

    Code
    'WS.PageSetup.LeftFooter = "&KFF0000&str"



    Tried a coupe things, but cant make BOTH of those to work in conjunction. If anyone can help me that would be greatly appreciated!

    Re: Copy columns if the sheet name in source WB matches sheet name in target WB


    thank you sir! you're a genius. this works perfectly, I've just had to declare a new variable and tweak the next statement. I know this must be not the most efficient solution, but since I'm not working with hundreds of tabs I think it's acceptable.


    here's the final code in case anyone else needs it:


    Re: Copy columns if the sheet name in source WB matches sheet name in target WB


    pike, thanks a lot for taking the time to go through the code. The code I posted works fine, what I am actually trying to automate is the If ws.Name = ... Else If routine. Right now I need to repeat the same chunk of code for each new worksheet - right now that's 10 different worksheets.


    Instead of specifying the whole "if worksheet name in this workbook matches the worksheet name in the active workbook then copy data.." thing I want to create a loop that will go through all worksheets in both WB and compare them. If the name matches, I want the data to be copied over. Have you ever had to tackle a problem like this before?

    Re: Copy columns if the sheet name in source WB matches sheet name in target WB


    Thanks pike, I'd actually like to keep the ActiveWorkbook/ThisWorkbook references as the name of the import file is not static (I've got 10 WBs that I will be importing, so this seemed like the best way to go about it). Here's the full code, I've just stripped it of checks and messages that I use to check if the target WS is empty etc.:



    Hello,


    I know this must sound childish to VBA pros, but I am trying to automate data import in my workbook. In a nutshell, I'm trying to write code that will:


    1) let the user select the source file (already figured out this part);
    2) if one of the sheets in the selected source workbook matches the name of the sheet in my target workbook, copy columns A:S and paste them into my target workbook


    I've got the following code which works fine but I must specify sheet names for each individual sheets that need to be imported (so, basically repeat the same passage of code over and over again):


    Code
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "Sheet1" Then
                ActiveWorkbook.Sheets("Sheet1").Range("A:S").Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range("A:S")


    Ideally, I want something along the following lines:


    Code
    For Each ws In ActiveWorkbook.Worksheets
        If ActiveWorkbook.ws.Name = ThisWorkbook.ws.Name Then
              ActiveWorkbook.ws.Range("A:S").Copy Destination:=ThisWorkbook.ws.Range("A:S")
    Next



    Doesn't need to be a ready solution, but maybe you can point me out in the right direction? Thanks!

    Re: Clear contents in selected sheets


    Hi Smallman, and thanks for a quick reply. I've tried to use the solution you've posted, but unfortunately the results are somewhat different from what I've expected: the code selects all sheets, but clears contents only on the first one.


    I've played out with it just a bit and found the following way to achieve the result I was looking for:


    Code
    Sub ClearSheets()
        Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
        Cells.Select
        Selection.Clear
    End Sub


    This will select the 3 sheets that need to be cleared, and will remove all contents and formatting. In case someone else is looking for a similar solution, changing "Selection.Clear" to "Selection.ClearContents" will keep the formatting but will remove all the values.


    But thanks again for your help, it drove me in the right direction!

    Hello,


    I'm trying to clear all contents on 3 selected sheets (out of many worksheets I've got in my WB). So far I've got the following code (which works fine), but I was wondering if there's an easier way to get the same result:



    I was trying to see if it's possible to use Array, but couldn't find anything relevant so far. Would appreciate your help!