Need a VBA Macro get data from one file to another + calculation

So this is what I took from that:
Code: 
[FONT=&quot]       'work with each unique     [/FONT]
 [FONT=&quot]   For j = 0 To d.Count - 1[/FONT]

  [FONT=&quot]        If d.keys()(j) <> "" Then[/FONT]
  [FONT=&quot]            crit = d.keys()(j)[/FONT]

  [FONT=&quot]            '"L" total for this MS[/FONT]

  [FONT=&quot]            Ltot = Application.WorksheetFunction.SumIf(.Range("H6:H" & lr), crit, Range("L6:L" & lr))[/FONT]
  [FONT=&quot]            'filter for this MS[/FONT]
  [FONT=&quot]            filtRng.AutoFilter Field:=1, Criteria1:=crit[/FONT]
  [COLOR=#008000][FONT=&quot]            'put L total in last[B] P[/B] showing[/FONT][/COLOR]

  [FONT=&quot]            .Cells(GetFilteredRangeBottomRow, [B]"P"[/B]).Value = Ltot[/FONT]

  [FONT=&quot]        End If[/FONT]

  [FONT=&quot]    Next j[/FONT]
  [FONT=&quot]   [/FONT]
  [COLOR=#008000][FONT=&quot]    'remove last filter[/FONT][/COLOR]

  [FONT=&quot]    filtRng.AutoFilter[/FONT]

  [COLOR=#008000][FONT=&quot]    ' show results[/FONT][/COLOR]

  [COLOR=#008000][FONT=&quot]    ' filtRng.AutoFilter Field:=4, Criteria1:=">0" (I Commented this out rather than remove in case i misread)[/FONT][/COLOR]

  [FONT=&quot]End With[/FONT]
  [FONT=&quot]Application.ScreenUpdating = True[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]End Sub

Is this correct?

[/FONT]
 
Yes, but..... I don't know what you're working with.

In the last Sup-DSTool.xlsm you posted, the "DSTool" sheet has a new column C being used for Time, so the rest of the columns got shifted, what was "C" is now "D", what was "H" is now "I", etc, etc. Everything up to this point that was using specific columns will need to be adjusted.
 
ok. Thanks. I'll play with it and see. Sequentially speaking, the CasePick Macro happens before the time column gets created and at this point Excel appears to just automatically push everything over by one column; however, that is not with this code above. I'll check it out.
 
NoS,
I took the 3 pieces of code you gave me Sub IdentifyLastRow(), Sub ToggleFilter(), and the Rick Rothstein function and put them into their own Module. I then created two control buttons one that runs "Identify" and the other "Toggle". As in your statement above I am running a function that moves everything over one column from "C" on. So this makes what was "H" "I". I also setup the creation of a "values only" copy of "I" in to Column "P". That being said, I edited the code in "Identify" so that it was looking at "I":
Code: 
With Sheets("DSTool")
    lr = .Cells(Rows.Count, "I").End(xlUp).Row
    Set rng = .Range("I7:I" & lr)
    Set filtRng = .Range("I6:I" & lr)

Now when I run it the debug screen opens, highlights this line

Code: 
[COLOR=#ff0000].Cells(GetFilteredRangeBottomRow, "P").Value = "display"[/COLOR]

and tells me "Application defined or Object defined error".View attachment PERSONAL.XLSBView attachment Sup-DSTool.xlsm
 
I think you're creating headaches for yourself trying to make things work from your personal.xlsb file without first getting them to work from the Sup-DSTool.xlsm workbook itself.

I took what you posted in #34 and 35 and had a go at getting to what I think you're after, starting from square one.
I've never used personal.xlsb file. In my mind that prevents a file from running anywhere other than on one computer.
Now I know that's not exactly true and may or may not be your intent but I'd be ignoring it until after things work.

Here's what I came up with.

View attachment DSTool_Tester.xlsm

Import of raw data is handled by adding a temporary sheet for manipulation.
Conditional formatting is what you had, only column and range adjusted.
Nothing is existing for the Finalize button.
All code is in a single module. See if things do what you expect.


Here's the workbook I used for the other 3 pieces of code you refer to View attachment robertwp.xlsm
 
Nos,
I absolutely agree. I am a complete novice when it comes to writing the deep magic for excel. Your help has been excellent and I have begun to learn a bit more about what I am seeing when I look at the code but still far from being able to visualize the outcome while I am typing. It was always my intent to consolidate and clean up the code. The Macro recorder, even though it works, makes real messy code. I was trying to keep it all separate to make sure the bugs weren't crossing over. I realized early that the Personal.xlsb thing was 500lb ankle weight but I was truing to get it all to function first.

Again, Thank you for all of your help. I will look at these attachments and let you know.
 
NoS,

I just got a chance to run that, I like that the finalize button is no longer needed. The only issue is that the toggle sees the blank Master Ships as duplicates and hides them as well; those are actually Unloads and not Loads and need to remain un-hidden.

Would there be a way to add a line of code to the toggle function that makes it ignore the row if Column "A" has a value of "U"? Or if easier; If the vale of 'I" = nothing then skip. The only thing that needs to be hidden is the duplicate Masters where the conditional formatting turns the cell green in "I".

Other than that it works perfectly. As such, can I delete the old Personal.xlsb? or put a .old in the file name so it doesn't try to run every-time I open Excel?
 
Yes you can get rid of the personal.xlsb
I suspect it was a result of not knowing where to store recorded macros and choosing 'personal macro workbook' instead of 'this workbook'. No big deal.

The only issue is that the toggle sees the blank Master Ships as duplicates and hides them as well; those are actually Unloads and not Loads and need to remain un-hidden.

Would there be a way to add a line of code to the toggle function that makes it ignore the row if Column "A" has a value of "U"? Or if easier; If the vale of 'I" = nothing then skip. The only thing that needs to be hidden is the duplicate Masters where the conditional formatting turns the cell green in "I".
Click to expand...
Not sure I follow this. I believe the first part says to show all the rows that are blank in column I and the second part qualifies that by adding if column A = U. If I have that right, row 28 having A=L and I blank would go against the first part as would a bunch of rows from 310 on. Can you clarify as to exactly what's required ?

I noticed in the last personal.xlsb you posted the range being worked with in the CPreport file is changed. This tells me you are now working with a different file, can you attach it so we're both working with the same things. Thanks.
 
Ditching personal.xlsb. Thanks. all i did on the CPreport was delete the headers so that data now starts at B1 instead B4, otherwise it's the same reportView attachment CPReport.xlsx
I had a thought about the the question I sent last night. I realized that when I was doing this all manually, I would remove the Duplicates by using the Appt# Column which is now "E" removes the same lines and should work the same way, but it has unique values for the unloads whereas Column "I" has blanks. I went into code and changed the parameters from I to E and now it does not remove the Unload rows. :becky:
 
Yup, that should work.

Here's what I ended up with.
 

Attachments

  • DSTool_Tester.xlsm
    77.8 KB · Views: 7
Perfect! :becky:

The only thing left is a few aesthetic changes:
1.) Auto-hide "M" since it is only a helper Column that provides the data for "L"; or change everything in "M" to white so it is invisible.
2.) Set the Text color of "P" to White so it effectively goes invisible.
3.) Set Text in "L" to Bold & Center alignment, but turn off thick borders for "L".
4.) Set Default Sort to be by "B,A,C,E,F" but also add a Sort button so the sheet can quickly be returned to this setup if manually sorted in a different way for another purpose (ie. I sometimes manually sort by "F" only in order to quickly find a row or rows that will align together when done.)
 
See if this does the trick.

Added another helper column in order to sort back to original state.
I'm not a fan of making text invisible. That always seems to come back to bite me. I'd rather hide an entire column.
I changed the P column to N and added O as a third helper column and hide all 3 helpers together, M, N and O. This keeps the columns contiguous so nothing goofy should happen with a quick manual sort.
The sort procedures are what the macro recorder recorded, just replaced the last row with the lr variable to make them reusable.

View attachment DSTool_Tester.xlsm

Hope this works out OK.
 
You must log in or register to reply here.
Back
Top