$$Excel-Macros$$ Need some vba code to copy and paste automatically when the file is opened, only last day of month
the data I am copying always lies in A1:F5 On the last day of the month I need to copy B1:F5 to B10:F15 and put the month name in B9; after that it would go to the next empty column (in this case E) and paste again for the next month. -- 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: Continuation of VBA for Excel to Word
Still getting error 462 every other time I run the macro. not sure how to beat this error. On Monday, October 21, 2013 8:21:02 AM UTC-7, Justin Pulley wrote: I don't know how to mark something uncompleted. The code works every other time. code Sub CreateReportPDF() Dim wdapp As Word.Application Dim wddoc As Word.Document Run CopyData Set wdapp = Word.Application Set wddoc = wdapp.documents.Add Application.ScreenUpdating = False wdapp.Visible = True With wddoc.PageSetup .Orientation = wdOrientLandscape .PageWidth = InchesToPoints(17) .PageHeight = InchesToPoints(11) End With For Each sh In Worksheets(Array(Power Report, ATS Data)) ThisWorkbook.Sheets(sh.Name).UsedRange.Copy With wdapp .Visible = True .Selection.Paste End With Next Application.CutCopyMode = False Application.ScreenUpdating = True wddoc.SaveAs2 MyDocuments \ Power Report Format((Date), mm - dd - yy) Format((Time), hhmm) 'change file name as u like MsgBox The power report has been generated and saved to your My Documents Folder wddoc.Close wdapp.Quit Set wddoc = Nothing Set wdapp = Nothing End Sub /code The first time you run the routine it works no issues. The next time you run the routine it fails on error 462. Then you can run it again and it's fine. Error 462 occurs every other time. I have tried every fix I can find for that error and none of it seems to work. There are some sites that claim you should not use a With command when using a word application object. -- 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.
$$Excel-Macros$$ Continuation of VBA for Excel to Word
I don't know how to mark something uncompleted. The code works every other time. code Sub CreateReportPDF() Dim wdapp As Word.Application Dim wddoc As Word.Document Run CopyData Set wdapp = Word.Application Set wddoc = wdapp.documents.Add Application.ScreenUpdating = False wdapp.Visible = True With wddoc.PageSetup .Orientation = wdOrientLandscape .PageWidth = InchesToPoints(17) .PageHeight = InchesToPoints(11) End With For Each sh In Worksheets(Array(Power Report, ATS Data)) ThisWorkbook.Sheets(sh.Name).UsedRange.Copy With wdapp .Visible = True .Selection.Paste End With Next Application.CutCopyMode = False Application.ScreenUpdating = True wddoc.SaveAs2 MyDocuments \ Power Report Format((Date), mm - dd - yy) Format((Time), hhmm) 'change file name as u like MsgBox The power report has been generated and saved to your My Documents Folder wddoc.Close wdapp.Quit Set wddoc = Nothing Set wdapp = Nothing End Sub /code The first time you run the routine it works no issues. The next time you run the routine it fails on error 462. Then you can run it again and it's fine. Error 462 occurs every other time. I have tried every fix I can find for that error and none of it seems to work. There are some sites that claim you should not use a With command when using a word application object. -- 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$$ VBA for Excel to Word
Nevermind, I got it. For some reason there was a bunch of used rows in there. On Friday, October 18, 2013 7:21:50 AM UTC-7, Justin Pulley wrote: So I am going to have to upload the entire sheet I guess because when I run your code in the full workbook I end up with 5 blank pages of empty rows in the word document, but when I run it with just the sample sheet you provide the code in it doesn't do that. I am not sure why the whole sheet is doing that. On Thursday, October 17, 2013 9:37:23 PM UTC-7, ravinder negi wrote: Yes u can use this statement For Each sh In Worksheets(ARRAY(Power Report, ATS Data)) if you want only transfer these two sheets. I have updated attached file. *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *Justin Pulley *Sent:* Thursday, October 17, 2013 8:36 PM *To:* excel-...@googlegroups.com *Cc:* koul@gmail.com; Soum *Subject:* Re: $$Excel-Macros$$ VBA for Excel to Word What if I only wanted to copy worksheets power report and ats data out of a workbook with 12 other sheets in addition to those? looks like I need to modify the For Each statement. So perhaps like this For Each sh In Worksheets(ARRAY(Power Report, ATS Data)) ? On Thursday, October 17, 2013 3:45:10 AM UTC-7, ravinder negi wrote: PFA…first click on button and it will transfer data from excel to word and ask for saving files. You can change file name as per choice (coding). *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *Justin Pulley *Sent:* Thursday, October 17, 2013 12:02 AM *To:* excel-...@googlegroups.com *Cc:* koul@gmail.com *Subject:* Re: $$Excel-Macros$$ VBA for Excel to Word I have manually moved the excel data to a word file to show how I am hoping to get it to come out. Worst case scenario is I have to manually do it all the time, but I was hoping I could automate it. I have also made a copy of the two sheets I want to use to make the word document and put them in an excel spreadsheet, but normally they are part of a larger workbook. On Wednesday, October 16, 2013 10:49:32 AM UTC-7, ashish wrote: Can u share sample workbook n output format in word document Sent on my BlackBerry® from Vodafone -- *From: *Justin Pulley maveric...@gmail.com *Sender: *excel-...@googlegroups.com *Date: *Wed, 16 Oct 2013 10:47:40 -0700 (PDT) *To: *excel-...@googlegroups.com *ReplyTo: *excel-...@googlegroups.com *Subject: *$$Excel-Macros$$ VBA for Excel to Word Is it possible to write a macro that exports two worksheets out of a workbook to Word? currently I export to .pdf which I thought was fine, but my boss asked for an export to word. The challenge is one of the worksheets contains charts that need to go with it. current code ThisWorkbook.Sheets(Array(Power Report, ATS Data)).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ MyDocuments \ Power Report .pdf, Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=True The power report sheet has the two charts on it. Both worksheets are landscape. Power report uses A1:O7 (that includes space for the charts) ATS data varies in the range it uses, but it starts at A1 and goes to H (last used row) At the top of Power Report worksheet it has 5 empty lines for entering notes, I could put those into a word document as a power report template and then let it paste after that if it makes it easier to do. -- 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. -- 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
Re: $$Excel-Macros$$ VBA for Excel to Word
What if I only wanted to copy worksheets power report and ats data out of a workbook with 12 other sheets in addition to those? looks like I need to modify the For Each statement. So perhaps like this For Each sh In Worksheets(ARRAY(Power Report, ATS Data)) ? On Thursday, October 17, 2013 3:45:10 AM UTC-7, ravinder negi wrote: PFA…first click on button and it will transfer data from excel to word and ask for saving files. You can change file name as per choice (coding). *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *Justin Pulley *Sent:* Thursday, October 17, 2013 12:02 AM *To:* excel-...@googlegroups.com javascript: *Cc:* koul@gmail.com javascript: *Subject:* Re: $$Excel-Macros$$ VBA for Excel to Word I have manually moved the excel data to a word file to show how I am hoping to get it to come out. Worst case scenario is I have to manually do it all the time, but I was hoping I could automate it. I have also made a copy of the two sheets I want to use to make the word document and put them in an excel spreadsheet, but normally they are part of a larger workbook. On Wednesday, October 16, 2013 10:49:32 AM UTC-7, ashish wrote: Can u share sample workbook n output format in word document Sent on my BlackBerry® from Vodafone -- *From: *Justin Pulley maveric...@gmail.com *Sender: *excel-...@googlegroups.com *Date: *Wed, 16 Oct 2013 10:47:40 -0700 (PDT) *To: *excel-...@googlegroups.com *ReplyTo: *excel-...@googlegroups.com *Subject: *$$Excel-Macros$$ VBA for Excel to Word Is it possible to write a macro that exports two worksheets out of a workbook to Word? currently I export to .pdf which I thought was fine, but my boss asked for an export to word. The challenge is one of the worksheets contains charts that need to go with it. current code ThisWorkbook.Sheets(Array(Power Report, ATS Data)).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ MyDocuments \ Power Report .pdf, Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=True The power report sheet has the two charts on it. Both worksheets are landscape. Power report uses A1:O7 (that includes space for the charts) ATS data varies in the range it uses, but it starts at A1 and goes to H (last used row) At the top of Power Report worksheet it has 5 empty lines for entering notes, I could put those into a word document as a power report template and then let it paste after that if it makes it easier to do. -- 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. -- 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
Re: $$Excel-Macros$$ VBA for Excel to Word
Ok the following code KIND OF works Dim wdapp As New Word.Application Dim wddoc As Word.Document Run CopyData Set wdapp = CreateObject(word.application) Set wddoc = wdapp.documents.Add wdapp.Visible = True With wddoc.PageSetup .Orientation = wdOrientLandscape .PageWidth = InchesToPoints(17) .PageHeight = InchesToPoints(11) End With For Each sh In Worksheets(Array(Power Report, ATS Data)) Worksheets(sh.Name).UsedRange.Copy With wdapp .Visible = True .Selection.Paste End With Next Application.CutCopyMode = False wddoc.SaveAs2 MyDocuments \ Power Report Format((Date), mm - dd - yy) 'change file name as u like MsgBox Done wdapp.Activate I get a bunch of blank empty pages between the pasting of the two sheets of data now though, which it didn't do with your code. I am going to go back and study the differences between the code. On Thursday, October 17, 2013 3:45:10 AM UTC-7, ravinder negi wrote: PFA…first click on button and it will transfer data from excel to word and ask for saving files. You can change file name as per choice (coding). *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *Justin Pulley *Sent:* Thursday, October 17, 2013 12:02 AM *To:* excel-...@googlegroups.com javascript: *Cc:* koul@gmail.com javascript: *Subject:* Re: $$Excel-Macros$$ VBA for Excel to Word I have manually moved the excel data to a word file to show how I am hoping to get it to come out. Worst case scenario is I have to manually do it all the time, but I was hoping I could automate it. I have also made a copy of the two sheets I want to use to make the word document and put them in an excel spreadsheet, but normally they are part of a larger workbook. On Wednesday, October 16, 2013 10:49:32 AM UTC-7, ashish wrote: Can u share sample workbook n output format in word document Sent on my BlackBerry® from Vodafone -- *From: *Justin Pulley maveric...@gmail.com *Sender: *excel-...@googlegroups.com *Date: *Wed, 16 Oct 2013 10:47:40 -0700 (PDT) *To: *excel-...@googlegroups.com *ReplyTo: *excel-...@googlegroups.com *Subject: *$$Excel-Macros$$ VBA for Excel to Word Is it possible to write a macro that exports two worksheets out of a workbook to Word? currently I export to .pdf which I thought was fine, but my boss asked for an export to word. The challenge is one of the worksheets contains charts that need to go with it. current code ThisWorkbook.Sheets(Array(Power Report, ATS Data)).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ MyDocuments \ Power Report .pdf, Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=True The power report sheet has the two charts on it. Both worksheets are landscape. Power report uses A1:O7 (that includes space for the charts) ATS data varies in the range it uses, but it starts at A1 and goes to H (last used row) At the top of Power Report worksheet it has 5 empty lines for entering notes, I could put those into a word document as a power report template and then let it paste after that if it makes it easier to do. -- 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. -- 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
Re: $$Excel-Macros$$ Need some help modifying a copy and paste routine
It is somehow attached to the first post in the thread, you'd have to expand all threads to see it. On Saturday, October 12, 2013 7:36:09 AM UTC-7, ashish wrote: attachment is missing On Fri, Oct 11, 2013 at 3:48 AM, Justin Pulley maveric...@gmail.comjavascript: wrote: Was the attached file what you were looking for? On Thursday, October 10, 2013 8:44:55 AM UTC-7, ashish wrote: can u pls share a sample workbook? On Thu, Oct 10, 2013 at 9:05 PM, Justin Pulley maveric...@gmail.comwrote: Here is my current code: Code: Dim sh As Worksheet, sh2 As Worksheet Dim LastRow As Integer Dim LastRow2 As Integer Set sh = Sheets(Raw ATS Data) Set sh2 = Sheets(ATS Data) With sh2 Range(A2:H Rows.Count).Clear End With LastRow = sh.Range(A1).End(xlDown).Row sh.Range(A1:H LastRow).Copy sh2.Range(A1).PasteSpecial xlPasteAll sh2.Range(A1).PasteSpecial xlPasteColumnWidths LastRow2 = sh2.Range(A1).End(xlDown).**Row Range(A1:H LastRow2).Select Selection.Rows.AutoFit Application.CutCopyMode = False Application.DisplayAlerts = False /code I need to modify this so that it looks at the date from the Power Report sheet in cell O1 and then finds all the dates within the last 14 days from the sh sheet in column B (row 2 and on) and then performs the copy on only those rows within that date criteria. Row 1 contains the data headers. There are also categories separator rows which always contain the same names (Static Drop, Back Feed Drop, Flip Failure, and Peripheral Drop) so it looks like Row 1 - Headers Row 2 - Separator w/ label Row 3 - x (current row 36) data Row 37 (currently) - Separator w/ label Row 38 - 43 - data Row 44 (Currently) - Separator w/ label Row 45 - Data I will be adding data to this until the end of the year and then archiving it. So I need a macro that is fluid enough to Always copy the separators and then only the data that has the date as mentioned. my VBA skills are somewhat new and I am struggling with the best way to implement what I want to do. I considered a Match function that looked at the date and then determined if it was within the last 14 days, or an ElseIf type of implementation, or a Do Until. Just need a hand with the most efficient way to modify the code and make it work. -- 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/**discussexcelhttps://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-macroshttp://groups.google.com/group/excel-macros . For more options, visit https://groups.google.com/**groups/opt_outhttps://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
$$Excel-Macros$$ Re: Need some help modifying a copy and paste routine
I guess what I am asking is way more complex than I thought, I can't seem to get a push in the right direction anywhere. -- 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.
$$Excel-Macros$$ Need some help modifying a copy and paste routine
Here is my current code: Code: Dim sh As Worksheet, sh2 As Worksheet Dim LastRow As Integer Dim LastRow2 As Integer Set sh = Sheets(Raw ATS Data) Set sh2 = Sheets(ATS Data) With sh2 Range(A2:H Rows.Count).Clear End With LastRow = sh.Range(A1).End(xlDown).Row sh.Range(A1:H LastRow).Copy sh2.Range(A1).PasteSpecial xlPasteAll sh2.Range(A1).PasteSpecial xlPasteColumnWidths LastRow2 = sh2.Range(A1).End(xlDown).Row Range(A1:H LastRow2).Select Selection.Rows.AutoFit Application.CutCopyMode = False Application.DisplayAlerts = False /code I need to modify this so that it looks at the date from the Power Report sheet in cell O1 and then finds all the dates within the last 14 days from the sh sheet in column B (row 2 and on) and then performs the copy on only those rows within that date criteria. Row 1 contains the data headers. There are also categories separator rows which always contain the same names (Static Drop, Back Feed Drop, Flip Failure, and Peripheral Drop) so it looks like Row 1 - Headers Row 2 - Separator w/ label Row 3 - x (current row 36) data Row 37 (currently) - Separator w/ label Row 38 - 43 - data Row 44 (Currently) - Separator w/ label Row 45 - Data I will be adding data to this until the end of the year and then archiving it. So I need a macro that is fluid enough to Always copy the separators and then only the data that has the date as mentioned. my VBA skills are somewhat new and I am struggling with the best way to implement what I want to do. I considered a Match function that looked at the date and then determined if it was within the last 14 days, or an ElseIf type of implementation, or a Do Until. Just need a hand with the most efficient way to modify the code and make it work. -- 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$$ Need some help modifying a copy and paste routine
Was the attached file what you were looking for? On Thursday, October 10, 2013 8:44:55 AM UTC-7, ashish wrote: can u pls share a sample workbook? On Thu, Oct 10, 2013 at 9:05 PM, Justin Pulley maveric...@gmail.comjavascript: wrote: Here is my current code: Code: Dim sh As Worksheet, sh2 As Worksheet Dim LastRow As Integer Dim LastRow2 As Integer Set sh = Sheets(Raw ATS Data) Set sh2 = Sheets(ATS Data) With sh2 Range(A2:H Rows.Count).Clear End With LastRow = sh.Range(A1).End(xlDown).Row sh.Range(A1:H LastRow).Copy sh2.Range(A1).PasteSpecial xlPasteAll sh2.Range(A1).PasteSpecial xlPasteColumnWidths LastRow2 = sh2.Range(A1).End(xlDown).Row Range(A1:H LastRow2).Select Selection.Rows.AutoFit Application.CutCopyMode = False Application.DisplayAlerts = False /code I need to modify this so that it looks at the date from the Power Report sheet in cell O1 and then finds all the dates within the last 14 days from the sh sheet in column B (row 2 and on) and then performs the copy on only those rows within that date criteria. Row 1 contains the data headers. There are also categories separator rows which always contain the same names (Static Drop, Back Feed Drop, Flip Failure, and Peripheral Drop) so it looks like Row 1 - Headers Row 2 - Separator w/ label Row 3 - x (current row 36) data Row 37 (currently) - Separator w/ label Row 38 - 43 - data Row 44 (Currently) - Separator w/ label Row 45 - Data I will be adding data to this until the end of the year and then archiving it. So I need a macro that is fluid enough to Always copy the separators and then only the data that has the date as mentioned. my VBA skills are somewhat new and I am struggling with the best way to implement what I want to do. I considered a Match function that looked at the date and then determined if it was within the last 14 days, or an ElseIf type of implementation, or a Do Until. Just need a hand with the most efficient way to modify the code and make it work. -- 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.
