Cell Referencing Issues when Sort By and Retrieving Data based on Dates

[00Able]

I want to copy an entire worksheet into another workbook, basically I create a new workbook, we will call it Master Log, and I have 2 other workbooks called Log1 and Log2, ok so I want to take the essential data out of Log1, and the essential data out of Log2 and import it directly into this "Master Log" workbook.

So it sounds easy, I copy the format to the new work book, open a new worksheet name it similiar to the one I want to copy and then go to cell A1 and say =Log1 (Master)!A1 and then drag across and down, so all the cells are cell referenced....and then I repeart this process into another sheet and viola, it works, everything looks pretty good; however, because all the cells are referenced, I can't sort by data, as there is all equations in the cells....ugh

So I have a couple of issues, first of all,

is it possible to delete all cell referencing but keep the initial data, without having to copy/paste special it manually into another worksheet? Is there a macro that will perform this task? Keep in mind, users are going to continually use the other worksheets, and I want to show the results into this master sheet, but at the same time, I want to be able to sort through this data.

Is there a cleaner way to copy a page, without all these cell references?On a similar note, is it possible to have a dynamically copy similar to dynamic ranges, so as you add data the work sheet,(keep in mind this data is pulling from 2 seperate workbooks) it automatically updates the page? Because of the formatting of the cells, when I have a Date and once I surpass the rows being used, it returns the 1/1/1900 in column A down about 5000 rows, and 0's in the rest, and when I try to sort the data, by date, everything is thrown off?!?

Now as you can see in the example, my front page (with much assistance from jaslake) is able to pull data by date, so if I want to look at one date or a week of data, I have the flexibility to do that, but it only works, if these cells are not formatted with the "references", as you can see. If you would take the information from Log1 (master) and copy it directly into Log1 and do the same for Log2, you will see it will work flawlessly. Just do not delete these 2 sheets or then the macro will be "wacked"...then you will need to start over with the original file.

Also, I was thinking about putting some active buttons at the top of the page, that would sort the information by columns without having to go to the sort option. So if you clicked on cell B2, it would sort by Line, if you clicked on C2 it would sort by SKU, if you clicked on cell D2 it would sort by Error Description, etc. I had played around with the thought of adding a Tab Strip, and trying to add the macros to this and naming each Tab a "sort by" field, but only to save space...not sure if any of this will affect the outcome of what I would like to accomplish in the long term...so figured I would ask in advance if this will affect how the outcome?

Ok as always, any suggestions tips ideas advice, is appreciated, until then I will keep moving forward with this on my own, and update my progress...Thanks

[jaslake]

Hi 00Able

In your previous thread, we weren't working with cell references, we were working with values. Had we been working with references, I'd have suggested

Worksheets("FRONT PAGE").Range("A" & LR).PasteSpecial Paste:=xlPasteValues

This may well address this issue

keep the initial data

I'm not clear what you're looking for here (I got rather involved in your prior post so I had a pretty clear idea where you were going) but see if the above code snippet does solve one issue. If not, let me know and I'll look at it.

You've got a lot of issues in this thread; see if the above resolves the Data issue. I'll be glad, as will many others on the Forum, to help with the other issues.

John

[00Able]

You've got a lot of issues in this thread; see if the above resolves the Data issue. I'll be glad, as will many others on the Forum, to help with the other issues.

I know, I am trying my best to only deal with one issue at a time in a post, so that it stays structured and I stay within the forum rules, but at the same time, I want to give an understanding of the final project, in its entirity, so we can prepare for other issues that we may face, as I didn't think cell referencing would pose a problem...yeah a bit niave on that aspect..its all a learning curve... I will play with this and see what happens, will post more as I find out.

I'm not clear what you're looking for here (I got rather involved in your prior post so I had a pretty clear idea where you were going

Until them I will try to explain more clearly what I am hoping to accomplish. I have 2 shifts working, I have an identical folder layed out for both shifts with many excel workbooks for every department, and covering every aspect of production. It is my desire that all the information stays uniform, and both shifts are keeping the same records. I want to be able to retrieve the data they input, without affecting their ability to enter those files and make changes, as there are lots of files and lots of users. So I thought, the best way to do this would be to cell reference to those sheets in a "Master" workbook, and then I would be able to access the data I wanted whenever I needed without interferring with a users access. Since I do a lot of data analysis, I was thinking it would be nice to be able to sort the information into a front page,with just the dates that I want, and then to make things even easier, if I could then add macros to the top to sort by the columns. Also, this workbook will be copied and used in several similiar instances of course some will have more columns then others, the layouts will be similiar.

I know it might sound trite to add a macro to sort data, but I believe a good manager should spend more time managing and developing employees then managing spreadsheets and scorecards. So I am looking for an approach that allows me to find a few extra minutes to be on the floor. As you can tell, I spend a lot of my off time, working to find these solutions. I know how to add macros, and have confidence this in general wont be hard, however, the more I got to thinking about it, is if I had one prebuilt into this sheet would save me time from having to add it to every copy of this worksheet I make. My only concern would be how many columns would I need, ok well I will start with 10 and hope that is enough, however it will probably be more like 15, so I am going to have to look at that in depth. Just not to sure if the Tab thingy will work for that, or if I am overshooting it? Seemed like it might be a good idea. I hope this helps you understand some of what I am trying to accomplish.

[jaslake]

Hi 00Able

Hope you're making progress. You're in the real world; I'm in a fantasy world (retired...don't have many responsibilities other than Family). I don't have deadlines; I don't NEED to make things work.

Keep me posted. Be happy to help out where I can.

You'll need to give feedback as to what helps and what doesn't.

John

PS: I'll look at Test Tracker 11.xls and make recommendations as to how to bring Values over. Still not clear if this will resolve the issue.

[jaslake]

Hi 00Able
Took a look at the code for Public Sub PullData()

Changes are underlined (see lines 310, 500 & 670)

Public Sub PullData()
          Dim fRng As Range
          Dim LR As Long
10        Application.ScreenUpdating = False
20        Flag = True
30        Flag1 = True
40        With Sheet1
50            If Sheet2.TextBox1.Value = "" Or Sheet2.TextBox2.Value = "" Then
60                MsgBox "Enter a start and end date"
70                Sheet2.TextBox1.Value = ""
80                Sheet2.TextBox2.Value = ""
90                Application.ScreenUpdating = True
100               Exit Sub
110           End If
120           If DateValue(Sheet2.TextBox1.Value) > DateValue(Sheet2.TextBox2.Value) Then
130               MsgBox "End Date must be greater than or equal to Start Date"
140               Sheet2.TextBox1.Value = ""
150               Sheet2.TextBox2.Value = ""
160               Application.ScreenUpdating = True
170               Exit Sub
180           End If
190           .AutoFilterMode = False
200           .Columns("A:A").AutoFilter Field:=1, Criteria1:=">=" & Sheet2.TextBox1.Value, Operator:=xlAnd _
                      , Criteria2:="<=" & Sheet2.TextBox2.Value
210           With Sheet1.AutoFilter.Range
220               If .Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 = 0 Then
230                   Sheet1.AutoFilterMode = False
240                   Flag = False
250                   GoTo NextSheet
260               End If
270               Set fRng = .Offset(1, 0).Resize(.Rows.Count - 1, 11) _
                          .SpecialCells(xlCellTypeVisible)
280           End With
290           LR = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
300           fRng.Copy
310           Worksheets("FRONT PAGE").Range("A" & LR).PasteSpecial Paste:=xlPasteValues
320           Application.CutCopyMode = False
330           .AutoFilterMode = False
340           Flag = True
350       End With
NextSheet:
360       With Sheet4
370           .AutoFilterMode = False
380           .Columns("A:A").AutoFilter Field:=1, Criteria1:=">=" & Sheet2.TextBox1.Value, Operator:=xlAnd _
                      , Criteria2:="<=" & Sheet2.TextBox2.Value
390           With Sheet4.AutoFilter.Range
400               If Not .Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 = 0 Then
410                   Set fRng = .Offset(1, 0).Resize(.Rows.Count - 1, 11) _
                              .SpecialCells(xlCellTypeVisible)
420                   Flag1 = True
430               Else: Flag1 = False
440                   Sheet4.AutoFilterMode = False
450                   GoTo SortRecords
460               End If
470           End With
480           LR = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
490           fRng.Copy
500           Worksheets("FRONT PAGE").Range("A" & LR).PasteSpecial Paste:=xlPasteValues
510           Application.CutCopyMode = False
520           .AutoFilterMode = False
530           Flag = True
540       End With
SortRecords:
550       With Sheet2
560           If Flag = True Or Flag1 = True Then
570               LR = .Range("A" & .Rows.Count).End(xlUp).Row
580               Range("A2:K" & LR).Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
                          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
590           Else
600               MsgBox "No Records Found"
610               Sheet2.TextBox1.Value = ""
620               Sheet2.TextBox2.Value = ""
630           End If
640       End With
650       Flag = True
660       Flag1 = True
670       Columns("A:A").NumberFormat = "m/d/yyyy;@"
680       Application.ScreenUpdating = True
End Sub

The code will pull ACTUAL VALUES into Front Page rather than cell references. Let me know how things progress.

John

[00Able]

I have made the above changes and it seems to be working flawlessly. I am going to start playing with the tabs or exploring other options to set up the macros.

[jaslake]

Hi 00Able

Alrighty then! Let me know how I can help.

John

[00Able]

Is there a code I can enter at the end of the macro to have it put a grid around only the information being displayed? Because now that we are using the Special Paste, values only, it will not bring the original format (grid) with it?

Another concern will be, I want to use this for multiple purposes. This one is illustrating errors, and it has 11 columns, however when I have a page that uses 12 or 13 columns, it will only pull the first 11 columns, how can I tweak that?

[jaslake]

Hi 00Able

I've added a couple lines of code to deal with this

it will not bring the original format (grid) with it

and added/modified some lines to deal with this

when I have a page that uses 12 or 13 columns, it will only pull the first 11 columns

The code will now pull as many columns of data that exist in the source worksheet

Public Sub PullData()
    Dim fRng As Range
    Dim LR As Long
    Dim LC As Long
10  Application.ScreenUpdating = False
20  Flag = True
30  Flag1 = True
40  With Sheet1
41     LC = .UsedRange.Columns.Count
50      If Sheet2.TextBox1.Value = "" Or Sheet2.TextBox2.Value = "" Then
60          MsgBox "Enter a start and end date"
70          Sheet2.TextBox1.Value = ""
80          Sheet2.TextBox2.Value = ""
90          Application.ScreenUpdating = True
100         Exit Sub
110     End If
120     If DateValue(Sheet2.TextBox1.Value) > DateValue(Sheet2.TextBox2.Value) Then
130         MsgBox "End Date must be greater than or equal to Start Date"
140         Sheet2.TextBox1.Value = ""
150         Sheet2.TextBox2.Value = ""
160         Application.ScreenUpdating = True
170         Exit Sub
180     End If
190     .AutoFilterMode = False
200     .Columns("A:A").AutoFilter Field:=1, Criteria1:=">=" & Sheet2.TextBox1.Value, Operator:=xlAnd _
                , Criteria2:="<=" & Sheet2.TextBox2.Value
210     With Sheet1.AutoFilter.Range
220         If .Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 = 0 Then
230             Sheet1.AutoFilterMode = False
240             Flag = False
250             GoTo NextSheet
260         End If
270         Set fRng = .Offset(1, 0).Resize(.Rows.Count - 1, LC) _
               .SpecialCells(xlCellTypeVisible)
280     End With
290     LR = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
300     fRng.Copy
310     Worksheets("FRONT PAGE").Range("A" & LR).PasteSpecial Paste:=xlPasteFormats
311     Worksheets("FRONT PAGE").Range("A" & LR).PasteSpecial Paste:=xlPasteValues
320     Application.CutCopyMode = False
330     .AutoFilterMode = False
340     Flag = True
350 End With
NextSheet:
360 With Sheet4
361     LC = .UsedRange.Columns.Count
370     .AutoFilterMode = False
380     .Columns("A:A").AutoFilter Field:=1, Criteria1:=">=" & Sheet2.TextBox1.Value, Operator:=xlAnd _
                , Criteria2:="<=" & Sheet2.TextBox2.Value
390     With Sheet4.AutoFilter.Range
400         If Not .Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 = 0 Then
410             Set fRng = .Offset(1, 0).Resize(.Rows.Count - 1, LC) _
                   .SpecialCells(xlCellTypeVisible)
420             Flag1 = True
430         Else: Flag1 = False
440             Sheet4.AutoFilterMode = False
450             GoTo SortRecords
460         End If
470     End With
480     LR = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
490     fRng.Copy
500     Worksheets("FRONT PAGE").Range("A" & LR).PasteSpecial Paste:=xlPasteFormats
501     Worksheets("FRONT PAGE").Range("A" & LR).PasteSpecial Paste:=xlPasteValues
510     Application.CutCopyMode = False
520     .AutoFilterMode = False
530     Flag = True
540 End With
SortRecords:
550 With Sheet2
560     If Flag = True Or Flag1 = True Then
570         LR = .Range("A" & .Rows.Count).End(xlUp).Row
580         Range("A2:K" & LR).Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
                    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
590     Else
600         MsgBox "No Records Found"
610         Sheet2.TextBox1.Value = ""
620         Sheet2.TextBox2.Value = ""
630     End If
640 End With
650 Flag = True
660 Flag1 = True
670 Columns("A:A").NumberFormat = "m/d/yyyy;@"
680 Application.ScreenUpdating = True
End Sub

Let me know if this works for you.

John

[00Able]

The formatting looks great now, Thanks!

Ok, I took a screenshot of the original file notice how it ends at column K, then I took a screen shot of the one you just provided, notice the difference. I made all the changes that you showed, but my screen still looks like the origninal, which poses no problem for this layout, but if I need to add columns it will. What changes am I missing?

[jaslake]

Hi 00Able

In one workbook, columns to the right of K are hidden. In the other workbook, they're not. Unhide the columns and see if that resolves the issue.

John

[00Able]

never realized you could Ctrl+Shift+Right, then Hide all columns, thats a nifty trick that I am going to keep in the bag, I like it.

Well, I have a compatibility issue between Excel 2010 and 2003. Ok, I made 10 macros, to sort by columns, when I make the macros, I use the Record Now mode, then I highlight the cells I want to sort by, press ctrl+shift+down, then I go to sort, and choose the column I want to sort by, I then click on an empty cell, and hit stop recording. All is good,it works fine. My problem, is when I take this to 2003, it will not work, as Excel 2010 has more rows then 2003, so the Macros return an error out of range. Ok so then I have to recreate all the macros using the 2003 program, deep sighs. This again sounds trite, but usually I do not have the extra time to do this at work. Is there a way I can do this without manually scrolling down to the max row level that 2003 offers?

[jaslake]

Hi 00Able

Are we still talking about "Front Page" when you say you want to

sort by columns

Tell me what you'd like to do. This can all be handled, regardless of what version of Excel you're running (said the blind man, not certain of 2010, don't have it). Do you want options to sort on ANY column? Do you want to sort on only specific columns?

Let me know.

John

[00Able]

I have been attempting to upload my updated file to show you, and been trying to revise that last post, so let me see if I can do it now, I have been having issues on this site for the past hour...nope won't let me attach it

I added a tabs at the top of my page for labeled each column name, and then tied a macro inside it. to sort by that field, the site won't allow me to attach an excel file, but it will let me show a picture, so I will show you what I did...but...yes, all I want to do is sort the information on the "Front Page", nice to do it with all columns, again, as I plan on using this sample for lots of worksheets in the future, and do not know what columns are where...so I will just do it to all columns and I will have it if I need it, right?

[jaslake]

Hi 00Able

Yeah, I've been having issues with the forum tonight also (like walking through mud). Pictures don't really help ME. Others, they might. My mind doesn't work that way.

Your file is getting rather large (although the last file wasn't huge). Try zipping the file then attach. I'd prefer to work with YOUR ideas rather that interjecting mine. It's YOUR workbook.

John

[00Able]

Sure enough you were right again...my file was under 800, so I thought I was good...lol

Again, the macros should work in your version, if not let me know, as I also have 2007 excel that I run on my laptop, I just hate my laptop, as I do not like typing on it...I don't understand everyones fascination with them, ok it comes in handy when traveling, but seriously thats about it...alright enough ranting...let me know your thoughts on how I can change the macros...

[jaslake]

Hi 00Able

HA!!! I abandoned my desktop well over two years ago. Only use it when I need to test Office 2000 applications with Outlook 2000 (can't run more than one version of Outlook on the same platform).

Have TWO laptops running here! Vista and Office 2007 on one, XP and Excel 2000, Excel 2007 on the other. Gives me lot's of flexibility to help with issues like yours.

Alright, as you said, "enough ranting". I'll look at this perhaps tonight. If not, in the AM.

I like the "concepts" you come up with. We'll see if we can make them work.

John

[jaslake]

Hi 00Able

I've only looked at your code briefly tonight as it's well past my bedtime. I can tell you this. I don't believe this will work in Excel 2003

 With ActiveWorkbook.Worksheets("FRONT PAGE").Sort
        .SetRange Range("A2:J3994")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

I've run into that issue MANY times working between Excel 2000 and Excel 2007. The good news is that it can be fixed. Not tonight.

John

[jaslake]

Hi 00Able

Change your sort routines to look like this for each of your buttons

Sub sortSKU()
    Dim LR As Long
    Dim LC As String
    LR = Range("A" & Rows.Count).End(xlUp).Row
    LC = ColumnLetter(ActiveSheet.UsedRange.Columns.Count)
    Range("A2:" & LC & LR).Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

This will work in all versions of Excel (I think).

John

[00Able]

I have made all the changes based on what you show, but I get an error, shown in the attachement with the ColumnLetter part of the code?
Any thoughts?

[jaslake]

Hi 00Able

Sorry about that. I forgot to post this

'From http://www.freevbcode.com/ShowCode.asp?ID=4303
Function ColumnLetter(ColumnNumber As Long) As String
  If ColumnNumber > 26 Then
    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter
    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')
    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function

Add this code to the bottom of Module2.

John

[00Able]

That seemed to be the fix, I needed. I will put this on this into action tomorrow and see how it works in the "real world", I will keep you posted.

Thanks so much for all of your assistance!

[00Able]

Ok, it appears to be working pretty good. Only cosmetic issues between 2003 and 2010...nothing we can do about that..., time to post another challenge...
Thanks so much!