date:20130427

RE: $$Excel-Macros$$ Filter based on coulmns

2013-04-27 Thread Amit Desai (MERU)

You are right.

Can you please suggest some macro for that?

Best Regards,
Amit Desai

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On 
Behalf Of Paul Schreiner
Sent: 26 April 2013 17:17
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Filter based on coulmns

Your original statement is not entirely accurate.
Excel's Autofilter is techically applying a filter to ROWS based on the value 
in a COLUMN (or value of a cell in that row)
What it is actually doing is simply hiding any row that does not meet the 
filter criteria for a specific column.

What you SEEM to be asking is if you can hide columns based on the value of 
cells in a ROW.

Excel has no built-in functionality to do that.

Your options are:
Copy and paste/transpose the data into another sheet and apply AutoFilter.

Or write a macro that hides columns based on your criteria.


Paul

-
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-



From: Amit Desai (MERU) 
mailto:amit.de...@merucabs.com>>
To: excel-macros@googlegroups.com
Sent: Fri, April 26, 2013 7:11:12 AM
Subject: RE: $$Excel-Macros$$ Filter based on coulmns
Can someone please answer?

Best Regards,
Amit Desai

From: excel-macros@googlegroups.com 
[mailto:excel-macros@googlegroups.com] On Behalf Of Amit Desai (MERU)
Sent: 22 April 2013 18:15
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Filter based on coulmns

Dear Friends,

In general we filter data based on the contents in rows. I would like to know 
if we have any options to filter data columns.

Could you please help.

Best Regards,
Amit Desai



Disclaimer: This message and its attachments contain confidential information 
and may also contain legally privileged information. This message is intended 
solely for the named addressee. If you are not the addressee indicated in this 
message (or authorized to receive for addressee), you may not copy or deliver 
any part of this message or its attachments to anyone or use any part of this 
message or its attachments. Rather, you should permanently delete this message 
and its attachments (and all copies) from your system and kindly notify the 
sender by reply e-mail. Any content of this message and its attachments that 
does not relate to the official business of Meru Cab Company Pvt. Ltd. must be 
taken not to have been sent or endorsed by any of them. Email communications 
are not private and no warranty is made that e-mail communications are timely, 
secure or free from computer virus or other defect.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to 
excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to 
excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




Disclaimer: This message and its attachments contain confidential information 
and may also contain legally privileged information. This message is intended 
solely for the named addressee. If you are not the addressee indicated in this 
message (or authorized to receive for addressee), you may not copy or deliver 
any part of this message or its attachments to anyone or use any part of this 
message or its attachments. Rather, you should permanently delete this message 
and its attachments (and all copies) from your system and kindly notify the 
sender by reply e-mail. Any content of this message and its attachments that 
does not relate to the official business of Meru Cab Company Pvt.

Re: $$Excel-Macros$$ Filter based on coulmns

2013-04-27 Thread Paul Schreiner

So... you're saying copy/paste traspose isn't an option and you want a macro.

do you know anything about macros?

or are you wanting someone to write one for you?

either way, if you want help with a macro, you'll have to provide WAY more 
information than you've given.
 
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-





From: Amit Desai (MERU) 
To: excel-macros@googlegroups.com
Sent: Sat, April 27, 2013 4:34:49 AM
Subject: RE: $$Excel-Macros$$ Filter based on coulmns


You are right.
 
Can you please suggest some macro for that?
 
Best Regards,
Amit Desai
 
From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On 
Behalf Of Paul Schreiner
Sent: 26 April 2013 17:17
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Filter based on coulmns
 
Your original statement is not entirely accurate.
Excel's Autofilter is techically applying a filter to ROWS based on the value 
in 
a COLUMN (or value of a cell in that row)
What it is actually doing is simply hiding any row that does not meet the 
filter 
criteria for a specific column.
 
What you SEEM to be asking is if you can hide columns based on the value of 
cells in a ROW.
 
Excel has no built-in functionality to do that.
 
Your options are:
Copy and paste/transpose the data into another sheet and apply AutoFilter.
 
Or write a macro that hides columns based on your criteria.
 
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
 
 



From:Amit Desai (MERU) 
To: excel-macros@googlegroups.com
Sent: Fri, April 26, 2013 7:11:12 AM
Subject: RE: $$Excel-Macros$$ Filter based on coulmns
Can someone please answer?
 
Best Regards,
Amit Desai
 
From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On 
Behalf Of Amit Desai (MERU)
Sent: 22 April 2013 18:15
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Filter based on coulmns
 
Dear Friends,
 
In general we filter data based on the contents in rows. I would like to know 
if 
we have any options to filter data columns. 

 
Could you please help.
 
Best Regards,
Amit Desai
 
 



Disclaimer: This message and its attachments contain confidential information 
and may also contain legally privileged information. This message is intended 
solely for the named addressee. If you are not the addressee indicated in this 
message (or authorized to receive for addressee), you may not copy or deliver 
any part of this message or its attachments to anyone or use any part of this 
message or its attachments. Rather, you should permanently delete this message 
and its attachments (and all copies) from your system and kindly notify the 
sender by reply e-mail. Any content of this message and its attachments that 
does not relate to the official business of Meru Cab Company Pvt. Ltd. must be 
taken not to have been sent or endorsed by any of them. Email communications 
are 
not private and no warranty is made that e-mail communications are timely, 
secure or free from computer virus or other defect.
-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE : Don't ever post confidential data in a workbook. Forum owners and 
members 
are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 
 



Disclaimer: This message and its attachments contain confidential information 
and may also contain legally privileged information. This message is in

Re: $$Excel-Macros$$ Create Sheets by given name.........

2013-04-27 Thread priti verma

Hi Kuldeep,
You can also try this.
Sub CreateSheet()

For Each rngCell In Selection
ThisWorkbook.Worksheets.Add.Name = rngCell.Value
Next rngCell

End Sub


Regards
Priti verma


On Fri, Apr 26, 2013 at 7:26 PM, Kuldeep Singh wrote:

> Hi Experts,
>
> I want to create sheets by given name (Via VBA). anyone please help me.
>
> Regards,
> Kuldeep Singh
> Info Edge India Limited (naukri.com)
> Phone.: +91-0120-4841100, Extn.: 2467, 9716615535
> naukrikuld...@gmail.com || www.naukri.com
> *Please* *Consider the environment. Please don't print this e-mail unless
> you really need to.*
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

$$Excel-Macros$$ Copy/Paste Problem

2013-04-27 Thread Bill Q

Hi, 

When copying and pasting from a particular site in question - for whatever 
reason it will not copy the color. Is there anyway to keep the original 
color ? 

The link is 

*https://miseojeu.lotoquebec.com/en/results/complete-results*
 

Then: 

1. Press on "complete results" on the left hand side. 
2. Select sport "hockey" 
3. Select sub-category "National" 
4. Below that - select sub-category "Matches" 
5. Choose time period "January 19, 2013" 

Ok results will come up. As you can see there "green" in certain boxes. 

When copy and pasting - I cannot find a way of keeping the "green". 

Not sure if these can be solved with VBA, but any help or suggestions would 
be much appreciated. 

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: $$Excel-Macros$$ Create Sheets by given name.........

2013-04-27 Thread Bé Trần Văn

1/ Create one sheet

Sub CreateSheet()
   Worksheets.Add
End Sub

2/ Remove 1 sheet named "THANH", and create a new one sheet called "TRUNG"
Sub CreateSheet()
  On Error Resume Next
  Application.DisplayAlerts = False
  Sheets("THANH").Delete
  On Error GoTo 0
Worksheets.Add
   ActiveSheet.Name = "TRUNG"
End Sub

2013/4/27 priti verma 

> Hi Kuldeep,
> You can also try this.
>  Sub CreateSheet()
>
> For Each rngCell In Selection
> 
> ThisWorkbook.Worksheets.Add.Name= 
> rngCell.Value
> Next rngCell
>
> End Sub
>
>
> Regards
> Priti verma
>
>
>  On Fri, Apr 26, 2013 at 7:26 PM, Kuldeep Singh 
> wrote:
>
>> Hi Experts,
>>
>> I want to create sheets by given name (Via VBA). anyone please help me.
>>
>> Regards,
>> Kuldeep Singh
>> Info Edge India Limited (naukri.com)
>> Phone.: +91-0120-4841100, Extn.: 2467, 9716615535
>> naukrikuld...@gmail.com || www.naukri.com
>> *Please* *Consider the environment. Please don't print this e-mail
>> unless you really need to.*
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
>  --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: $$Excel-Macros$$ Copy/Paste Problem

2013-04-27 Thread David Grugeon

I think you will Nero download the source code and go through it to extract
the indicator (probably a CSS class) that turns a particular item green.
 It probably could be done in VBA but a big exercise.

On Sunday, 28 April 2013, Bill Q wrote:

> Hi,
>
> When copying and pasting from a particular site in question - for whatever
> reason it will not copy the color. Is there anyway to keep the original
> color ?
>
> The link is
>
> *https://miseojeu.lotoquebec.com/en/results/complete-results*
>
> Then:
>
> 1. Press on "complete results" on the left hand side.
> 2. Select sport "hockey"
> 3. Select sub-category "National"
> 4. Below that - select sub-category "Matches"
> 5. Choose time period "January 19, 2013"
>
> Ok results will come up. As you can see there "green" in certain
> boxes.
>
> When copy and pasting - I cannot find a way of keeping the "green".
>
> Not sure if these can be solved with VBA, but any help or suggestions
> would be much appreciated.
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com  'cvml', 'excel-macros%2bunsubscr...@googlegroups.com');>.
> To post to this group, send email to 
> excel-macros@googlegroups.com 'excel-macros@googlegroups.com');>
> .
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>


-- 
Regards
David Grugeon

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

$$Excel-Macros$$ Loop issue

2013-04-27 Thread knowledgeforexcel

Hi Experts,

i have come across an issue where in auto filter code  is skipping over 
without performing the required action and running next course of action.

I Have highlighted the line where am getting error can any one correct me 
where am facing this issue.

Macro is executing well but this particular lines it is not performing the 
action.


ActiveSheet.Range("A101:AY" & k).AutoFilter Field:=51, Criteria1:="<>" 
& FileName
 Rows("102:" & k).Select
Selection.Delete Shift:=xlUp
Range("A101").Select
Selection.AutoFilter


*complete code*

Public Sub OPEX_Reporting()

Dim Master As Workbook' Macro and Master Workbook
'Dim Linked As Workbook' CCA P&L Linked File
Dim NewFile As Workbook   ' New File

Dim macro, CCA As Worksheet   ' Worksheets in Macro and Master Workbook
'Dim Report, CON As Worksheet  ' Worksheets in CCA P&L Linked File
Dim sh As Worksheet   ' Searching for sheets

Dim rng As Range

Dim i, j, k, l As Double
Dim Outerloop, Innerloop As Integer

Dim PATH, PATH1  As String
Dim FileName As Double
Dim FileNamesave As Double

Set Master = ThisWorkbook

Set macro = Master.Sheets("Macro")


i = macro.Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False
Application.DisplayAlerts = False

PATH = macro.Range("C4")

Dim Answer As String
Dim MyNote As String

'Place your text here
MyNote = "You are about to run the Macro. Are you Sure?" & Chr(13) & 
Chr(13) & "If Yes! Make sure the below Path is empty - " & Chr(13) & 
Chr(13) & PATH

'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Macro Confirmation 
Message !!!")

If Answer = vbNo Then
'Code for No button Press
'MsgBox "You pressed NO!"
End
Else
'Code for Yes button Press
'MsgBox "You pressed Yes!"
End If

For Outerloop = 8 To 10
j = macro.Cells(Outerloop, Columns.Count).End(xlToLeft).Column


FileName = macro.Cells(Outerloop, 1)


Set NewFile = Workbooks.Add

For Innerloop = 3 To j
Dim CC As String

CC = ""
CC = macro.Cells(Outerloop, Innerloop)
Set sh = Master.Worksheets(CC)
sh.Activate
ActiveSheet.Copy before:=NewFile.Sheets(1)
k = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

NewFile.Sheets(CC).Select
Cells.Find(What:="Mapping Data", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False).Activate

'If Sheets(CC).FilterMode = True Then
'Selection.AutoFilter
'Else
'Selection.AutoFilter
'End If


 If CC = "Transaction Details" Then
ActiveSheet.Range("A101:AY" & k).AutoFilter Field:=51, Criteria1:="<>" 
& FileName
 Rows("102:" & k).Select
Selection.Delete Shift:=xlUp
Range("A101").Select
Selection.AutoFilter
ElseIf CC = "Phased Actuals" Then
ActiveSheet.Range("A49:AY" & k).AutoFilter Field:=51, Criteria1:="<>" & 
FileName
Rows("50:" & k).Select
Selection.Delete Shift:=xlUp
Range("A49").Select
Selection.AutoFilter
ElseIf CC = "Summary" Then
Rows("11:11").Select
ActiveSheet.Range("$A$11:$BF$19" & k).AutoFilter Field:=50, 
Criteria1:="<>" & FileName
Rows("12:" & k).Select
Selection.Delete Shift:=xlUp
Range("a11").Select
Selection.AutoFilter
'ElseIf CC = "Nat Exp Vs Plan" Then
'ActiveSheet.Range("A94:AH" & k).AutoFilter Field:=35, Criteria1:="<>" 
& FileName
'Rows("95:" & k).Select
'Selection.Delete Shift:=xlUp
'Range("A94").Select
'Selection.AutoFilter
'Else
'ActiveSheet.Range("A94:AH" & k).AutoFilter Field:=34, Criteria1:="<>" 
& FileName
'Rows("95:" & k).Select
'Selection.Delete Shift:=xlUp
'Range("A94").Select
'Selection.AutoFilter
End If

Next
NewFile.Activate
On Error Resume Next
NewFile.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
NewFile.SaveAs FileName:=PATH & "\" & FileName, 
FileFormat:=xlOpenXMLWorkbook
NewFile.Close
Next
macro.Activate
MsgBox "You have run the Macro Successfully!!!"

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Thanks & Regards,
Ram





-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum

Re: $$Excel-Macros$$ Loop issue

2013-04-27 Thread David Grugeon

You have highlighted 5 lines

Try stepping through the code and finding out which line does not work as
required.



Regards
David Grugeon



On 28 April 2013 16:25,  wrote:

> Hi Experts,
>
> i have come across an issue where in auto filter code  is skipping over
> without performing the required action and running next course of action.
>
> I Have highlighted the line where am getting error can any one correct me
> where am facing this issue.
>
> Macro is executing well but this particular lines it is not performing the
> action.
>
>
> ActiveSheet.Range("A101:AY" & k).AutoFilter Field:=51,
> Criteria1:="<>" & FileName
>  Rows("102:" & k).Select
> Selection.Delete Shift:=xlUp
> Range("A101").Select
> Selection.AutoFilter
>
>
> *complete code*
>
> Public Sub OPEX_Reporting()
>
> Dim Master As Workbook' Macro and Master Workbook
> 'Dim Linked As Workbook' CCA P&L Linked File
> Dim NewFile As Workbook   ' New File
>
> Dim macro, CCA As Worksheet   ' Worksheets in Macro and Master Workbook
> 'Dim Report, CON As Worksheet  ' Worksheets in CCA P&L Linked File
> Dim sh As Worksheet   ' Searching for sheets
>
> Dim rng As Range
>
> Dim i, j, k, l As Double
> Dim Outerloop, Innerloop As Integer
>
> Dim PATH, PATH1  As String
> Dim FileName As Double
> Dim FileNamesave As Double
>
> Set Master = ThisWorkbook
>
> Set macro = Master.Sheets("Macro")
>
>
> i = macro.Cells(Rows.Count, "A").End(xlUp).Row
>
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
>
> PATH = macro.Range("C4")
>
> Dim Answer As String
> Dim MyNote As String
>
> 'Place your text here
> MyNote = "You are about to run the Macro. Are you Sure?" & Chr(13) &
> Chr(13) & "If Yes! Make sure the below Path is empty - " & Chr(13) &
> Chr(13) & PATH
>
> 'Display MessageBox
> Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Macro Confirmation
> Message !!!")
>
> If Answer = vbNo Then
> 'Code for No button Press
> 'MsgBox "You pressed NO!"
> End
> Else
> 'Code for Yes button Press
> 'MsgBox "You pressed Yes!"
> End If
>
> For Outerloop = 8 To 10
> j = macro.Cells(Outerloop, Columns.Count).End(xlToLeft).Column
>
>
> FileName = macro.Cells(Outerloop, 1)
>
>
> Set NewFile = Workbooks.Add
>
> For Innerloop = 3 To j
> Dim CC As String
>
> CC = ""
> CC = macro.Cells(Outerloop, Innerloop)
> Set sh = Master.Worksheets(CC)
> sh.Activate
> ActiveSheet.Copy before:=NewFile.Sheets(1)
> k = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
>
> NewFile.Sheets(CC).Select
> Cells.Find(What:="Mapping Data", After:=ActiveCell, LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
> MatchCase:=False, SearchFormat:=False).Activate
>
> 'If Sheets(CC).FilterMode = True Then
> 'Selection.AutoFilter
> 'Else
> 'Selection.AutoFilter
> 'End If
>
>
>  If CC = "Transaction Details" Then
> ActiveSheet.Range("A101:AY" & k).AutoFilter Field:=51,
> Criteria1:="<>" & FileName
>  Rows("102:" & k).Select
> Selection.Delete Shift:=xlUp
> Range("A101").Select
> Selection.AutoFilter
> ElseIf CC = "Phased Actuals" Then
> ActiveSheet.Range("A49:AY" & k).AutoFilter Field:=51, Criteria1:="<>"
> & FileName
> Rows("50:" & k).Select
> Selection.Delete Shift:=xlUp
> Range("A49").Select
> Selection.AutoFilter
> ElseIf CC = "Summary" Then
> Rows("11:11").Select
> ActiveSheet.Range("$A$11:$BF$19" & k).AutoFilter Field:=50,
> Criteria1:="<>" & FileName
> Rows("12:" & k).Select
> Selection.Delete Shift:=xlUp
> Range("a11").Select
> Selection.AutoFilter
> 'ElseIf CC = "Nat Exp Vs Plan" Then
> 'ActiveSheet.Range("A94:AH" & k).AutoFilter Field:=35, Criteria1:="<>"
> & FileName
> 'Rows("95:" & k).Select
> 'Selection.Delete Shift:=xlUp
> 'Range("A94").Select
> 'Selection.AutoFilter
> 'Else
> 'ActiveSheet.Range("A94:AH" & k).AutoFilter Field:=34, Criteria1:="<>"
> & FileName
> 'Rows("95:" & k).Select
> 'Selection.Delete Shift:=xlUp
> 'Range("A94").Select
> 'Selection.AutoFilter
> End If
>
> Next
> NewFile.Activate
> On Error Resume Next
> NewFile.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
> NewFile.SaveAs FileName:=PATH & "\" & FileName,
> FileFormat:=xlOpenXMLWorkbook
> NewFile.Close
> Next
> macro.Activate
> MsgBox "You have run the Macro Successfully!!!"
>
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
> End Sub
>
>
> Thanks & Regards,
> Ram
>
>
>
>
>
>  --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help,

Re: $$Excel-Macros$$ Loop issue

2013-04-27 Thread excel learner

hi David,

I tried step by step execution but still it is executing fine without any
error but deletion part in first transaction tab is not done..

Thanks & Regards,
Ram


On Sun, Apr 28, 2013 at 12:04 PM, David Grugeon wrote:

> You have highlighted 5 lines
>
> Try stepping through the code and finding out which line does not work as
> required.
>
>
>
> Regards
> David Grugeon
>
>
>
> On 28 April 2013 16:25,  wrote:
>
>> Hi Experts,
>>
>> i have come across an issue where in auto filter code  is skipping over
>> without performing the required action and running next course of action.
>>
>> I Have highlighted the line where am getting error can any one correct me
>> where am facing this issue.
>>
>> Macro is executing well but this particular lines it is not performing
>> the action.
>>
>>
>> ActiveSheet.Range("A101:AY" & k).AutoFilter Field:=51,
>> Criteria1:="<>" & FileName
>>  Rows("102:" & k).Select
>> Selection.Delete Shift:=xlUp
>> Range("A101").Select
>> Selection.AutoFilter
>>
>>
>> *complete code*
>>
>> Public Sub OPEX_Reporting()
>>
>> Dim Master As Workbook' Macro and Master Workbook
>> 'Dim Linked As Workbook' CCA P&L Linked File
>> Dim NewFile As Workbook   ' New File
>>
>> Dim macro, CCA As Worksheet   ' Worksheets in Macro and Master Workbook
>> 'Dim Report, CON As Worksheet  ' Worksheets in CCA P&L Linked File
>> Dim sh As Worksheet   ' Searching for sheets
>>
>> Dim rng As Range
>>
>> Dim i, j, k, l As Double
>> Dim Outerloop, Innerloop As Integer
>>
>> Dim PATH, PATH1  As String
>> Dim FileName As Double
>> Dim FileNamesave As Double
>>
>> Set Master = ThisWorkbook
>>
>> Set macro = Master.Sheets("Macro")
>>
>>
>> i = macro.Cells(Rows.Count, "A").End(xlUp).Row
>>
>> Application.ScreenUpdating = False
>> Application.DisplayAlerts = False
>>
>> PATH = macro.Range("C4")
>>
>> Dim Answer As String
>> Dim MyNote As String
>>
>> 'Place your text here
>> MyNote = "You are about to run the Macro. Are you Sure?" & Chr(13) &
>> Chr(13) & "If Yes! Make sure the below Path is empty - " & Chr(13) &
>> Chr(13) & PATH
>>
>> 'Display MessageBox
>> Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Macro Confirmation
>> Message !!!")
>>
>> If Answer = vbNo Then
>> 'Code for No button Press
>> 'MsgBox "You pressed NO!"
>> End
>> Else
>> 'Code for Yes button Press
>> 'MsgBox "You pressed Yes!"
>> End If
>>
>> For Outerloop = 8 To 10
>> j = macro.Cells(Outerloop, Columns.Count).End(xlToLeft).Column
>>
>>
>> FileName = macro.Cells(Outerloop, 1)
>>
>>
>> Set NewFile = Workbooks.Add
>>
>> For Innerloop = 3 To j
>> Dim CC As String
>>
>> CC = ""
>> CC = macro.Cells(Outerloop, Innerloop)
>> Set sh = Master.Worksheets(CC)
>> sh.Activate
>> ActiveSheet.Copy before:=NewFile.Sheets(1)
>> k = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
>>
>> NewFile.Sheets(CC).Select
>> Cells.Find(What:="Mapping Data", After:=ActiveCell, LookIn:=xlFormulas, _
>> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
>> MatchCase:=False, SearchFormat:=False).Activate
>>
>> 'If Sheets(CC).FilterMode = True Then
>> 'Selection.AutoFilter
>> 'Else
>> 'Selection.AutoFilter
>> 'End If
>>
>>
>>  If CC = "Transaction Details" Then
>> ActiveSheet.Range("A101:AY" & k).AutoFilter Field:=51,
>> Criteria1:="<>" & FileName
>>  Rows("102:" & k).Select
>> Selection.Delete Shift:=xlUp
>> Range("A101").Select
>> Selection.AutoFilter
>> ElseIf CC = "Phased Actuals" Then
>> ActiveSheet.Range("A49:AY" & k).AutoFilter Field:=51, Criteria1:="<>"
>> & FileName
>> Rows("50:" & k).Select
>> Selection.Delete Shift:=xlUp
>> Range("A49").Select
>> Selection.AutoFilter
>> ElseIf CC = "Summary" Then
>> Rows("11:11").Select
>> ActiveSheet.Range("$A$11:$BF$19" & k).AutoFilter Field:=50,
>> Criteria1:="<>" & FileName
>> Rows("12:" & k).Select
>> Selection.Delete Shift:=xlUp
>> Range("a11").Select
>> Selection.AutoFilter
>> 'ElseIf CC = "Nat Exp Vs Plan" Then
>> 'ActiveSheet.Range("A94:AH" & k).AutoFilter Field:=35,
>> Criteria1:="<>" & FileName
>> 'Rows("95:" & k).Select
>> 'Selection.Delete Shift:=xlUp
>> 'Range("A94").Select
>> 'Selection.AutoFilter
>> 'Else
>> 'ActiveSheet.Range("A94:AH" & k).AutoFilter Field:=34,
>> Criteria1:="<>" & FileName
>> 'Rows("95:" & k).Select
>> 'Selection.Delete Shift:=xlUp
>> 'Range("A94").Select
>> 'Selection.AutoFilter
>> End If
>>
>> Next
>> NewFile.Activate
>> On Error Resume Next
>> NewFile.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
>> NewFile.SaveAs FileName:=PATH & "\" & FileName,
>> FileFormat:=xlOpenXMLWorkbook
>> NewFile.Close
>> Next
>> macro.Activate
>> MsgBox "You have run the Macro Successfully!!!"
>>
>> Application.DisplayAlerts = True

RE: $$Excel-Macros$$ Filter based on coulmns

Re: $$Excel-Macros$$ Filter based on coulmns

Re: $$Excel-Macros$$ Create Sheets by given name.........

$$Excel-Macros$$ Copy/Paste Problem

Re: $$Excel-Macros$$ Create Sheets by given name.........

Re: $$Excel-Macros$$ Copy/Paste Problem

$$Excel-Macros$$ Loop issue

Re: $$Excel-Macros$$ Loop issue

Re: $$Excel-Macros$$ Loop issue

9 matches

Site Navigation

Mail list logo

Footer information