$$Excel-Macros$$ Macro to copy 6 tabs to new sheet and past as values.
1. I have a number of spreadsheets in one folder. For each spreadsheet, I need to open, then copy all 6 tabs (all have the same name: Option A, Option B, Option C, Francais A, Francais B, Francais C) to a new sheet and name the new sheet with the the same name as the original sheet plus 'copy' in the name. I manually tested an found that I had to 'unprotect' each sheet, then break the links in each sheet but retaining the original data. The original sheet with the option tabs all have formulas. Once I have this macro working, I would like to inlcude it into another macro that updated cells in multiple spreadsheets in a folder. l will then updated it so that I can run it on all sheets in a folder. ie. C:\Excel-Work\. Hope this is clear. thx Mel -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Need help to create VBA to copy 4 cells from one spreadsheet to over 200 spreadsheats in same folder
fantastic Paul. yes, all workbooks in only one folder. worked like a charm. Many thanks. Mel On Wednesday, October 31, 2012 12:49:45 PM UTC-4, Mel wrote: > I have a master spreadsheet with 5 tabs. On my master sheet (called > Master) - on the tab called 'Option Letter' I need to copy cells B131, > B202, B319, B424 to the same > tab (same name on each sheet 'Option Letter') on over 200 spreadsheets. I > will need to unprotect the Option Letter tab (no password), paste in the > info from my Master sheet Option letter tab (master sheet is called > master), > then protect (no password) and save the spreadsheet with the same name. > Each spreadsheet is in the same folder called C:\EBACKUP. Each spreadsheet > has a different name. > > I will only need to run this process once to update all spreadsheets with > the new info for these 4 cells.I tried a number of different VBA to > loop through each spreadsheet but keep getting a number of error. > Any help is appreciated. > thx > > Mel > -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ How to create VBA to copy 4 cells from one spreadsheet to over 200 spreadsheats in same folder
On Wednesday, October 31, 2012 12:49:45 PM UTC-4, Mel wrote:
>
> I have a master spreadsheet with 5 tabs. On my master sheet (called
> Master) - on the tab called 'Option Letter' I need to copy cells B131,
> B202, B319, B424 to the same
> tab (same name on each sheet 'Option Letter') on over 200 spreadsheets. I
> will need to unprotect the Option Letter tab (no password), paste in the
> info from my Master sheet Option letter tab (master sheet is called
> master),
> then protect (no password) and save the spreadsheet with the same name.
> Each spreadsheet is in the same folder called P:\EBACKUP. Each spreadsheet
> has a different name.
>
> I will only need to run this process once to update all spreadsheets with
> the new info for these 4 cells.I tried a number of different VBA to
> loop through each spreadsheet but keep getting a number of error.
> Any help is appreciated.
> thx
>
>
Here is what I have so far that I need to get top open all files (over 200
of them) in directory P:\EBACKUP\ and update the 4 cells with data from the
master spreadsheet and update once cell on another tab called group table
with a new date.
then close and save the spreadsheet, open the next on in the list and so on
until all are updated.
Sub RRSP_fix()
'
' RRSP_fix Macro
'
'
Range("B131").Select
Selection.Copy
Windows("Abedin, Joynal 012 867 235.xlsx").Activate
ActiveWindow.SmallScroll Down:=33
Range("B131").Select
ActiveSheet.Paste
Windows("MASTER.xlsm").Activate
ActiveWindow.SmallScroll Down:=78
Range("B207").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Abedin, Joynal 012 867 235.xlsx").Activate
ActiveWindow.SmallScroll Down:=75
Range("B207").Select
ActiveSheet.Paste
Windows("MASTER.xlsm").Activate
ActiveWindow.SmallScroll Down:=114
Range("B319").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Abedin, Joynal 012 867 235.xlsx").Activate
ActiveWindow.SmallScroll Down:=111
Range("B319").Select
ActiveSheet.Paste
Windows("MASTER.xlsm").Activate
ActiveWindow.SmallScroll Down:=108
Range("B424").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Abedin, Joynal 012 867 235.xlsx").Activate
ActiveWindow.SmallScroll Down:=108
Range("B424").Select
ActiveSheet.Paste
Range("B430:J430").Select
Sheets("Group Table").Select
Range("B7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "7/12/2012"
Range("B3").Select
Sheets("Option Letter").Select
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.WindowState = xlMinimized
Application.WindowState = xlNormal
End Sub
>
>
> Mel
>
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
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) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Need help to create VBA to copy 4 cells from one spreadsheet to over 200 spreadsheats in same folder
I have a master spreadsheet with 5 tabs. On my master sheet (called Master) - on the tab called 'Option Letter' I need to copy cells B131, B202, B319, B424 to the same tab (same name on each sheet 'Option Letter') on over 200 spreadsheets. I will need to unprotect the Option Letter tab (no password), paste in the info from my Master sheet Option letter tab (master sheet is called master), then protect (no password) and save the spreadsheet with the same name. Each spreadsheet is in the same folder called C:\EBACKUP. Each spreadsheet has a different name. I will only need to run this process once to update all spreadsheets with the new info for these 4 cells.I tried a number of different VBA to loop through each spreadsheet but keep getting a number of error. Any help is appreciated. thx Mel -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Macro to hide rows, print, unhide rows runs very slow - how to speed up?
I have a macro that will hide unused rows in a spreadsheet, print the
active sheet, then unhide the rows.
Spreadsheet is from cell A1 to P175. Base info is recorded in various
cells on rows 1 to 4.
Cells 5 to 16 display output.
The main Data input starts in cell A18 to G 167. A18 is a from date, B18
to date, with comments in column P.
Because I have 150 lines that could be used, I want to hide unused rows
when printing to reduce the pages.
Rows 168 to 175 display results and are not hidden.
Here is the macro I have. It works but runs very slowly. I'm lookig for
ways to speed it up.
Option Explicit
Sub PrintNonBlankColA()
Dim RowCrnt As Integer
Dim RowLast As Integer
' Note: This operates on the active worksheet
Application.ScreenUpdating = False
RowLast = Cells.SpecialCells(xlCellTypeLastCell).Row
' Hide all rows with a used cell and column "A" empty
For RowCrnt = 18 To RowLast
If IsEmpty(Cells(RowCrnt, "A")) Then
Range(RowCrnt & ":" & RowCrnt).EntireRow.Hidden = True
End If
Next
Range("A1:Q175").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$175"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Rows("17:168").Select
Selection.EntireRow.Hidden = False
Range("B2").Select
End Sub
any suggestions to speed it up appreciated.
Mel
--
FORUM RULES (986+ members already BANNED for violation)
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) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ How to copy sheet to new book without sheet module code?
I copy specific sheets from one book into a new book but I don't want any VBA code in the new book. One sheet, however, has code in the sheet module. How can I copy it without the code or remove the code in the destination book? For the copy, I'm simply using... Sheets(Array(sShtName1, sShtName2, sShtName3, sShtName4)).Copy Assume sShtName4 has the sheet code. Thanks, Melina -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ how to print only rows with data and not inlcude rows with a formula
I have the folowing macro set up to print but in column i I have a formula to look at column H o- if data in H, show comment in I. But, when I have the formula copied down to row 2000, my print macro pics up the formula as data and prints all rows. How to I exclude the formula in I from the macro to print only rows with Data? thx Mel Sub SetUsedPrintArea() Dim LastRow As Long If WorksheetFunction.CountA(Cells) > 0 Then 'Search for any entry, by searching backwards by Rows. LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row 'MsgBox LastRow End If Dim LastColumn As Integer If WorksheetFunction.CountA(Cells) > 0 Then 'Search for any entry, by searching backwards by Columns. LastColumn = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column ' MsgBox LastColumn End If ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(LastRow, LastColumn)).Address ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ macro to print only rows with date but exclude rows that have a formula
I have the following macro that I use to print only rows with data (A1 to up to I2000 possible). The only column that has a formula is the I colunm. Without the formula this macro works but I do not know where to put in the exclusion to not look at the formula in column I. Note that the formula is set to look at info in column H, if there is something in H, show comment in I. thx Mel Sub SetUsedPrintArea() Dim LastRow As Long If WorksheetFunction.CountA(Cells) > 0 Then 'Search for any entry, by searching backwards by Rows. LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row 'MsgBox LastRow End If Dim LastColumn As Integer If WorksheetFunction.CountA(Cells) > 0 Then 'Search for any entry, by searching backwards by Columns. LastColumn = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column ' MsgBox LastColumn End If ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(LastRow, LastColumn)).Address ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Macro to filter dates using Userform1 & printing
not sure but how do I re-format or move fields around without causing the dates for the calendar to fail? I am also trying to format the table of values (A3 to E5000) but cannot. On Mar 16, 8:31 pm, Deepak Rai wrote: > Hi Mel, > > Please find the updated file which can be use with Excel 2003. > > Regards, > > Deepak Rai > > > > > > On Tue, Mar 16, 2010 at 6:45 PM, Mel wrote: > > It looks like this is what I need however, I have Excel version 2003 > > not 2007 so it does not conver over and work. > > any chance you can re-save in 2003 version? thx > > Mel > > > On Mar 14, 5:02 am, Deepak Rai wrote: > > > Hi Mel, > > > > I have a sample macro which work like similar as you want. Please find > > the > > > attached. > > > > Hope this will help. > > > > Thanks, > > > > Deepak Rai > > > > On Fri, Mar 12, 2010 at 10:50 AM, Abhishek Jain > >wrote: > > > > > Mel, > > > > > I did something similar to this (not sorting but filtering) for another > > > > project. If you put a sample file of yours, I can help. > > > > > Best, > > > > > AJ > > > > > On Thu, Mar 11, 2010 at 10:22 PM, Mel wrote: > > > > >> I have a speadsheet that has 5 columns. > > > >> (Headings are in row 2 and are: Cell A2=Date, B2=Name, C2=Paylist, > > > >> D2=Cycle, E2=Action). > > > >> Data starts in Row 3 and will go down to row 5000. The dates would > > > >> start 01-Jan-2011 and could go up to 31-Dec-2011. > > > > >> Right now I have 2 macro's to sort by date and paylist but what I > > > >> also > > > >> need is a macro to allow the user to sort by the user inputting a > > > >> from > > > >> date (I think I can use a calendar to pick from) and a to date > > > >> (option > > > >> of => and =<) in a form. > > > > >> The next piece is how to display and be able to print this filtered > > > >> info. > > > >> I am not sure best option. Possibly only these rows be displayed and > > > >> the other rows be hidden or maybe copy to a new page and after > > > >> filtering the macro moves access to new page. > > > > >> I have also created a macro to open a user form1. So far I have (but > > > >> no code yet to run) options to sort by Name, Date, Paylist, cycle and > > > >> action. > > > > >> thx > > > >> Mel > > > > >> -- > > > ------ > > > >> Some important links for excel users: > > > >> 1. Follow us on TWITTER for tips tricks and links : > > > >>http://twitter.com/exceldailytip > > > >> 2. Join our Facebook Group @ > > > >>http://www.facebook.com/group.php?gid=287779555678 > > > >> 3. Excel tutorials athttp://www.excel-macros.blogspot.com > > > >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > > >> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > > > >> To post to this group, send email to excel-macros@googlegroups.com > > > > >> <><><><><><><><><><><><><><><><><><><><><><> > > > >> HELP US GROW !! > > > > >> We reach over 6,800 subscribers worldwide and receive many nice notes > > > >> about the learning and support from the group.Let friends and > > co-workers > > > >> know they can subscribe to group at > > > >>http://groups.google.com/group/excel-macros/subscribe > > > > > -- > > > ------ > > > > Some important links for excel users: > > > > 1. Follow us on TWITTER for tips tricks and links : > > > >http://twitter.com/exceldailytip > > > > 2. Join our Facebook Group @ > > > >http://www.facebook.com/group.php?gid=287779555678 > > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > > > > To post to this group, send email to excel-macros@googlegroups.com > > > > > <><&
Re: $$Excel-Macros$$ Macro to filter dates using Userform1 & printing
It looks like this is what I need however, I have Excel version 2003 not 2007 so it does not conver over and work. any chance you can re-save in 2003 version? thx Mel On Mar 14, 5:02 am, Deepak Rai wrote: > Hi Mel, > > I have a sample macro which work like similar as you want. Please find the > attached. > > Hope this will help. > > Thanks, > > Deepak Rai > > On Fri, Mar 12, 2010 at 10:50 AM, Abhishek Jain wrote: > > > > > > > Mel, > > > I did something similar to this (not sorting but filtering) for another > > project. If you put a sample file of yours, I can help. > > > Best, > > > AJ > > > On Thu, Mar 11, 2010 at 10:22 PM, Mel wrote: > > >> I have a speadsheet that has 5 columns. > >> (Headings are in row 2 and are: Cell A2=Date, B2=Name, C2=Paylist, > >> D2=Cycle, E2=Action). > >> Data starts in Row 3 and will go down to row 5000. The dates would > >> start 01-Jan-2011 and could go up to 31-Dec-2011. > > >> Right now I have 2 macro's to sort by date and paylist but what I > >> also > >> need is a macro to allow the user to sort by the user inputting a > >> from > >> date (I think I can use a calendar to pick from) and a to date > >> (option > >> of => and =<) in a form. > > >> The next piece is how to display and be able to print this filtered > >> info. > >> I am not sure best option. Possibly only these rows be displayed and > >> the other rows be hidden or maybe copy to a new page and after > >> filtering the macro moves access to new page. > > >> I have also created a macro to open a user form1. So far I have (but > >> no code yet to run) options to sort by Name, Date, Paylist, cycle and > >> action. > > >> thx > >> Mel > > >> -- > > >> ------ > >> Some important links for excel users: > >> 1. Follow us on TWITTER for tips tricks and links : > >>http://twitter.com/exceldailytip > >> 2. Join our Facebook Group @ > >>http://www.facebook.com/group.php?gid=287779555678 > >> 3. Excel tutorials athttp://www.excel-macros.blogspot.com > >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > >> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > >> To post to this group, send email to excel-macros@googlegroups.com > > >> <><><><><><><><><><><><><><><><><><><><><><> > >> HELP US GROW !! > > >> We reach over 6,800 subscribers worldwide and receive many nice notes > >> about the learning and support from the group.Let friends and co-workers > >> know they can subscribe to group at > >>http://groups.google.com/group/excel-macros/subscribe > > > -- > > > ------ > > Some important links for excel users: > > 1. Follow us on TWITTER for tips tricks and links : > >http://twitter.com/exceldailytip > > 2. Join our Facebook Group @ > >http://www.facebook.com/group.php?gid=287779555678 > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > > To post to this group, send email to excel-macros@googlegroups.com > > > <><><><><><><><><><><><><><><><><><><><><><> > > HELP US GROW !! > > > We reach over 6,800 subscribers worldwide and receive many nice notes about > > the learning and support from the group.Let friends and co-workers know they > > can subscribe to group at > >http://groups.google.com/group/excel-macros/subscribe > > -- > Thanks, > > Deepak Rai > > Solved Answer (Mel).xlsm > 45KViewDownload- Hide quoted text - > > - Show quoted text - -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Macro to filter dates using Userform1 & printing
I have a speadsheet that has 5 columns. (Headings are in row 2 and are: Cell A2=Date, B2=Name, C2=Paylist, D2=Cycle, E2=Action). Data starts in Row 3 and will go down to row 5000. The dates would start 01-Jan-2011 and could go up to 31-Dec-2011. Right now I have 2 macro's to sort by date and paylist but what I also need is a macro to allow the user to sort by the user inputting a from date (I think I can use a calendar to pick from) and a to date (option of => and =<) in a form. The next piece is how to display and be able to print this filtered info. I am not sure best option. Possibly only these rows be displayed and the other rows be hidden or maybe copy to a new page and after filtering the macro moves access to new page. I have also created a macro to open a user form1. So far I have (but no code yet to run) options to sort by Name, Date, Paylist, cycle and action. thx Mel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Macro to filter dates using Userform1 & printing
I have a speadsheet that has 5 columns. (Headings are in row 2 and are: Cell A2=Date, B2=Name, C2=Paylist, D2=Cycle, E2=Action). Data starts in Row 3 and will go down to row 5000. The dates would start 01-Jan-2011 and could go up to 31-Dec-2011. Right now I have 2 macro's to sort by date and paylist but what I also need is a macro to allow the user to sort by the user inputting a from date (I think I can use a calendar to pick from) and a to date (option of => and =<) in a form. The next piece is how to display and be able to print this filtered info. I am not sure best option. Possibly only these rows be displayed and the other rows be hidden or maybe copy to a new page and after filtering the macro moves access to new page. I have also created a macro to open a user form1. So far I have (but no code yet to run) options to sort by Name, Date, Paylist, cycle and action. thx Mel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ using Networkdays formula in French version of Excel options
I have a spreadsheet that I have several references to the formula 'Networkdays'. The spreadsheet works great using the English version of Excel however, I have collegues that use a French copy of Excel and the spreadsheet does not function using that version of Excel. In order to get the spreadsheet to work, I have to change all the references in the spreadsheet from 'Networkdays' to 'NB.JOURS.OUVRES' and then it will work. Is there anyway I can set up the spreadsheet so that it will work in both languages without having to re-write the formual for different versions of Excel? Maybe a statement 'if Excel ver=French, use NB.JOURS.OUVRES, if English use Networkdays'? Or maybe a macro to change the formula from 'Networkdays' to 'NB.JOURS.OUVRES' on all tabs of the workbook. thx Mel --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Networkdays formula in French version of Excel options
Networkdays formula in French version of Excel options I have a spreadsheet that I have several references to the formula 'Networkdays'. The spreadsheet works great using the English version of Excel however, I have collegues that use a French copy of Excel and the spreadsheet does not function using that version of Excel. In order to get the spreadsheet to work, I have to change all the references in the spreadsheet from 'Networkdays' to 'NB.JOURS.OUVRES' and then it will work. Is there anyway I can set up the spreadsheet so that it will work in both languages without having to re-write the formual for different versions of Excel? Maybe a statement 'if Excel ver=French, use NB.JOURS.OUVRES, if English use Networkdays'? Another option I am thinking is if I could create a macro to change all formula's in the workbook that contain 'networkdays' to 'nb.jours.ouvres'. Not exactly sure how to search all formulas for this but maybe an options. thx Mel --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ using Networkdays formula in French version of Excel options
I have a spreadsheet that I have several references to the formula 'Networkdays'. The spreadsheet works great using the English version of Excel however, I have collegues that use a French copy of Excel and the spreadsheet does not function using that version of Excel. In order to get the spreadsheet to work, I have to change all the references in the spreadsheet from 'Networkdays' to 'NB.JOURS.OUVRES' and then it will work. Is there anyway I can set up the spreadsheet so that it will work in both languages without having to re-write the formual for different versions of Excel? Maybe a statement 'if Excel ver=French, use NB.JOURS.OUVRES, if English use Networkdays'? Or, maybe a macro to change any formula in the spreadsheet (and other tabs) from 'networkdays' to 'NB.JOURS.OUVRES' by pressing button? thx Mel --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
