macro to copy data into 1 sheet and sort

**neowok** · 07-19-2013, 08:04 AM

Having some trouble with a couple of macros.

I have the attached file which different people populate each day with various data. What I need to do is create a macro which will blank the Master sheet (from row 4 downwards to keep the headers) then copy all of the individual sheets (for as much data that is on it, excluding rows where the Location column (J) is blank) into the Master sheet and then sort the master sheet into date order.

Then one more macro which will copy any rows between the 2 dates in cells K1 and L1 on the Master sheet into the export sheet.

I have attached 2 buttons to the master sheet for the Macros. I have had similar macros in the past a few years ago and they are relatively small and simple, but after going through them now I just can't work out what its doing well enough to make it work for this so any help would be appreciated.

thanks

**JBeaucaire** · 07-19-2013, 10:24 PM

For the first macro to work with autofilters, you'll need to fix the data on the on sheet that has fully blank rows all through the data. Make sure column A is filled properly all the way down all the data sets on all sheets so the Autofilter can "see" them all as a single set of data. The rows with REF errors will end up at the bottom.

Then this macro will do your consolidation:

Sub ConsolidateData()
Dim ws As Worksheet, wsMaster As Worksheet, LR As Long

Set wsMaster = Sheets("Master Sheet - Do Not Touch")
wsMaster.UsedRange.Offset(3).EntireRow.Clear
For Each ws In Worksheets
    If ws.Name <> wsMaster.Name And ws.Name <> "Export" Then
        With ws
            .AutoFilterMode = False
            .Rows(3).AutoFilter 10, "<>", xlAnd, "<>0"
            LR = .Range("J" & .Rows.Count).End(xlUp).Row
            If LR > 3 Then .Range("A4:A" & LR).EntireRow.Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Rows(3).AutoFilter
        End With
    End If
Next ws

With wsMaster
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A4:DA" & LR).Sort Key1:=.Range("B4"), Order1:=xlAscending
End With

End Sub

**JBeaucaire** · 07-19-2013, 10:29 PM

And the EXPORT macro:

Sub Export()
Dim LR As Long

Sheets("Export").UsedRange.Offset(3).EntireRow.Clear

With Sheets("Master Sheet - Do not touch")
    .Rows(3).AutoFilter
    .Rows(3).AutoFilter 2, ">=" & .Range("K1").Value, xlAnd, "<=" & .Range("L1").Value
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    If LR > 3 Then .Range("A4:A" & LR).EntireRow.Copy Sheets("Export").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Rows(3).AutoFilter
End With

End Sub

**neowok** · 07-20-2013, 05:29 AM

Thanks I think I managed to get everything to work, now to see how many ways the useless users can manage to break it

**JBeaucaire** · 07-20-2013, 12:32 PM

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.

**neowok** · 08-22-2013, 04:06 AM

Ok after using this for a while now I have come accross a couple of issues.

Firstly whenever clicking either of the macro buttons on the master or export sheets (which are virtually the same macro), it is adding blank but 'used' lines at the bottom of those sheets (so you can see the scroll bar getting smaller as additional 'used' rows are added). I am not sure what is happening but after using the sheet for a couple of weeks and users clicking the button loads of times, those sheets had a lot of 'used' but blank rows at the bottom of them, making the file bigger and bigger.

Secondly, somehow, users are ending up making 'used' columns for a few hundred columns sometimes on the named users sheets, this is not related to the macro as far as I can work out as it doesnt happen all the time so it must be something the users are doing (but I have no clue what!!). You can see this in the attached sheet on Tmo's sheet there are hundreds of extra used columns. We had this on a couple of sheets and due to the sheer number of colunms, it ended up crashing excel when trying to run the macro. I had to go in and delete all the extra columns they had somehow deleted. Is it possible somehow when the macro rnus on each sheet for it to check if a column has no text in it at all (because as far as I can tell, all those columns on Tmo's sheet have no text but are somehow being marked as used columns) and if so simply delete that column before copynig anything to the other sheets?

I think if anyone can help adjustig the macros to do that it might account for some of the users inability to use excel properly and stop them breaking the file!

Thanks

**JBeaucaire** · 08-22-2013, 01:53 PM

I've run the macros on those two sheets a myriad of times with many dates and ranges, from a single day to a 50 day range... I am unable to get "blank rows" to appear in the results.

**neowok** · 08-22-2013, 01:58 PM

the blank rows appear below the results not in them, it doesn't matter what the range is. You can see the scroll bar on the right getting smaller and smaller on the master and export sheets as more 'used' rows end up at the bottom after each click. I had to go in and delete about 400 of them then realised it was adding them every time the macro is clicked. on the master sheet, if I drag the scroll bar to the bottom, the last row I can see with my screen and resolution is row 69. if I click the macro then drag the scroll bar to the bottom, the last row is then 72. click again and the last row is row 75. It is adding 3 rows each time and I am guessing this is somehow related to the fact that the first 3 rows of any of the sheets are the header rows.

**JBeaucaire** · 08-22-2013, 02:51 PM

I'm not seeing that. When I run the macro, the sheet clears and all new data appears. The size of the scroll bar indicator is not something I've ever looked at. It does appear to fluctuate in size, but I'm not seeing blank rows using the workbook you uploaded.

**neowok** · 08-22-2013, 03:01 PM

Not sure you are looking at the right thing, it does exactly the same using excel 2010 at work, and excel 2013 at home. screenshots to demonstrate

the workbook I attached, before clicking the macro (dragging scrollbar down with the mouse stops at row 69)
before.JPG

After clicking the macro once (3 new rows have been added as seen by the scrollbar position even though nothing was touched other than one click of the macro. Dragging the scrollbar down now will stop on row 72)
one click.JPG

After clicking the macro once more (3 more new rows have been added, dragging the scrollbar down now will stop on row 75)
two clicks.JPG

after a couple of weeks of users clicking this, I had several hundred blank rows underneath the actual data which I had to delete since excel is seeing them as used rows it is also adding size to the file unnecessarily).

macro to copy data into 1 sheet and sort

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

macro to copy data into 1 sheet and sort

Re: macro to copy data into 1 sheet and sort

Re: macro to copy data into 1 sheet and sort

Re: macro to copy data into 1 sheet and sort

Re: macro to copy data into 1 sheet and sort

Re: macro to copy data into 1 sheet and sort

Re: macro to copy data into 1 sheet and sort

Re: macro to copy data into 1 sheet and sort

Re: macro to copy data into 1 sheet and sort

Re: macro to copy data into 1 sheet and sort

Thread Information

Users Browsing this Thread

Similar Threads

copy data to another sheet,filter it there,sort it,then copy it again onto another sheet

[SOLVED] OMG! how do I copy certain data from one sheet to another and then sort it.

Macro to Look for Sheet, create if none, sort items from master & copy to new sheet.

Macro to sort data,match,copy paste on to other sheet

Macro to sort,match.and copy paste on to other sheet

Bookmarks

Bookmarks

Posting Permissions