I require some help with a section of the following code.

[tracks via OfficeKB.com]

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,J339,R2:X39,B43:H80,J4380,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("C913,C1517,C1936,C3944,F9:G13,F15:G17,
F19:G36,F38:G44,E9:E13,E15:E17,E20:E30,E38:E44").Select
Selection.ClearContents


End Sub


I thank you for your time and help

Rick Mason(TRACKS)

 

[wisccal]

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]

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

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.aspx/excel-programming/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

 

[wisccal]

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 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

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.aspx/excel-programming/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

 

[tracks via OfficeKB.com]

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

[quoted text clipped - 52 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1

 

[tracks via OfficeKB.com]

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
[quoted text clipped - 52 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1

Hi, Steve: I copied everything into my code , I get a compiler error:- syntax
error at
Private Sub copyToSummary() line.

 

[wisccal]

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 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 Rick,

[quoted text clipped - 52 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1

Hi, Steve: I copied everything into my code , I get a compiler error:- syntax
error at
Private Sub copyToSummary() line.

 

[tracks via OfficeKB.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
'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

[quoted text clipped - 30 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1

 

[tracks via OfficeKB.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
'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

[quoted text clipped - 30 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1

 

[wisccal]

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
'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.aspx/excel-programming/200611/1

 

[tracks via OfficeKB.com]

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.aspx/excel-programming/200611/1

 

[wisccal]

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

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.aspx/excel-programming/200611/1

 

[tracks via OfficeKB.com]

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.aspx/excel-programming/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