Copying or summarizing visible cells

[colbyjh]

Hello,

I am creating an excel sheet to allow supervisors to easily delegate work out to mechanics. The Work order software they use allows them to export into excel. I have created an excel sheet that has them past what is exported into the first page of the document and the rest of the document auto populates.

I have a sheet for each department and the cells are copied and formatted into the appropriate tabs automatically. The trouble I am having is that I did this by filtering out all but what I wanted that page to see. Now I am wanted to consolidate everything back into one page to show how many hours of work each mechanic has scheduled so that it can be easily seen who can handle more work.

A simplified example is below. line number is the row number in excel. Just showing that some lines are hidden due to a filter,


[TABLE="width: 500"]
<tbody>[TR]
[TD]line[/TD]
[TD]WO Number[/TD]
[TD]Hours Needed[/TD]
[TD]Mechanic[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]Fred[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]George[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]bob[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]bob[/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]Fred[/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]George[/TD]
[/TR]
</tbody>[/TABLE]


I love the idea of a pivot table, but the hidden cells make this not work. If there is a formula to copy all of the visible cells? I can make another table off to the side that will allow me to link all cells from every page into it and there will be extra blank space. I could then sort it so the blank spots are at the bottom and do a pivot table on that. The issue with this is I need it to autosort whenever data is added. I am not sure how to go about doing this.

Thank you for any insight you may be able to provide,

Colby Henson

 

[igold]

Hi Colby,


Since you have not provided any idea of the ranges you are working with, this code will look at your filtered sheet and copy the data without the header
row to Sheet2 starting in Cell A2. I am assuming that "WO Number is in cell A1 and "Mechanic" is in Cell C1.

Sub CpyVisble()
    ActiveSheet.Range("A1:C" & Cells(Rows.Count, 3).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Offset(1, 0).Copy Worksheets("Sheet2").Range("A2")
End Sub

HTH

igold

 

[colbyjh]

Hello,

I wanted to thank you for the reply. I am dealing with quite a large range with a varying amount of rows. How it works is data gets put into excel and I have a sheet for each Category. For example: Instrument Tech, Can Line Mechanic, Food Service Mechanic, Processing Mechanic, etc. The columns start at A1 go up to AP and I will have up to 300 rows.
Will this code run automatically? I have experience in other coding, but I have never used VBA in excel. I know how to get to the editor to type in, save, and run the code. I tried seeing if it would copy anything over as you typed the code so I could try to figure out how the code works, but it did not work. How would I alter the code to get it to copy over. How would I specify that I want the data coming from multiple sheets?

For example:
-Copy all not hidden cells that are not blank in the range A1:AP500 on sheets 1,2,3,4,5.

Thank you,

Colby

Hi Colby,


Since you have not provided any idea of the ranges you are working with, this code will look at your filtered sheet and copy the data without the header
row to Sheet2 starting in Cell A2. I am assuming that "WO Number is in cell A1 and "Mechanic" is in Cell C1.

Sub CpyVisble()
    ActiveSheet.Range("A1:C" & Cells(Rows.Count, 3).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Offset(1, 0).Copy Worksheets("Sheet2").Range("A2")
End Sub

HTH

igold

 

[SpillerBD]

I wonder if you should get the data before anybody else does their copying to segregate it into the various departments.
IF the data exported is formatted to go directly into a Pivot Table (that's always the big question when data coming from 3rd party source) you should be able to run the Pivot Table and have the Departments go into the Page Filter and then arrange the rest of the data normally in the PT.

As far as needing to auto sort the data.... why? The Pivot table will group and sort for you.

 

[colbyjh]

Hello,

Thank you for the reply. The data from the 3rd party software does not know what mechanics we have and does not allow for the input of the estimated completion time. The reason I have it set to sepererate the data first is so that the planner can easily see all of the jobs that are required for Food Service and see a list of all of the mechanics available. If it was all in one sheet, it would be one solid list of 200 rows. I believe I have stumbled across a solution involving macros. I have learned how to record a macro, but I need to figure out a way to have it run when cells are edited.

Currently this is what I have:

1. Data gets copied in from 3rd party software
2. Data gets automatically distributed among different sheets based on department via a copy and filter
   1. On these sheets the planner will put the estimated completion time and who will be performing the work
3. All data is copied over at cell CC250 to include 300-400 rows. This is well beyond what is needed, but since I do not know the exact number of cells to be copied, I went overboard.
4. For example:
   1. Food Service
      [TABLE="width: 500"]
      <tbody>[TR]
      [TD]Bob[/TD]
      [TD]8[/TD]
      [/TR]
      [TR]
      [TD]Fred[/TD]
      [TD]10[/TD]
      [/TR]
      [TR]
      [TD]George[/TD]
      [TD]6[/TD]
      [/TR]
      [TR]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      [TR]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      </tbody>[/TABLE]
   2. Can line[TABLE="width: 500"]
      <tbody>[TR]
      [TD]Larry[/TD]
      [TD]6[/TD]
      [/TR]
      [TR]
      [TD]Moe[/TD]
      [TD]12[/TD]
      [/TR]
      [TR]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      [TR]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      [TR]
      [TD][/TD]
      [TD][/TD]
      [/TR]
      </tbody>[/TABLE]

So I would copy cells A1:B5 from both of the above sheets and past them into a summary sheet so I'd end up with:​

[TABLE="width: 500"]
<tbody>[TR]
[TD]Bob[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Larry[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Moe[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This gap between George and Larry messes up any pivot table attempts. So I would have to sort by name to force the blanks to the bottom.
I have recorded the following macro that tells it to sort the data by name:

Sub sort()'
' sort Macro
' by resources
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    ActiveWorkbook.Worksheets("Summary of hours").AutoFilter.sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Summary of hours").AutoFilter.sort.SortFields.Add _
        Key:=Range("CL200:CL1500"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Summary of hours").AutoFilter.sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Now I would need to figure out how to make it run. Without having the planner have to do it manually. This will allow me to create a pivot table to encompass the entire potential range that will stop at the blank lines.

Thank you,

Colby

I wonder if you should get the data before anybody else does their copying to segregate it into the various departments.
IF the data exported is formatted to go directly into a Pivot Table (that's always the big question when data coming from 3rd party source) you should be able to run the Pivot Table and have the Departments go into the Page Filter and then arrange the rest of the data normally in the PT.

As far as needing to auto sort the data.... why? The Pivot table will group and sort for you.

 

[igold]

If you have a filtered sheet that has data from Cell A1 to Cell AP (however many rows of data there are in Column "A") this code will copy the visible cells only to another sheet sans header row, (I happened to choose "Sheet2") starting at Cell A2.

Please change the destination to wherever you would like. If you last column is not Column AP, change that as well.

Sub CpyVisble()
    ActiveSheet.Range("A1:AP" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Offset(1, 0).Copy Worksheets("Sheet2").Range("A2")
End Sub

This is as per your first post, revised to include up to Column AP

igold