delete rows from multiple tabs if cells in specific column is blank

[balaji3081]

Hi, Need urgent help is this, have to submit the file by monday -


this is how I have the file

- On the 'Main' tab there to list criteria's which need to be selected, once selected the formula in the rest of the tabs (Actuals, Budget,Headcount,LY Actuals) marks the relevant rows.

Now I need a macro to -

1)Delete all the rows in which col E is empty.(leaving tab with data for the department selected in 'Main' tab cellC3 )

2)refresh the piviot.

3) Save it in a specific folder.

4)Save it with the name as Data 'Main C3' &" -"& 'Main C5' & previous month -13 (e,g - Data LAX01_Australia - All - May-13)

 

[Debraj]

Hi Balaji,


Welcome to the forum,


Wait.. wait.. two days is enough.. we will solve this or help you to solve you..


Please read the below..

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting


match if above your post is matching with all the rules provide there..


Then Please introduce yourself..

http://chandoo.org/forums/topic/welcome-to-phd-forums


after completing Please comeback with a Sample file.. so that we will solve this as early as possible..

without sample.. it may cross MONDAY.. and we dont have any problem if it cross deadline..


Regards,

Deb

 

[balaji3081]

Hey Deb , Aplogies on the Tags, also good to know how to share files,


Below is the link to the file on Dropbox


https://www.dropbox.com/s/w7zaz1a934z4584/sample%20file.zip


If this is replied asap, I can start working on 75 files Which I have to produce.


Rgeards,

Bala

 

[Debraj]

Hi bala!


Please check the below.. Let us know if you have any further query..


https://dl.dropboxusercontent.com/u/78831150/Excel/delete-rows-from-multiple-tabs-if-cells-in-specific-column-is-blank%28Balaji%29.xlsb

[pre]

Sub GetData()
With Sheets(1)
ParentDept = Left(.[c3], 5): dept = .[c5]
fname = "" & .[c3] & " - " & .[c5] & " - " & _
MonthName(Format(Date, "m") - 1, True)
End With
Sheets(Array(2, 3, 4, 5, 6)).Copy
Set deb = ActiveWorkbook
With deb
For i = 5 To 2 Step -1
With Sheets(i).Range("A1:F" & Sheets(i).Range("A" & Sheets(i).Rows.Count).End(-4162).Row)
If Not Sheets(i).AutoFilterMode Then _
.AutoFilter
.Value = .Value
.AutoFilter 6, "="
.Offset(1).SpecialCells(12).EntireRow.Delete -4162
.AutoFilter
End With
Next i
With Sheets(1).PivotTables("PVTPrntDept")
.PivotCache.Refresh
.PivotFields("L5_PARENT_DEPT2").CurrentPage = ParentDept
.PivotFields("DEPT2").CurrentPage = dept
End With
.SaveAs ThisWorkbook.Path & fname & ".xlsx"
.Close False
End With
MsgBox "File Created & Placed in Same Folder.."
End Sub

[/pre]

Regards,

Deb

 

[balaji3081]

Hey Deb,

Saw the post right now, thanks for the file works like magic.....will try that on my file.


Will update you on that.


Thanks a ton


Regards,

Bala

 

[balaji3081]

Hey Deb,


I need the 'Main' Tab as well in all the files, will that be easy???


Regards

 

[Debraj]

Hi Balaji,


Re-Download the file from same location..


Regards,

Deb

 

[balaji3081]

Hi Deb,


The macro works fins on the sample file but not on my file , the auto filter need to be applied on Col Z i.e. 26, the col which needs to be filtered in 4 tabs does not a fall in the same number.


Hope you get it.

 

[balaji3081]

Hi Deb,


I am banking you a lot,


https://www.dropbox.com/s/s2fvrnvj17c4epx/Sample%20Format%20File%20%28No%20data%29.zip


Above is the link to my file , I have deleted all the data, hope that is fine with you,

So that you get an idea of the format and the tab manes, as I was not able to use the macro you provided in my file.


PL TAB - the selection are made in Cell G3 And G5, based on which the formula fills relavent cell in the data tabs, and also populates the numbers in that tab.


Details Tab , FMNO Driven Expenses , Project Driven Expenses are all Pivot table


RDW Actuals , BUDGET - FY13-FPA , FTE , RDW LY Actuals , are all data tabs with numbers


Lookup & refrrance tabs are necessary tabs are are need in all the files.


Here is what I am looking for -


1)I need all the tabs as it is.


2) Macro to delete rows where the SUB - Department ( available in all the data tabs) to be deleted.


RDW Actuals - Col Z,

BUDGET - FY13-FPA - Col O

FTE - Col K

RDW LY Actuals Col H


3) The macro should run after I make the selection in Cell G3 & G5.


4) No Need of saving it as I will save it manually.


My intention was to make it simpler , apologies if I have complicated, If this is solved on a ASAP basis will be very helpful.


Also I will be using the same Macro on other files.


Regards,

Bala

 

[balaji3081]

Hi,


Any feedback or solution....???

 

[Debraj]

Hi Balaji!


I support you privacy & security of data.. but due to huge customization & random requirement changes.. I would like to view sample Random data for the same.. atleast to debug.. & verify..


Its really hard to debug.. as I am completely blank & I have only Header..


Can you please add some Random data.. to check.. or

You can count me to secure your data & you can directly mail me sample file to my personal mail ID.. "mail3debraj[at]gmail[dot]com"


Regards,

Deb

 

[balaji3081]

Hi Deb,


Did you get my Mail???

 

[Debraj]

Hi Bala!


You know.. Office, Family.. So I was not able to work on this.. I hope you can understand..


Please check your mail..

[pre]

Sub GetData()
With Sheets(1)
ParentDept = Left(.[G3], 4): dept = Left(.[G5], 4)
'        fname = "" & .[c3] & " - " & .[c5] & " - " & _
'            MonthName(Format(Date, "m") - 1, True)
End With
Sheets.Copy
Set deb = ActiveWorkbook
With deb
For i = Sheets.Count To 2 Step -1
With Sheets(i)
Select Case .Name
Case "RDW - Actuals", "BUDGET - FY13-FPA", "FTE", "RDW LY Actuals"
Sheets(i).Select
Set hdr = .UsedRange.Find(What:="SUB- DEPT", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
With .UsedRange.Offset(1)
If Not Sheets(i).AutoFilterMode Then _
.AutoFilter
.Value = .Value
.AutoFilter hdr.Column, "="
.Offset(1).SpecialCells(12).EntireRow.Delete -4162
.AutoFilter
End With
End Select
End With
Next i
Sheets("Details").PivotTables("PivotTable6").RefreshTable
Sheets("FMNO Driven Expenses").PivotTables("PivotTable1").RefreshTable
Sheets("Project Driven Expenses").PivotTables("PivotTable2").RefreshTable
End With
MsgBox "File Created! Please save.. "
End Sub

[/pre]