$$Excel-Macros$$ pls help -Required out[ut in a cell
Hi Group i m creating a macro by vba but getting some error ... pls help i want to output in a cell.using below code .. example atteched.. Private Sub CommandButton1_Click() Dim sh As Worksheet Dim rng As Range Dim i As Integer Set sh = ThisWorkbook.Sheets(1) Set rng = sh.Range(a1:A sh.Cells(Rows.Count, a).Row) For i = 1 To 100 sh.Range(B1) = sh.Range(a i) i Next i End Sub Thankx Rgds, Chandra Singh Bisht -- 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/d/optout. example.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
$$Excel-Macros$$ How to Get Data of 527 employees from One Excel Sheet to another Sheet on given proforma
Dear Excel Experts! Hi, Once again I need your help regarding subject matter, mentioned above. I need Data of 527 employees data on other sheet to another proforma through the help of macros. Which can only be possible if you experts do this for me. I will be very much thankful to you experts for timely help. Note: I'm attaching my excel file here in which my problem is described in detail plz go through my sheet that will give you clear picture of my query. Thanking you in anticipation. __ Muhammad Rafique Ujjan -- 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/d/optout. Sample File for Excel Gurus.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ pls help -Required out[ut in a cell
Dear chandan, PFA. If you have any concern let me know. Regards, Ganesh N On Tue, Nov 4, 2014 at 3:41 PM, Chandan Bisht csb.bi...@gmail.com wrote: Hi Group i m creating a macro by vba but getting some error ... pls help i want to output in a cell.using below code .. example atteched.. Private Sub CommandButton1_Click() Dim sh As Worksheet Dim rng As Range Dim i As Integer Set sh = ThisWorkbook.Sheets(1) Set rng = sh.Range(a1:A sh.Cells(Rows.Count, a).Row) For i = 1 To 100 sh.Range(B1) = sh.Range(a i) i Next i End Sub Thankx Rgds, Chandra Singh Bisht -- 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/d/optout. -- 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/d/optout. Copy of example.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Fwd: $$Excel-Macros$$ Automatic update excel file from website
FYI + *I did not do this for you. God is here working through me for you.* -- Forwarded message -- From: Vaibhav Joshi v...@vabs.in Date: Sat, Nov 1, 2014 at 3:17 PM Subject: Re: $$Excel-Macros$$ Automatic update excel file from website To: excel-macros@googlegroups.com excel-macros@googlegroups.com check this... cheers!! + *I did not do this for you. God is here working through me for you.* On Fri, Oct 31, 2014 at 8:42 PM, Vaibhav Joshi v...@vabs.in wrote: Though it looks simple, but not the same! I will try send you working file for MCA too. Cheers!! + *I did not do this for you. God is here working through me for you.* On Fri, Oct 31, 2014 at 2:30 PM, Bajrang Sharma sharma@gmail.com wrote: Dear Friend, This works smoothly. I am really thankful to you. Thanks for sharing the link, I tried a lot to understand the logic / ways to do this by my own, but I failed as I don’t have programming knowledge. I want to learn this because I have to extract the data from other websites which have different options.. I am sharing one more file which have to extract data from mca website. Can you please please do the same programming in this file too... Thanks Regards, BS *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Vaibhav Joshi *Sent:* Thursday, 30 October, 2014 8:02 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Automatic update excel file from website Cheers Bro.. + *I did not do this for you. God is here working through me for you.* On Thu, Oct 30, 2014 at 7:08 PM, Ashish Kumar kumar.ashish...@gmail.com wrote: Hi Vaibhav Bhai, It's great.!! Regards Ashish -- 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/d/optout. -- 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 a topic in the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/excel-macros/X00xbnfdjyE/unsubscribe. To unsubscribe from this group and all its topics, 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/d/optout. -- 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
Re: $$Excel-Macros$$ pls help -Required out[ut in a cell
thnks ganesh..working fine.. Chandra Singh Bisht On Tue, Nov 4, 2014 at 5:01 PM, Ganesh N ganeshg...@gmail.com wrote: Dear chandan, PFA. If you have any concern let me know. Regards, Ganesh N On Tue, Nov 4, 2014 at 3:41 PM, Chandan Bisht csb.bi...@gmail.com wrote: Hi Group i m creating a macro by vba but getting some error ... pls help i want to output in a cell.using below code .. example atteched.. Private Sub CommandButton1_Click() Dim sh As Worksheet Dim rng As Range Dim i As Integer Set sh = ThisWorkbook.Sheets(1) Set rng = sh.Range(a1:A sh.Cells(Rows.Count, a).Row) For i = 1 To 100 sh.Range(B1) = sh.Range(a i) i Next i End Sub Thankx Rgds, Chandra Singh Bisht -- 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/d/optout. -- 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/d/optout. -- 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/d/optout.
$$Excel-Macros$$ How to convert PDF file into excel sheet correctly with same column width rows
Dear Experts! Hi, I have a problem while i copy data from pdf file to excel... it doesn't copy in same columns rows as shown in pdf file. Therefore please help me in this regard. Here i am attaching a pdf file. Thanks in advance. __ Muhammad Rafique Ujjan -- 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/d/optout. Sample PDF File.pdf Description: Adobe PDF document
$$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet
Hello, I have been given a task where I have say 10 spreadsheets located on the network which have the same filename and tab naming convention. Each spreadsheet will have a tab for each week of the year that contains reports that I'd like to consolidate. For example, each spreasdsheet will have the week number listed as tab name and I want to pull the same week's information into one file to have one spreadsheet with each location's report for the week. Here is the VBA code for the very simple flow that I want to replicate on larger scale: http://pastebin.com/99WG6nVz It basically opens each location's spreadsheet, grabs week 27's report and then consolidates them into one file that I save as a new copy called week 27 update.xlsm. Here is where I need some help. I'd like to set up the original MACRO.xlsm file with a reference to the week number in a cell so that I could select which tabs to copy over. For example, the above macro pulls week 27 from each of the spreadsheets, I'd like to make this dynamic in such that I could enter the week number and then use that cell value to dictate which week would be copied over. For clarification, each tab is named at number like 27, 28, 29, etc. The last bit of custom work would be to use that same cell value and save the file of the newly consolidated files to a location. The above example is week XX update.xlsm where XX is the cell value. I hope that all makes sense. Thank you in advance! Dza -- 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/d/optout.
Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet
I'm sure I can put together something that will work cleanly. Rather than make assuptions though, I'll ask a couple of questions: in the folder: C:\Users\test\Desktop\Labour\test will you be consolidating specific sheets from ALL of the files in this location (that do now begin with Week.. or all are type .xlsx?) We can specify the files on the worksheet: 11000 11001 11002 or we can simply process all files in the folder. I'll make some sample files for testing and then put together a macro for you. 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: dza49 dvpe...@gmail.com To: excel-macros@googlegroups.com Sent: Tuesday, November 4, 2014 8:11 AM Subject: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet Hello, I have been given a task where I have say 10 spreadsheets located on the network which have the same filename and tab naming convention. Each spreadsheet will have a tab for each week of the year that contains reports that I'd like to consolidate. For example, each spreasdsheet will have the week number listed as tab name and I want to pull the same week's information into one file to have one spreadsheet with each location's report for the week. Here is the VBA code for the very simple flow that I want to replicate on larger scale: http://pastebin.com/99WG6nVz It basically opens each location's spreadsheet, grabs week 27's report and then consolidates them into one file that I save as a new copy called week 27 update.xlsm. Here is where I need some help. I'd like to set up the original MACRO.xlsm file with a reference to the week number in a cell so that I could select which tabs to copy over. For example, the above macro pulls week 27 from each of the spreadsheets, I'd like to make this dynamic in such that I could enter the week number and then use that cell value to dictate which week would be copied over. For clarification, each tab is named at number like 27, 28, 29, etc. The last bit of custom work would be to use that same cell value and save the file of the newly consolidated files to a location. The above example is week XX update.xlsm where XX is the cell value. I hope that all makes sense. Thank you in advance! Dza -- 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/d/optout. -- 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
Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet
Here's what I came up with. Let me know if you need additional explanation. Option Explicit Sub Consolidate_Sheets() Dim ShtNo As String, NewWb, wbName Dim fPath Dim Sht, fso, fld, File Application.ScreenUpdating = False ' fPath = C:\temp\Users\test\Desktop\Labour\test\ ' ' Determine sheet to copy ' ShtNo = ThisWorkbook.Sheets(1).Range(A1).Value If (ShtNo X = X) Then MsgBox No Sheet Specified Exit Sub End If ' ' Define new Workbook Name ' NewWb = week ShtNo update ' Workbooks.Add ActiveWorkbook.SaveAs _ Filename:=fPath NewWb .xlsm, _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ CreateBackup:=False ' Set fso = CreateObject(Scripting.FileSystemObject) Set fld = fso.getfolder(fPath) ' ' Loop through files in fPath, looking for .XLSX files ' For Each File In fld.Files If (UCase(Right(File.Name, 4)) = XLSX) Then wbName = Replace(UCase(File.Name), .XLSX, ) 'Remove file extension Workbooks.Open Filename:=fPath File.Name Workbooks(File.Name).Sheets(ShtNo).Copy _ After:=Workbooks(NewWb .xlsm).Sheets(Workbooks(NewWb .xlsm).Sheets.Count) ActiveSheet.Name = wbName Workbooks(File.Name).Close savechanges:=False End If Next File ' Application.ScreenUpdating = True ' Workbooks(NewWb .xlsm).Close savechanges:=True End Sub 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: Paul Schreiner schreiner_p...@att.net To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Tuesday, November 4, 2014 8:51 AM Subject: Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet I'm sure I can put together something that will work cleanly. Rather than make assuptions though, I'll ask a couple of questions: in the folder: C:\Users\test\Desktop\Labour\test will you be consolidating specific sheets from ALL of the files in this location (that do now begin with Week.. or all are type .xlsx?) We can specify the files on the worksheet: 11000 11001 11002 or we can simply process all files in the folder. I'll make some sample files for testing and then put together a macro for you. 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: dza49 dvpe...@gmail.com To: excel-macros@googlegroups.com Sent: Tuesday, November 4, 2014 8:11 AM Subject: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet Hello, I have been given a task where I have say 10 spreadsheets located on the network which have the same filename and tab naming convention. Each spreadsheet will have a tab for each week of the year that contains reports that I'd like to consolidate. For example, each spreasdsheet will have the week number listed as tab name and I want to pull the same week's information into one file to have one spreadsheet with each location's report for the week. Here is the VBA code for the very simple flow that I want to replicate on larger scale: http://pastebin.com/99WG6nVz It basically opens each location's spreadsheet, grabs week 27's report and then consolidates them into one file that I save as a new copy called week 27 update.xlsm. Here is where I need some help. I'd like to set up the original MACRO.xlsm file with a reference to the week number in a cell so that I could select which tabs to copy over. For example, the above macro pulls week 27 from each of the spreadsheets, I'd like to make this dynamic in such that I could enter the week number and then use that cell value to dictate which week would be copied over. For clarification, each tab is named at number like 27, 28, 29, etc. The last bit of custom work would be to use that same cell value and save the file
Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet
Hello! With regards to consolidation file location point, yes that path (C:\Users\test\Desktop\Labour\test) is where I'd like the file to be saved to in the end. As for the source files if you will, (aka the files maintained by stores). These files will be saved in different folders on the network and yes, I will want to consolidate specific sheets from each store's file. Each file will have a unique name but each of the sheets will be labeled the same way (ie week number like 26, 27, 28, etc.) For the purpose of this test, we can use the assume that the source files and the consolidation macro will all reside in the same directory (C:\Users\test\Desktop\Labour\test). The file names are 11000,xlsx, 11001,xlsx and 11002.xlsx. Becuase in the end the source files will reside in different locations on the network having a macro that consolidates these specific files would be great! Again, the only area I'm struggling with is how to use a cell reference in a macro to then dictate which sheets need to be pulled into the consolidation file. Thanks for your help! On Tuesday, 4 November 2014 13:52:07 UTC, Paul Schreiner wrote: I'm sure I can put together something that will work cleanly. Rather than make assuptions though, I'll ask a couple of questions: in the folder: C:\Users\test\Desktop\Labour\test will you be consolidating specific sheets from ALL of the files in this location (that do now begin with Week.. or all are type .xlsx?) We can specify the files on the worksheet: 11000 11001 11002 or we can simply process all files in the folder. I'll make some sample files for testing and then put together a macro for you. *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:* dza49 dvp...@gmail.com javascript: *To:* excel-...@googlegroups.com javascript: *Sent:* Tuesday, November 4, 2014 8:11 AM *Subject:* $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet Hello, I have been given a task where I have say 10 spreadsheets located on the network which have the same filename and tab naming convention. Each spreadsheet will have a tab for each week of the year that contains reports that I'd like to consolidate. For example, each spreasdsheet will have the week number listed as tab name and I want to pull the same week's information into one file to have one spreadsheet with each location's report for the week. Here is the VBA code for the very simple flow that I want to replicate on larger scale: http://pastebin.com/99WG6nVz It basically opens each location's spreadsheet, grabs week 27's report and then consolidates them into one file that I save as a new copy called week 27 update.xlsm. Here is where I need some help. I'd like to set up the original MACRO.xlsm file with a reference to the week number in a cell so that I could select which tabs to copy over. For example, the above macro pulls week 27 from each of the spreadsheets, I'd like to make this dynamic in such that I could enter the week number and then use that cell value to dictate which week would be copied over. For clarification, each tab is named at number like 27, 28, 29, etc. The last bit of custom work would be to use that same cell value and save the file of the newly consolidated files to a location. The above example is week XX update.xlsm where XX is the cell value. I hope that all makes sense. Thank you in advance! Dza -- 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
Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet
Since the files will be in different locations, you can list them in column B like: C:\temp\Users\test\Desktop\Labour\test\11000.xlsx C:\temp\Users\test\Desktop\Labour\test\11001.xlsx C:\temp\Users\test\Desktop\Labour\test\11002.xlsx C:\temp\Users\test\Desktop\Labour\test\11003.xlsx C:\temp\Users\test\Desktop\Labour\test\11004.xlsx C:\temp\Users\test\Desktop\Labour\test\11005.xlsx then, the macro can loop through this list and collect the sheets: Option Explicit Sub Consolidate_Sheets() Dim ShtNo As String, NewWb, wbName Dim fPath Dim Sht, fso, fld, File Dim R As Integer, nRows As Integer Application.ScreenUpdating = False ' nRows = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(1).Range(B1:B65000)) ' ' Determine sheet to copy ' ShtNo = ThisWorkbook.Sheets(1).Range(A1).Value If (ShtNo X = X) Then MsgBox No Sheet Specified Exit Sub End If ' ' Define new Workbook Name ' NewWb = week ShtNo update ' Workbooks.Add ActiveWorkbook.SaveAs _ Filename:=fPath NewWb .xlsm, _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ CreateBackup:=False ' Set fso = CreateObject(Scripting.FileSystemObject) ' ' Loop through files in column B ' For R = 1 To nRows If (ThisWorkbook.Sheets(1).Cells(R, B).Value X X) Then If (Not fso.fileexists(ThisWorkbook.Sheets(1).Cells(R, B).Value)) Then MsgBox Could not located file: Chr(13) ThisWorkbook.Sheets(1).Cells(R, B).Value Else Set File = fso.getfile(ThisWorkbook.Sheets(1).Cells(R, B).Value) wbName = Replace(UCase(File.Name), .XLSX, ) 'Remove file extension Workbooks.Open Filename:=File.Path Workbooks(File.Name).Sheets(ShtNo).Copy _ After:=Workbooks(NewWb .xlsm).Sheets(Workbooks(NewWb .xlsm).Sheets.Count) ActiveSheet.Name = wbName Workbooks(File.Name).Close savechanges:=False End If End If Next R ' Application.ScreenUpdating = True ' Workbooks(NewWb .xlsm).Close savechanges:=True End Sub 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: dza49 dvpe...@gmail.com To: excel-macros@googlegroups.com Cc: schreiner_p...@att.net Sent: Tuesday, November 4, 2014 9:32 AM Subject: Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet Hello! With regards to consolidation file location point, yes that path (C:\Users\test\Desktop\Labour\test) is where I'd like the file to be saved to in the end. As for the source files if you will, (aka the files maintained by stores). These files will be saved in different folders on the network and yes, I will want to consolidate specific sheets from each store's file. Each file will have a unique name but each of the sheets will be labeled the same way (ie week number like 26, 27, 28, etc.) For the purpose of this test, we can use the assume that the source files and the consolidation macro will all reside in the same directory (C:\Users\test\Desktop\Labour\test). The file names are 11000,xlsx, 11001,xlsx and 11002.xlsx. Becuase in the end the source files will reside in different locations on the network having a macro that consolidates these specific files would be great! Again, the only area I'm struggling with is how to use a cell reference in a macro to then dictate which sheets need to be pulled into the consolidation file. Thanks for your help! On Tuesday, 4 November 2014 13:52:07 UTC, Paul Schreiner wrote: I'm sure I can put together something that will work cleanly. Rather than make assuptions though, I'll ask a couple of questions: in the folder: C:\Users\test\Desktop\Labour\ test will you be consolidating specific sheets from ALL of the files in this location (that do now begin with Week.. or all are type .xlsx?) We can specify the files on the worksheet: 11000 11001 11002 or we can simply process all files in the folder. I'll make some sample files
$$Excel-Macros$$ How to get value in next cell
Hi Group Please find attached file, i am locking for formula like vlookup function. Pls help i want to output value come in next cell like vlookup. example attached.. -- __ Thanks Regards, Bholanath Verma M +234 8141 337 555 E: bholanathve...@gmail.com gtalk ID: bholanathve...@gmail.com Skype ID: bholanath.verma whatsApp:+234 8141 337 555 / +91 990 3030 248 -- 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/d/optout. Book2.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ Re: split text by pipe sign
Hi Sanjay use this function =IF(COLUMN()-2=0,IFERROR(LEFT($A18,FIND(|,$A18)-1),$A18),IFERROR(MID($A18,FIND(~,SUBSTITUTE($A18,|,~,COLUMN()-2))+1,IFERROR(FIND(~,SUBSTITUTE($A18,|,~,COLUMN()-1))-1,LEN($A18))-FIND(~,SUBSTITUTE($A18,|,~,COLUMN()-2))),)) Regards, Anil Kumar Maurya On Wednesday, 15 October 2014 4:11 PM, sanjaiy yadav sanjai99...@gmail.com wrote: Hi, PFA As Required. Regards Sanjai Kumar On Saturday, October 11, 2014 10:19:47 AM UTC+5:30, Waseem Saifi wrote: Hi Experts, I have attached a file which contain text in cell A15. I want to split this text by pipe sign [|] in cells next to cell A15. Thanks in Advance. Regards,Waseem Saifi -- 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/d/optout. -- 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/d/optout.
Re: $$Excel-Macros$$ How to Get Data of 527 employees from One Excel Sheet to another Sheet on given proforma
Hi pfa Regards, Anil Kumar Maurya On Tuesday, 4 November 2014 4:50 PM, M.Rafique MRU imrafiqu...@gmail.com wrote: Dear Excel Experts! Hi, Once again I need your help regarding subject matter, mentioned above. I need Data of 527 employees data on other sheet to another proforma through the help of macros. Which can only be possible if you experts do this for me. I will be very much thankful to you experts for timely help. Note: I'm attaching my excel file here in which my problem is described in detail plz go through my sheet that will give you clear picture of my query. Thanking you in anticipation. __ Muhammad Rafique Ujjan -- 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/d/optout. -- 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/d/optout. Book2.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ how to copy a text from a cell into several rows
Great, that´s just perfect! Thank you very much! What if the initial row with the text to be copied should be deleted after it has been copied?;) I would appreciate if you could help me with this too! Regards Iva Dne pondělí, 3. listopadu 2014 8:38:25 UTC+1 Vabz napsal(a): Iva Check this, do let us know for further help. Cheers! + *I did not do this for you. God is here working through me for you.* On Mon, Nov 3, 2014 at 12:47 AM, ivavok ivana.vok...@gmail.com javascript: wrote: Hello, I would appreciate if you could help me with this macro: The macro should copy a text from a cell which can be one of these: typ(1) = Home/Rent typ(2) = Utilities typ(3) = Food/Groceries typ(4) = Departmental typ(5) = Entertainment typ(6) = Car/Auto typ(7) = Insurance/Medical typ(8) = Misc/One-time typ(9) = Business If it finds one of these texts, then it should copy it into each row in column E until it finds another text from one of these..Then it should do the same with this new text. Please be aware that the first cell to be copied does not need to be always A2. The number of rows can vary. Please find an example of the data and the final outcome. Thank you 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/d/optout. -- 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/d/optout.
$$Excel-Macros$$ Can't show results of a calculation
Hello! I have a problem with this document. I make some simple calculations of ranges but in the last step when I need to get the results I get an error. can someone check the VBA adn find the problem?Thank you -- 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/d/optout. VIX 2jan2001-21may2014.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
$$Excel-Macros$$ Re: How to convert PDF file into excel sheet correctly with same column width rows
just use Adobe Acrobat XI pro, it may convert your pdf into excel On Tuesday, 4 November 2014 17:36:21 UTC+5, MRafique Ujjan wrote: Dear Experts! Hi, I have a problem while i copy data from pdf file to excel... it doesn't copy in same columns rows as shown in pdf file. Therefore please help me in this regard. Here i am attaching a pdf file. Thanks in advance. __ Muhammad Rafique Ujjan -- 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/d/optout.
$$Excel-Macros$$ Re: Four options to every question need to be randomized
I got an answer at excelforum and thought I should update the link http://www.excelforum.com/excel-programming-vba-macros/1047574-shuffle-four-answers-of-one-question-with-a-condition-and-loop-it.html Thank you Sub test() Dim r As Long Dim CA As String For r = 1 To Cells(Rows.Count, A).End(xlUp).Row Step 5 CA = Cells(r, A).Offset(Asc(LCase(Left(Cells(r, A).Value, 1))) - 96, 0).Value Cells(r, A).Offset(1, 1).Resize(4, 1).Formula = =RAND() With ActiveSheet.Sort .SortFields.Clear .SortFields.Add Key:=Cells(r, A).Offset(1, 1).Resize(4, 1), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Cells(r, A).Offset(1, 0).Resize(4, 2) .Header = xlNo .Orientation = xlTopToBottom .Apply End With Cells(r, A).Offset(1, 1).Resize(4, 1).Clear CA = Chr(Application.Match(CA, Cells(r, A).Offset(1, 0).Resize(4, 1), False) + 96) Cells(r, A).Value = CA Right(Cells(r, A).Value, Len(Cells(r, A).Value) - 1) Next r End Sub On Tuesday, November 4, 2014 6:58:41 AM UTC+5:30, Rajnish Malhotra wrote: In the attached excel file (version 2007), the first sheet is quiz and second sheet is sheet1. It has a question/answer sheet (sheet1) that is designed in such a way that the first letter of the question is prefixed with the answer. For example here is a sample data in the range A1:A20 which is also attached. Answer to first question is b hence the question is prefixed with b. bWho is a cricketer? Tiger Woods Sachin Tendulkar Will Smith Bill Gates aWho is a Golfer Tiger Woods Sachin Tendulkar Will Smith Bill Gates dWho is an Entrepreneur Tiger Woods Sachin Tendulkar Will Smith Bill Gates cWho is an actor Tiger Woods Sachin Tendulkar Will Smith Bill Gates I need to shuffle/jumble the four options for every question (in sheet1) whenever the workbook is opened without changing the logic of prefixing answer to the question's first letter and keeping all other things intact. Please help me how can I achieve this. What kind of code can be written and which section of vba (sheet/module)? Here is an example of question # 1 how it should be after randomization (on file open). cWho is a cricketer? Will Smith Bill Gates Sachin Tendulkar Tiger Woods Here, the options are shuffled/jumbled and also the correct answer is updated in the question (prefixed by 'c' which was earlier 'b'). I was looking at the chip article at http://www.cpearson.com/excel/ShuffleArray.aspx but I am not able to implement this in my question. I also tried finding source on how we can make use of these functions that suits my question to no avail. I am still trying... even if i randomize the four options, how can i get the correct answer sequence (a, b, c, d) to prefix in the question is another tough task. Appreciate some help here. Thank you! -- 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/d/optout.
Re: $$Excel-Macros$$ how to copy a text from a cell into several rows
here you go... Cheers!! + *I did not do this for you. God is here working through me for you.* On Wed, Nov 5, 2014 at 2:00 AM, ivavok ivana.vokrouhlik...@gmail.com wrote: Great, that´s just perfect! Thank you very much! What if the initial row with the text to be copied should be deleted after it has been copied?;) I would appreciate if you could help me with this too! Regards Iva Dne pondělí, 3. listopadu 2014 8:38:25 UTC+1 Vabz napsal(a): Iva Check this, do let us know for further help. Cheers! + *I did not do this for you. God is here working through me for you.* On Mon, Nov 3, 2014 at 12:47 AM, ivavok ivana.vok...@gmail.com wrote: Hello, I would appreciate if you could help me with this macro: The macro should copy a text from a cell which can be one of these: typ(1) = Home/Rent typ(2) = Utilities typ(3) = Food/Groceries typ(4) = Departmental typ(5) = Entertainment typ(6) = Car/Auto typ(7) = Insurance/Medical typ(8) = Misc/One-time typ(9) = Business If it finds one of these texts, then it should copy it into each row in column E until it finds another text from one of these..Then it should do the same with this new text. Please be aware that the first cell to be copied does not need to be always A2. The number of rows can vary. Please find an example of the data and the final outcome. Thank you 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/d/optout. -- 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/d/optout. -- 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
Re: $$Excel-Macros$$ Re: Four options to every question need to be randomized
Thanks for posting. + *I did not do this for you. God is here working through me for you.* On Wed, Nov 5, 2014 at 9:40 AM, Rajnish Malhotra freeofcosthotels...@gmail.com wrote: I got an answer at excelforum and thought I should update the link http://www.excelforum.com/excel-programming-vba-macros/1047574-shuffle-four-answers-of-one-question-with-a-condition-and-loop-it.html Thank you Sub test() Dim r As Long Dim CA As String For r = 1 To Cells(Rows.Count, A).End(xlUp).Row Step 5 CA = Cells(r, A).Offset(Asc(LCase(Left(Cells(r, A).Value, 1))) - 96, 0).Value Cells(r, A).Offset(1, 1).Resize(4, 1).Formula = =RAND() With ActiveSheet.Sort .SortFields.Clear .SortFields.Add Key:=Cells(r, A).Offset(1, 1).Resize(4, 1), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Cells(r, A).Offset(1, 0).Resize(4, 2) .Header = xlNo .Orientation = xlTopToBottom .Apply End With Cells(r, A).Offset(1, 1).Resize(4, 1).Clear CA = Chr(Application.Match(CA, Cells(r, A).Offset(1, 0).Resize(4, 1), False) + 96) Cells(r, A).Value = CA Right(Cells(r, A).Value, Len(Cells(r, A).Value) - 1) Next r End Sub On Tuesday, November 4, 2014 6:58:41 AM UTC+5:30, Rajnish Malhotra wrote: In the attached excel file (version 2007), the first sheet is quiz and second sheet is sheet1. It has a question/answer sheet (sheet1) that is designed in such a way that the first letter of the question is prefixed with the answer. For example here is a sample data in the range A1:A20 which is also attached. Answer to first question is b hence the question is prefixed with b. bWho is a cricketer? Tiger Woods Sachin Tendulkar Will Smith Bill Gates aWho is a Golfer Tiger Woods Sachin Tendulkar Will Smith Bill Gates dWho is an Entrepreneur Tiger Woods Sachin Tendulkar Will Smith Bill Gates cWho is an actor Tiger Woods Sachin Tendulkar Will Smith Bill Gates I need to shuffle/jumble the four options for every question (in sheet1) whenever the workbook is opened without changing the logic of prefixing answer to the question's first letter and keeping all other things intact. Please help me how can I achieve this. What kind of code can be written and which section of vba (sheet/module)? Here is an example of question # 1 how it should be after randomization (on file open). cWho is a cricketer? Will Smith Bill Gates Sachin Tendulkar Tiger Woods Here, the options are shuffled/jumbled and also the correct answer is updated in the question (prefixed by 'c' which was earlier 'b'). I was looking at the chip article at http://www.cpearson.com/excel/ ShuffleArray.aspx but I am not able to implement this in my question. I also tried finding source on how we can make use of these functions that suits my question to no avail. I am still trying... even if i randomize the four options, how can i get the correct answer sequence (a, b, c, d) to prefix in the question is another tough task. Appreciate some help here. Thank you! -- 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/d/optout. -- 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
