|
I require some help with a section of the following code.
I have been able to put together the code as follows with much help and
patience from the people of this site, alittle additional assistance would be greatly appreciated the code works well until the if statement the then part. the range("A3:G9") is filled over the period of one month, when the month changes to the next month (ie the ifstatement) the range ("A3:G39") is copied to Worksheet("Calender Summary"). the first range on that sheet is (B3: H39) the next month range is dimensions 7columns by 37 rows. the layout for the mnths on worksheet(Calender Summary) is three months across by 4 down. i tried using offset and was unsuer how set intial variables Worksheets("CRANE WT SUMMARY").Range("A3:G39").Select Selection.Copy _ Worksheets("CALENDER SUMMARY").Range("B3:H39").Offset(0, 0) the range blocks are B3:H39,J3:P39,R2:X39,B43:H80,J43:P80,R43:X80 etc b CO() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Worksheets("DAILY CRANE INFO").Range("I7") With Worksheets("CRANE WT SUMMARY") Set rng2 = .Cells(.Rows.Count, 1).End(xlUp) End With If Month(rng1) Month(rng2) Then Worksheets("CRANE WT SUMMARY").Range("A3:G39").Select Selection.Copy _ Worksheets("CALENDER SUMMARY").Range("B3:H39").Offset(0, 0) Worksheets("CRANE WT SUMMARY").Range("A7:G31").Select Selection.ClearContents Call Sheet2.TEST Else: Call Sheet2.TEST End If End Sub Sub TEST() Dim DestCell As Range With Worksheets("crane wt summary") Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0) End With With Worksheets("daily crane info") .Range("I7").Copy DestCell.PasteSpecial Paste:=xlPasteValues .Range("AA15:AF15").Copy DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues End With Worksheets("DAILY PRODUCTION").Select ActiveSheet.Range("C9:D13,C15:D17,C19:D36,C39:D44, F9:G13,F15:G17, F19:G36,F38:G44,E9:E13,E15:E17,E20:E30,E38:E44").S elect Selection.ClearContents End Sub I thank you for your time and help Rick Mason(TRACKS) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 |
I require some help with a section of the following code.
Hi Rick,
If I understand you correctly, then you want range A3:G39 copied over to calendar summary without overwriting the previous month. Try something like this: '==================================== 'rngFrom: the range to be copied 'clTo: the upper-left-corner cell of the summary range 'across: the number of columns across 'month: the month to be copied Private Sub copyToSummary(ByVal rngFrom As Excel.Range, ByVal clTo As Excel.Range, across As Integer, ByVal month As Integer) Dim cols As Integer, rows As Integer, a As Integer, d As Integer cols = rngFrom.Columns.Count rows = rngFrom.rows.Count a = ((month - 1) Mod across) * cols d = (Fix((month - 1) / across)) * rows Set clTo = clTo.Offset(d, a) rngFrom.Copy clTo End Sub '==================================== You would then call it like so: copyToSummary Worksheets("CRANE WT SUMMARY").Range("A3:G39"), Worksheets("CALENDER SUMMARY").Range("B3"), 3, Month(rng1) Regards, Steve tracks via OfficeKB.com schrieb: I have been able to put together the code as follows with much help and patience from the people of this site, alittle additional assistance would be greatly appreciated the code works well until the if statement the then part. the range("A3:G9") is filled over the period of one month, when the month changes to the next month (ie the ifstatement) the range ("A3:G39") is copied to Worksheet("Calender Summary"). the first range on that sheet is (B3: H39) the next month range is dimensions 7columns by 37 rows. the layout for the mnths on worksheet(Calender Summary) is three months across by 4 down. i tried using offset and was unsuer how set intial variables Worksheets("CRANE WT SUMMARY").Range("A3:G39").Select Selection.Copy _ Worksheets("CALENDER SUMMARY").Range("B3:H39").Offset(0, 0) the range blocks are B3:H39,J3:P39,R2:X39,B43:H80,J43:P80,R43:X80 etc b CO() Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Worksheets("DAILY CRANE INFO").Range("I7") With Worksheets("CRANE WT SUMMARY") Set rng2 = .Cells(.Rows.Count, 1).End(xlUp) End With If Month(rng1) Month(rng2) Then Worksheets("CRANE WT SUMMARY").Range("A3:G39").Select Selection.Copy _ Worksheets("CALENDER SUMMARY").Range("B3:H39").Offset(0, 0) Worksheets("CRANE WT SUMMARY").Range("A7:G31").Select Selection.ClearContents Call Sheet2.TEST Else: Call Sheet2.TEST End If End Sub Sub TEST() Dim DestCell As Range With Worksheets("crane wt summary") Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0) End With With Worksheets("daily crane info") .Range("I7").Copy DestCell.PasteSpecial Paste:=xlPasteValues .Range("AA15:AF15").Copy DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues End With Worksheets("DAILY PRODUCTION").Select ActiveSheet.Range("C9:D13,C15:D17,C19:D36,C39:D44, F9:G13,F15:G17, F19:G36,F38:G44,E9:E13,E15:E17,E20:E30,E38:E44").S elect Selection.ClearContents End Sub I thank you for your time and help Rick Mason(TRACKS) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 |
I require some help with a section of the following code.
Hi Rick,
First, paste the private sub copyToSummary() into the same module as your existing Sub CO() (before or after doesn't matter). Then replace the following lines of code in CO(): Worksheets("CRANE WT SUMMARY").Range("A3:G39").Select Selection.Copy _ Worksheets("CALENDER SUMMARY").Range("B3:H39").Offset(0, 0) with copyToSummary Worksheets("CRANE WT SUMMARY").Range("A3:G39"), Worksheets("CALENDER SUMMARY").Range("B3"), 3, Month(rng1) This should make it work. Regards, Steve tracks via OfficeKB.com schrieb: wrote: Hi Rick, If I understand you correctly, then you want range A3:G39 copied over to calendar summary without overwriting the previous month. Try something like this: '==================================== 'rngFrom: the range to be copied 'clTo: the upper-left-corner cell of the summary range 'across: the number of columns across 'month: the month to be copied Private Sub copyToSummary(ByVal rngFrom As Excel.Range, ByVal clTo As Excel.Range, across As Integer, ByVal month As Integer) Dim cols As Integer, rows As Integer, a As Integer, d As Integer cols = rngFrom.Columns.Count rows = rngFrom.rows.Count a = ((month - 1) Mod across) * cols d = (Fix((month - 1) / across)) * rows Set clTo = clTo.Offset(d, a) rngFrom.Copy clTo End Sub '==================================== You would then call it like so: copyToSummary Worksheets("CRANE WT SUMMARY").Range("A3:G39"), Worksheets("CALENDER SUMMARY").Range("B3"), 3, Month(rng1) Regards, Steve tracks via OfficeKB.com schrieb: I have been able to put together the code as follows with much help and patience from the people of this site, alittle additional assistance would be [quoted text clipped - 71 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 Hi, Steve : thanks for the prompt response, I do not understand where i enter the code you gave me. Does the private sub and the preamble go ahead of my 1st Sub and the read in statement after the "then" in the IF statement. Sorry I am real new at this. THANKS !! Rick -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 |
I require some help with a section of the following code.
wrote:
Hi Rick, First, paste the private sub copyToSummary() into the same module as your existing Sub CO() (before or after doesn't matter). Then replace the following lines of code in CO(): Worksheets("CRANE WT SUMMARY").Range("A3:G39").Select Selection.Copy _ Worksheets("CALENDER SUMMARY").Range("B3:H39").Offset(0, 0) with copyToSummary Worksheets("CRANE WT SUMMARY").Range("A3:G39"), Worksheets("CALENDER SUMMARY").Range("B3"), 3, Month(rng1) This should make it work. Regards, Steve Hi Steve i will try that and let you know how it work. Thanks again Rick tracks via OfficeKB.com schrieb: Hi Rick, [quoted text clipped - 52 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 -- Message posted via http://www.officekb.com |
I require some help with a section of the following code.
wrote:
Hi Rick, First, paste the private sub copyToSummary() into the same module as your existing Sub CO() (before or after doesn't matter). Then replace the following lines of code in CO(): Worksheets("CRANE WT SUMMARY").Range("A3:G39").Select Selection.Copy _ Worksheets("CALENDER SUMMARY").Range("B3:H39").Offset(0, 0) with copyToSummary Worksheets("CRANE WT SUMMARY").Range("A3:G39"), Worksheets("CALENDER SUMMARY").Range("B3"), 3, Month(rng1) This should make it work. Regards, Steve tracks via OfficeKB.com schrieb: Hi Rick, [quoted text clipped - 52 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 Hi, Steve: I copied everything into my code , I get a compiler error:- syntax error at Private Sub copyToSummary() line. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 |
I require some help with a section of the following code.
I'm sorry about that, that's probably the formatting that got screwed
up when I posted the code. You've got to remove the line break after 'ByVal clTo As'. I posted the macro as text file to the following address: http://swissbeton.com/stk/copyToSummary_macro.txt If you copy it from there, you shouldn't have any problems running the macro. Regards, Steve tracks via OfficeKB.com schrieb: wrote: Hi Rick, First, paste the private sub copyToSummary() into the same module as your existing Sub CO() (before or after doesn't matter). Then replace the following lines of code in CO(): Worksheets("CRANE WT SUMMARY").Range("A3:G39").Select Selection.Copy _ Worksheets("CALENDER SUMMARY").Range("B3:H39").Offset(0, 0) with copyToSummary Worksheets("CRANE WT SUMMARY").Range("A3:G39"), Worksheets("CALENDER SUMMARY").Range("B3"), 3, Month(rng1) This should make it work. Regards, Steve tracks via OfficeKB.com schrieb: Hi Rick, [quoted text clipped - 52 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 Hi, Steve: I copied everything into my code , I get a compiler error:- syntax error at Private Sub copyToSummary() line. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 |
I require some help with a section of the following code.
wrote:
I'm sorry about that, that's probably the formatting that got screwed up when I posted the code. You've got to remove the line break after 'ByVal clTo As'. I posted the macro as text file to the following address: http://swissbeton.com/stk/copyToSummary_macro.txt If you copy it from there, you shouldn't have any problems running the macro. Regards, Steve Hi, Steve : removing the line break seems to havr fix the problem. I worked it through the debugger and got no addition errors. I will test with my backlog of production files. your help and patience greatly appreciated have good day Rick tracks via OfficeKB.com schrieb: Hi Rick, [quoted text clipped - 30 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 |
I require some help with a section of the following code.
wrote:
I'm sorry about that, that's probably the formatting that got screwed up when I posted the code. You've got to remove the line break after 'ByVal clTo As'. I posted the macro as text file to the following address: http://swissbeton.com/stk/copyToSummary_macro.txt If you copy it from there, you shouldn't have any problems running the macro. Regards, Steve tracks via OfficeKB.com schrieb: Hi Steve: i started running daily production through the macro, when i reach the copyTo Summary, I error message "9" subscript out of range. i have a question the month that is being copied to "calender summary" is the rng2 month? thanks Rick Hi Rick, [quoted text clipped - 30 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 -- Message posted via http://www.officekb.com |
I require some help with a section of the following code.
Good morning Rick,
The range that is copied by copyToSummary is Worksheets("CRANE WT SUMMARY").Range("A3:G39"). It's the first argument to the function. I'm not sure why you are getting an out of range error. On what line does this error occur? If it is in copyToSummary, what is the value for the month that is being passed to the function (last parameter)? If it is 0, then you need to remove the '-1' in the following two lines: a = ((month - 1) Mod across) * cols 'remove -1 d = (Fix((month - 1) / across)) * rows 'remove -1 so that it looks like so: a = ((month) Mod across) * cols d = (Fix((month) / across)) * rows I'm sorry if it turns out that this was the problem. I assumed the month values were from 1-12, not 0-11. If the problem persists, please post back. Regards, Steve www.swiss-ins.com tracks via OfficeKB.com wrote: wrote: I'm sorry about that, that's probably the formatting that got screwed up when I posted the code. You've got to remove the line break after 'ByVal clTo As'. I posted the macro as text file to the following address: http://swissbeton.com/stk/copyToSummary_macro.txt If you copy it from there, you shouldn't have any problems running the macro. Regards, Steve tracks via OfficeKB.com schrieb: Hi Steve: i started running daily production through the macro, when i reach the copyTo Summary, I error message "9" subscript out of range. i have a question the month that is being copied to "calender summary" is the rng2 month? thanks Rick Hi Rick, [quoted text clipped - 30 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 -- Message posted via http://www.officekb.com |
I require some help with a section of the following code.
wrote:
Good morning Rick, The range that is copied by copyToSummary is Worksheets("CRANE WT SUMMARY").Range("A3:G39"). It's the first argument to the function. I'm not sure why you are getting an out of range error. On what line does this error occur? If it is in copyToSummary, what is the value for the month that is being passed to the function (last parameter)? If it is 0, then you need to remove the '-1' in the following two lines: a = ((month - 1) Mod across) * cols 'remove -1 d = (Fix((month - 1) / across)) * rows 'remove -1 so that it looks like so: a = ((month) Mod across) * cols d = (Fix((month) / across)) * rows I'm sorry if it turns out that this was the problem. I assumed the month values were from 1-12, not 0-11. If the problem persists, please post back. Regards, Steve www.swiss-ins.com GoodMorning Steve, the months are to be 1-12, in my code rng2 would the May and the new month June would be rng1, therefore the month to be copied would be May ie rng2, sorry if this was not clear before. I worked the values for "a" and "d" to make sure I hadn't done something to get the wrong valus everything was good. i get the error on the copyToSummary line after the if statement in the code. I have looked at some of the causes of the error 9 message and there are many. i have tried to find one that might explain the cause in the code you gave me as i should work ok. will keep hunting thaks again Rick I'm sorry about that, that's probably the formatting that got screwed up when I posted the code. You've got to remove the line break after [quoted text clipped - 21 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 |
I require some help with a section of the following code.
I belive I found the culprit: Calendar was misspelled as CalendEr. So,
we change copyToSummary to: copyToSummary Worksheets("CRANE WT SUMMARY").Range("A3:G39"), Worksheets("CALENDAR SUMMARY").Range("B3"), 3, Month(rng2) This should alleviate the problem. Again, don't forget to remove the line break after ...Range("A3:G39"), . Alternatively, you can also insert a space followed by an underline character like so: copyToSummary Worksheets(...<code..., _ ...<code... This tells VB to disregard the line break. Regards, Steve www.swiss-ins.com tracks via OfficeKB.com wrote: wrote: Good morning Rick, The range that is copied by copyToSummary is Worksheets("CRANE WT SUMMARY").Range("A3:G39"). It's the first argument to the function. I'm not sure why you are getting an out of range error. On what line does this error occur? If it is in copyToSummary, what is the value for the month that is being passed to the function (last parameter)? If it is 0, then you need to remove the '-1' in the following two lines: a = ((month - 1) Mod across) * cols 'remove -1 d = (Fix((month - 1) / across)) * rows 'remove -1 so that it looks like so: a = ((month) Mod across) * cols d = (Fix((month) / across)) * rows I'm sorry if it turns out that this was the problem. I assumed the month values were from 1-12, not 0-11. If the problem persists, please post back. Regards, Steve www.swiss-ins.com GoodMorning Steve, the months are to be 1-12, in my code rng2 would the May and the new month June would be rng1, therefore the month to be copied would be May ie rng2, sorry if this was not clear before. I worked the values for "a" and "d" to make sure I hadn't done something to get the wrong valus everything was good. i get the error on the copyToSummary line after the if statement in the code. I have looked at some of the causes of the error 9 message and there are many. i have tried to find one that might explain the cause in the code you gave me as i should work ok. will keep hunting thaks again Rick I'm sorry about that, that's probably the formatting that got screwed up when I posted the code. You've got to remove the line break after [quoted text clipped - 21 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 |
I require some help with a section of the following code.
wrote:
Good morning Rick, The range that is copied by copyToSummary is Worksheets("CRANE WT SUMMARY").Range("A3:G39"). It's the first argument to the function. I'm not sure why you are getting an out of range error. On what line does this error occur? If it is in copyToSummary, what is the value for the month that is being passed to the function (last parameter)? If it is 0, then you need to remove the '-1' in the following two lines: a = ((month - 1) Mod across) * cols 'remove -1 d = (Fix((month - 1) / across)) * rows 'remove -1 so that it looks like so: a = ((month) Mod across) * cols d = (Fix((month) / across)) * rows I'm sorry if it turns out that this was the problem. I assumed the month values were from 1-12, not 0-11. If the problem persists, please post back. Regards, Steve www.swiss-ins.com I'm sorry about that, that's probably the formatting that got screwed up when I posted the code. You've got to remove the line break after [quoted text clipped - 21 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 Hi Steve just a short note to let you know i got my code working due to yours and others much appreciated help. i could not correct the error for the private sub you gave. So i took your basic concept and code and with some hard work got it to do what i wanted it to do. Thanks again for your help and assisstance, rick Mason -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
| All times are GMT +1. The time now is 04:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com