Extracting Data by both Column and Rows

Tyarra

New Member
Joined
Aug 23, 2006
Messages
23
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?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Tyarra

New Member
Joined
Aug 23, 2006
Messages
23
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

New Member
Joined
Aug 23, 2006
Messages
23
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...

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

New Member
Joined
Aug 23, 2006
Messages
23
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.

:oops: :rolleyes:
 

Tyarra

New Member
Joined
Aug 23, 2006
Messages
23
I figured it out :oops: 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!
 

Forum statistics

Threads
1,137,341
Messages
5,680,922
Members
419,945
Latest member
Carrie Sellers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top