Re: $$Excel-Macros$$ Macro needed to add worksheet
Hi Experts please help On Wednesday, September 11, 2013 7:55:14 PM UTC+5:30, SG wrote: hi Ravi, This code is throwing error subscript out of range. Please suggest. On Wednesday, September 11, 2013 5:44:17 PM UTC+5:30, Ravi Kumar wrote: No dear, Point 1) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) Point 2) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) Above code is like in single line you can say. When we use “_” it will continue our current line with next new line. You can see above number 1 and 2 point both are same * * *Warm Regards,* *Ravi Kumar.* *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *SG *Sent:* Wednesday, September 11, 2013 5:33 PM *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet Hi Ravi, i think the below code is incomplete.please correct me if i'm wrong. Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ On Tuesday, September 10, 2013 2:54:19 PM UTC+5:30, Ravi Kumar wrote: Try this and change you folder name accordingly … Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False primewb = ActiveWorkbook.Name Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub * * *Warm Regards,* *Ravi Kumar.* *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *SG *Sent:* Tuesday, September 10, 2013 2:36 PM *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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
Re: $$Excel-Macros$$ Macro needed to add worksheet
Hi Ravi, i think the below code is incomplete.please correct me if i'm wrong. Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ On Tuesday, September 10, 2013 2:54:19 PM UTC+5:30, Ravi Kumar wrote: Try this and change you folder name accordingly … Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False primewb = ActiveWorkbook.Name Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub * * *Warm Regards,* *Ravi Kumar.* *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *SG *Sent:* Tuesday, September 10, 2013 2:36 PM *To:* excel-...@googlegroups.com javascript: *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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...@googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- *Regards* * * *Ashish Koul* *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- 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
Re: $$Excel-Macros$$ Macro needed to add worksheet
hi Ashish, it's running perfectly without any error but it's not adding the sheet in workbook. On Tuesday, September 10, 2013 7:54:02 PM UTC+5:30, ashish wrote: is it giving you any error On Tue, Sep 10, 2013 at 2:54 PM, Ravi Kumar excelle...@gmail.comjavascript: wrote: Try this and change you folder name accordingly … ** ** ** ** Sub copy_sheet() ** ** Application.ScreenUpdating = False Application.DisplayAlerts = False ** ** ** ** primewb = ActiveWorkbook.Name ** ** Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook ** ** ** ** Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files ** ** ** ** For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next ** ** Application.ScreenUpdating = True Application.DisplayAlerts = True ** ** ** ** End Sub ** ** ** ** * * *Warm Regards,* *Ravi Kumar.* ** ** *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *SG *Sent:* Tuesday, September 10, 2013 2:36 PM *To:* excel-...@googlegroups.com javascript: *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet ** ** Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: ** ** Sub copy_sheet() ** ** Application.ScreenUpdating = False Application.DisplayAlerts = False ** ** ** ** Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook ** ** Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files ** ** For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next ** ** Application.ScreenUpdating = True Application.DisplayAlerts = True ** ** ** ** ** ** End Sub ** ** ** ** see if it helps ** ** ** ** On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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...@googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. ** ** -- *Regards* * * *Ashish Koul* ** ** ** ** *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ ** ** ** ** P
RE: $$Excel-Macros$$ Macro needed to add worksheet
No dear, Point 1) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) Point 2) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) Above code is like in single line you can say. When we use _ it will continue our current line with next new line. You can see above number 1 and 2 point both are same Warm Regards, Ravi Kumar. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of SG Sent: Wednesday, September 11, 2013 5:33 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Macro needed to add worksheet Hi Ravi, i think the below code is incomplete.please correct me if i'm wrong. Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ On Tuesday, September 10, 2013 2:54:19 PM UTC+5:30, Ravi Kumar wrote: Try this and change you folder name accordingly . Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False primewb = ActiveWorkbook.Name Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub Warm Regards, Ravi Kumar. From: excel-...@googlegroups.com javascript: [mailto:excel-...@googlegroups.com javascript: ] On Behalf Of SG Sent: Tuesday, September 10, 2013 2:36 PM To: excel-...@googlegroups.com javascript: Subject: Re: $$Excel-Macros$$ Macro needed to add worksheet Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com mailto:sona...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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...@googlegroups.com mailto:excel-macros...@googlegroups.com . To post to this group, send email to excel-...@googlegroups.com mailto:excel-...@googlegroups.com . Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Regards Ashish
Re: $$Excel-Macros$$ Macro needed to add worksheet
hi Ravi, This code is throwing error subscript out of range. Please suggest. On Wednesday, September 11, 2013 5:44:17 PM UTC+5:30, Ravi Kumar wrote: No dear, Point 1) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) Point 2) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) Above code is like in single line you can say. When we use “_” it will continue our current line with next new line. You can see above number 1 and 2 point both are same * * *Warm Regards,* *Ravi Kumar.* *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *SG *Sent:* Wednesday, September 11, 2013 5:33 PM *To:* excel-...@googlegroups.com javascript: *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet Hi Ravi, i think the below code is incomplete.please correct me if i'm wrong. Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ On Tuesday, September 10, 2013 2:54:19 PM UTC+5:30, Ravi Kumar wrote: Try this and change you folder name accordingly … Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False primewb = ActiveWorkbook.Name Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub * * *Warm Regards,* *Ravi Kumar.* *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *SG *Sent:* Tuesday, September 10, 2013 2:36 PM *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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
Re: $$Excel-Macros$$ Macro needed to add worksheet
Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com javascript:wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- *Regards* * * *Ashish Koul* *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- 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. For more options, visit https://groups.google.com/groups/opt_out. Book1.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
RE: $$Excel-Macros$$ Macro needed to add worksheet
Try this and change you folder name accordingly . Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False primewb = ActiveWorkbook.Name Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub Warm Regards, Ravi Kumar. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of SG Sent: Tuesday, September 10, 2013 2:36 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Macro needed to add worksheet Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com javascript: wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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...@googlegroups.com javascript: . To post to this group, send email to excel-...@googlegroups.com javascript: . Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Regards Ashish Koul Visit My Excel Blog http://www.excelvbamacros.com/ Like Us on Facebook http://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- 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
Re: $$Excel-Macros$$ Macro needed to add worksheet
is it giving you any error On Tue, Sep 10, 2013 at 2:54 PM, Ravi Kumar excellearn2...@gmail.comwrote: Try this and change you folder name accordingly … ** ** ** ** Sub copy_sheet() ** ** Application.ScreenUpdating = False Application.DisplayAlerts = False ** ** ** ** primewb = ActiveWorkbook.Name ** ** Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook ** ** ** ** Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files ** ** ** ** For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next ** ** Application.ScreenUpdating = True Application.DisplayAlerts = True ** ** ** ** End Sub ** ** ** ** * * *Warm Regards,* *Ravi Kumar.* ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *SG *Sent:* Tuesday, September 10, 2013 2:36 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet ** ** Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: ** ** Sub copy_sheet() ** ** Application.ScreenUpdating = False Application.DisplayAlerts = False ** ** ** ** Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook ** ** Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files ** ** For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next ** ** Application.ScreenUpdating = True Application.DisplayAlerts = True ** ** ** ** ** ** End Sub ** ** ** ** see if it helps ** ** ** ** On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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...@googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. ** ** -- *Regards* * * *Ashish Koul* ** ** ** ** *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ ** ** ** ** P Before printing, think about the environment. -- 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
$$Excel-Macros$$ Macro needed to add worksheet
Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Macro needed to add worksheet
Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sonal...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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. For more options, visit https://groups.google.com/groups/opt_out. -- *Regards* * * *Ashish Koul* *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- 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. For more options, visit https://groups.google.com/groups/opt_out.
