| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I require code to run a macro dependant on the date | Excel Discussion (Misc queries) | |||
| I REQUIRE SOME HELP WITH CODE | Excel Programming | |||
| require macro or code for purchase order to do the following: | Excel Worksheet Functions | |||
| Really slow code (just this one section) | Excel Programming | |||
| Require extra funtionality - existing code included | Excel Programming | |||
Hybrid Mode
