+ Reply to Thread
Results 1 to 19 of 19

Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

  1. #1
    Registered User
    Join Date
    10-04-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    2011
    Posts
    10

    Unhappy Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Hey guys! I'm new to the forum. I'm just getting into Macros, so you will have to go easy on me... I'm sort of just learning as I go. I'm trying to create a macro that will filter by some cell value, go to several worksheets, grab a certain column, and paste it into the main worksheet. I did it! It works... the only problem is that the file gets so huge that it crashes. It doesn't make sense! How can copying some columns from other sheets make the file almost 10x its size? One note that may (or may not) be helpful. When I deleted those columns where the info from the other sheets was pasted, the file size went back down to 8mb!! Weird as crap!! What in tarnation is going on. It crashes right toward the end of the macro... I'm assuming because it's getting too big.

    I posted the code below. Any help would be greatly appreciated!



    Sub TopBanks()

    ' Set up variables
    Dim r As Range
    Dim sheetFind As String
    Dim colFind As String
    Dim rFilter As String
    Dim rCol As Range
    Dim loop1 As Integer
    Dim tempSheet As String
    Dim rCol2 As Range
    Dim loop2 As Integer
    Dim adder As Integer

    ' Refer to cells to decide what data to pull
    colFind = Range("G1")
    adder = 0

    ' ------------------------------- Main Loop -------------------------------'

    ' Loop to grab information from each year's Sheet
    For loop1 = 2 To 9

    ' Each time through loop, sets the row equal to the value of loop1 (starts at 2008, then 2009, etc) and column 16
    ' Then assigns that to a variable that looks for a worksheet with the same name
    tempSheet = Sheets("Main").Cells(loop1, 16)

    ' ------------------------------- Inner Loop -------------------------------'
    ' Loop to grab information from each bank
    For loop2 = 2 To 12

    ' Filter is set to each bank, one by one
    rFilter = Sheets("Main").Cells(loop2, 7)

    ' Filters by the bank
    Set rCol = Sheets(tempSheet).Range("1:1").Find(What:=colFind, After:=Sheets(tempSheet).Range("A1")).EntireColumn
    rCol.AutoFilter Field:=1, Criteria1:=rFilter

    ' Copy/Pastes the data into row 1, column loop1+18 (moves 1 column each iteration)
    Set r = Sheets(tempSheet).Range("1:1").Find(What:=Sheets("Main").Range("B4").Value, After:=Sheets(tempSheet).Range("A1"))
    r.EntireColumn.Copy Sheets("Main").Cells(1, loop2 + 40 + adder)

    ' Unfilter worksheet
    Sheets(tempSheet).Cells.AutoFilter

    ' Takes you through the loop again
    Next loop2


    ' Changes the column where it pastes
    adder = adder + 12

    ' Takes you through the loop again
    Next loop1


    ' ------------------------------- End Loop -------------------------------'

    ' End Macro
    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,672

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    I think when you're copying an entire column and pasting into another sheet, Excel assumes that the entire column is now used, so the filesize balloons up. If you'll post a sample workbook and describe what you're wanting to accomplish I'll try to help you achieve your goal.

  3. #3
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,676

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Excel columns have 1 million rows. So you maybe copying 1 million cells. Figure out size of the column [cells with data] and copy only that.

  4. #4
    Registered User
    Join Date
    10-04-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    2011
    Posts
    10

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Hi guys, thanks for the quick responses!

    jomili - unfortunately, a lot of what I'm working with is confidential, so it's tough to post a sample, but I'm trying to grab the info in specific columns (based on a cell reference) and paste them into the main sheet. I think you're right in that it's pasting the entire column. Is there a way to just paste rows that have values?

    modytrane - 1 million rows?? The problem is that my row numbers will vary by sheet, and I want to make this to automate future sheets that may vary in size. Can I paste only rows that have values? Or just set it to only do 1000 rows or something?

    Thanks so much! I'm learning something already.

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,672

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    dgreelis,

    You don't have to paste your confidential info; whip up a sample workbook that we can work with.

    When you say "I'm trying to grab the info in specific columns (based on a cell reference)", I THINK what you're meaning is "I'm trying to grab the info from the top row to the bottom populated row in specific columns (based on a cell reference)". So, you need to figure out the LastRow, right?
    Please Login or Register  to view this content.
    So, again, if you'll post a sample workbook and describe what you're wanting to accomplish I'll try to help you achieve your goal.

  6. #6
    Registered User
    Join Date
    10-04-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    2011
    Posts
    10

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    jomili - gotcha. I think it's attached to this now. I made a small sample version of it. So here's what I'm hoping for:

    1. Enter variable of interest (which will be a column in the other sheets)
    2. Enter banks of interest
    3. Have the macro filter just those banks (right now in my code, this is the inner loop)
    4. Copy the info in the variable of interest for just the banks of interest
    5. Paste that data into the original sheet
    6. Repeat for each sheet (which are years in my sample... 2001, 2002, etc.)

    You're exactly right about what I meant to say... And I think I do need to figure out LastRow and just copy what's between the first row and last row after the filter is applied. I'm not very familiar with how the copying function works in macros... I only know the copy entire row/column, so I'm not sure how to copy just between first and last rows.

    Thanks again!
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,672

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Harder than I thought in a macro, but I think I've got what you need. See the attached. I've tried to fully comment the code, so you can see what's happening. You can F8 through the code to watch each step if you want.

    I included a function you might want to keep to reuse; it returns the column letter.

    I THINK you could this whole operation without VBA, just using some INDIRECT formula strings. I didn't pursue that, but if you're interested let me know.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-04-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    2011
    Posts
    10

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Wow, this is incredible! It will take me a while to go through all the code and try to understand it, but I will definitely get back to you when I'm done and let you know how it all goes.

    I had never heard of INDIRECT, but I just looked it up and it sounds like something that could work! I will let you know if that's something I'm interested in pursuing after I figure this macro out and apply it to my data set.

    Thank you so much for spending the time to create this. It will help me a lot!!

  9. #9
    Registered User
    Join Date
    10-04-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    2011
    Posts
    10

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    I keep running into an error. First I tried applying it to my big worksheet and I got an error, and then I applied it to the Sample and unfortunately got the same error. It says "Named argument not found" at this line:

    Set aCell = .Rows(1).Find(What:=Var, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

    Not sure exactly what that means. It's not finding the Variable? I changed the Sheet1 name to DataCollectorPage. I made sure the Variable name was Variable 1, and that it was spelled the same in both the sheets. Any idea what's going on?

    Thank you!

  10. #10
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,672

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    No, but if you want to attached the workbook that's going wrong I can take a look. I assume the sample workbook I provided works okay, it's when you try to move it to your Sample that it fails?

    For your Sheet Names, you can use whatever name you want, just redefine that name in the macro in the section below.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    10-04-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    2011
    Posts
    10

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Ok, here it is.
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,672

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    That was an easy one; your button was too big, so was interfering with a field we were using. But, while I have it, I want to do some work on this one to make it faster, especially for larger ranges. I'll get back to you soon.

  13. #13
    Registered User
    Join Date
    10-04-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    2011
    Posts
    10

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Oh hah... didn't know that was a thing. Good to know. Thank you!

  14. #14
    Registered User
    Join Date
    10-04-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    2011
    Posts
    10

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Actually, it still doesn't seem to be working when I shrink the button or move it anywhere. I'm still getting the same error message.

  15. #15
    Registered User
    Join Date
    10-04-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    2011
    Posts
    10

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Ok, I was able to get it to work by replacing that line of code with one that I had used in my old code, but everything else looks good!!! And it works!!!



    Set aCell = .Rows(1).Find(What:=Var, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

    became

    Set aCell = Sheets(tempSheet).Range("1:1").Find(What:=Var, After:=Sheets(tempSheet).Range("A1"))


    Maybe it just needed the After? Or the Range("1:1)? I'm not sure what exactly those mean but it worked!!! Never mind the tempSheet variable, as that is in my actual workbook with my data and such.

  16. #16
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,672

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Glad you figured it out. I've got a better solution for you. It's still not 100% the way I'd like it, but it's good. It's formula driven, and the formula is a hum-dinger. I'm about to leave for the day so can't give you a full rundown, but here's some highlights:

    Main formula is:
    Please Login or Register  to view this content.
    This is an Array formula, so to enter it you need to Control-Shift-Enter, otherwise it won't work.

    It relies heavily on INDIRECT formulas to populate sheet names.

    In essence, the formula looks at the right side of E2 to determine which sheet to look at, then looks at the left side of E2 to decide which Bank to look for, then looks at C2 to decide which variable to look for. Then we use the one/two punch of INDEX/MATCH to return the values.

    I have formulas in B14:16 that list the worksheets in the wb. I want to figure out how to use those and values in A26:28 to autoset the data to collect but haven't figured a formula method to do that yet.

    Change the values in C2:C8 to see how the formula updates. I think it's better than the macro approach.
    Attached Files Attached Files
    Last edited by jomili; 10-07-2016 at 08:35 AM.

  17. #17
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,672

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Okay, I think we've got it all for you now.

    I found a weakness in the main formula and fixed it in the sample. Since these super-long Excel formulas are really lots of little formulas combined, sometimes the little ones need tweaking. So, In the original formula we had these two small formulas
    Please Login or Register  to view this content.
    which returned the last 4 digits (the WS name) and the first 6 digits (the bank name). But your bank name might be longer than 6 digits, or you might have "BankA" instead of "Bank A", or the worksheet may be called "Source" instead of "2001" so may be longer than 4 characters, so the formulas above weren't really scalable. So, these two formulas were replace with
    Please Login or Register  to view this content.
    I've added two little macros for you use.

    The formulas and one of the macros depend on knowing how many worksheets there are, and what their names are. So, I've got a formula route in B11:B13 (you can drag further down if you have more sheets) which will populate the sheet names, then I have another cell that counts those names. IF you don't like the formula route, the ListWS macro does the same thing (but puts them in A1 and on down).
    In the attachment, I've color-coded green all the cells that are used to do the calculations. There's a few things going on:
    1) In C2, you'll enter the variable you want to look for
    2) In C4-8, you'll enter the banks you want to use (you can expand this range, just need to adjust the formulas and the "Data2Collect" macro.
    3) If you run the "Data2Collect" macro, it will populate the values in E2 and rightward, for the number of needed columns.
    I did NOT add a routine to add the formulas under those columns, but that can be easily done if you want it.
    Attached Files Attached Files

  18. #18
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,672

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Okay, I think we've got it all for you now.

    I found a weakness in the main formula and fixed it in the sample. Since these super-long Excel formulas are really lots of little formulas combined, sometimes the little ones need tweaking. So, In the original formula we had these two small formulas
    Please Login or Register  to view this content.
    which returned the last 4 digits (the WS name) and the first 6 digits (the bank name). But your bank name might be longer than 6 digits, or you might have "BankA" instead of "Bank A", or the worksheet may be called "Source" instead of "2001" so may be longer than 4 characters, so the formulas above weren't really scalable. So, these two formulas were replace with
    Please Login or Register  to view this content.
    I've added two little macros for you use.

    The formulas and one of the macros depend on knowing how many worksheets there are, and what their names are. So, I've got a formula route in B11:B13 (you can drag further down if you have more sheets) which will populate the sheet names, then I have another cell that counts those names. IF you don't like the formula route, the ListWS macro does the same thing (but puts them in A1 and on down).
    In the attachment, I've color-coded green all the cells that are used to do the calculations. There's a few things going on:
    1) In C2, you'll enter the variable you want to look for
    2) In C4-8, you'll enter the banks you want to use (you can expand this range, just need to adjust the formulas and the "Data2Collect" macro.
    3) If you run the "Data2Collect" macro, it will populate the values in E2 and rightward, for the number of needed columns.
    I did NOT add a routine to add the formulas under those columns, but that can be easily done if you want it.

    Let me know if you need anything else on this.

  19. #19
    Registered User
    Join Date
    10-04-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    2011
    Posts
    10

    Re: Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!

    Wow, this is amazing. It works very quickly and very easily without any huge macro needed! I'm excited to apply this to my data set! Awesome, thank you so much. I'll let you know how it works when I try it with my data!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro for Exporting to PDF makes file too large
    By oiltech999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2016, 04:58 PM
  2. VBA Importing Text File makes the file longer to load everytime
    By Hudas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2014, 11:26 AM
  3. Replies: 4
    Last Post: 08-31-2012, 11:52 AM
  4. Editing makes file slower
    By footloose in forum Excel General
    Replies: 4
    Last Post: 04-05-2012, 01:11 AM
  5. Macro / VBA that makes list's
    By grinch247 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2011, 08:45 AM
  6. Shorter formulae makes file 3X larger
    By catshoes in forum Excel General
    Replies: 1
    Last Post: 10-16-2008, 01:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1