Extracting Data by both Column and Rows

[Tyarra]

Okay, this is a bit beyond my limited VBA capabilities, it appears, even after searching, and finding bits and pieces here and there.

Situation is this:

I have a Summary sheet, which I've assembled from other sheets in the workbook, and it refers to yet another sheet for pricing info, using VLOOKUP.

Simple enough, to this point - but, there's columns that are labled A through Z, for possible locations that the goods on the list can go. It will be rare that all 26 locations will be used, and at least as rare that all the items on the list will be ordered, too.

What I would like to do is to extract the rows and the columns that have things ordered in them, and ideally write it to a new workbook.


___A___|___B_|__C___|__D___|_E_|_F_|_G_|_H_|_I
1_Item_|_Ttl_|_Prc__|_Ext__|_A_|_B_|_C_|_D_|_E
2_ABC _|__3__|_3.00_|_9.00_|_1_|___|_2_|___|__
3_EDC__|__1__|_1.00_|_1.00_|___|_1_|___|___|__
4_GHI__|__0__|_2.00_|_0.00_|___|___|___|___|__
5_RST__|__1__|_5.00_|_5.00_|___|___|___|___|_1


Above is a scaled down bit of the sort of data I've got - this example, I would want Columns A, B, C, D, E, F, G, and I and rows 1, 2, 3, and 5. Or, possibly, just eliminate Column H and Row 4. Either way.

Help?

 

[Tyarra]

Oh, I probably need to add this:

This file will be distributed to end users - and they are not Excel pros. I would like to be able to attach the code to a button, so that they can push it, make the other workbook (part of the file name is in a cell that they will enter, if that's possible to incorporate), and then they can email it.

The Summary sheet that needs to be reduced is full of formulae, but I would prefer to have just values in the new workbook.

Thanks!

 

[Tyarra]

Okay, I've tried quite a few things, and my current bit of code seems to be working ... mostly.

I first tried hiding columns and rows (which worked nicely), then copying, but that wasn't possible (can't copy over multiple ranges, was that error), and then I kept getting the formulae copied over ....

I'm now to the point where I have managed to get just the values copied into a new workbook, and am trying to get the rows and column to hide based on whether there is anything ordered in either.

Unfortunately, it insists on working on the original, though I have the second workbook activated!

Here's my code...

Dim c As Range
Dim CurrentFileName As String
Dim NewFileName As String
Dim WB As Workbook
Dim WB2 As Workbook

Set WB = ActiveWorkbook
CurrentFileName = ActiveWorkbook.Name
NewFileName = WB.Sheets("Summary").Range("A2").Value

WB.Activate
Sheets("Information").Select
Sheets("Information").Copy
Set WB2 = ActiveWorkbook
WB2.SaveAs Filename:=WB.Path & "\" & NewFileName
WB.Activate
Sheets("Summary").Activate
Range("A9:AH157").Select

Selection.Copy
WB2.Activate
Sheets.Add After:=WB2.Sheets("Information")
ActiveSheet.Name = "Summary"

     'Range("A1").Select  (See below as well ... not sure why, but this stops my code from running, and a box with "400" in it pops up.  Not exactly helpful.)

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

WB.Activate
Application.CutCopyMode = False

WB2.Activate
Sheets("Summary").Activate

    'Range("A1").Select   (Code would stop running here, and a box would show "400" - commenting it out, and it works on the original "Summary" sheet.)

Range("I139:AH139").EntireColumn.Hidden = False

For Each c In Range("I139:AH139")
  If c = "0" Then
  Columns(c.Column).EntireColumn.Hidden = True
  End If
Next c


Range("C1:C146").EntireRow.Hidden = False

For Each c In Range("C2:C138")
  If c = "0" Then
  Rows(c.Row).EntireRow.Hidden = True
  End If
Next c

WB2.Save

End Sub

Any help would be appreciated!

 

[Tyarra]

Well, I've spent a sleepless night, trying to find answers ...

There is nothing I can find that suggests why my hiding code won't work where I'm telling it to -- though, obviously, I'm not telling it in the right way, since it works on the original file.

I've even tried going ahead and hiding cells in place, and trying to use the SpecialCells(xlCellTypeVisible) on it ... but now it won't open the new workbook, I get that same 400 box.

 

[Tyarra]

I figured it out Finally.

I needed to specify "ActiveSheet" when stepping through my For each statements.

Thanks to all the many contributors to the thousands and thousands of posts that I've culled through. Couldn't have done it without you!