J
John Adams
I am having difficulties in getting an @sum placed at a specific cell
location in a row copy macro. The worksheet sorts a users selections of
varying reference numbers and their descriptions. These selections are then
group based on the first two digits of their number and copied to another
worksheet. Here is a sample out put from one iteration of the loop:
A1. B1. C1. D1. E1.
Ref Lable
--Header Group1-- S <---- Sum required
Ref#1 RefLable1 2 2.00 4.00| |
Ref#2 RefLable2 2 3.00 6.00|--|
Ref#3 Reflable3 3 3.00 9.00|
Header Group2
..
..
..
"Header Group 2" would be the start of the second run of the loop. I need
to insert an @sum across from the header line and above the sums of the
reference numbers. The header line is a vlookup at F1 on sheet1. I2:M2
refers to the first row in the grouped reference items. Below is the
section of code utilized for this. Any help is appreciated.
Sub CopyCellRanges()
--- snip ---
Range("F1").Copy
Worksheets(sht2).Select
Worksheets(sht2).Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1,
1).PasteSpecial Paste:=xlPasteValues
Worksheets(sht1).Select
Range("I2:M2").Select
Do While ActiveCell.Value <> ""
flag = True
valuea = ActiveCell.Value
valueb = ActiveCell.Offset(0, 1).Value
beginaddrs = ActiveCell.Address
endaddrs = ActiveCell.End(xlToRight).Address
Range(beginaddrs & ":" & endaddrs).Copy
Sheets(sht2).Select
Worksheets(sht2).Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1,
1).Select
Do While ActiveCell.Value <> ""
If valuea = ActiveCell.Value And valueb = _
ActiveCell.Offset(0, 1).Value Then
flag = False
rowcount = Range("a1").CurrentRegion.Rows.Count
Range("a" & rowcount).Offset(1, 0).Select
Else
ActiveCell.Offset(3, 0).Select
End If
Loop
If flag Then
rowcount = Range("a1").CurrentRegion.Rows.Count
Range("a" & rowcount).Offset(1, 0).PasteSpecial
End If
Sheets(sht1).Select
Range(beginaddrs).Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
--- snip ---
location in a row copy macro. The worksheet sorts a users selections of
varying reference numbers and their descriptions. These selections are then
group based on the first two digits of their number and copied to another
worksheet. Here is a sample out put from one iteration of the loop:
A1. B1. C1. D1. E1.
Ref Lable
--Header Group1-- S <---- Sum required
Ref#1 RefLable1 2 2.00 4.00| |
Ref#2 RefLable2 2 3.00 6.00|--|
Ref#3 Reflable3 3 3.00 9.00|
Header Group2
..
..
..
"Header Group 2" would be the start of the second run of the loop. I need
to insert an @sum across from the header line and above the sums of the
reference numbers. The header line is a vlookup at F1 on sheet1. I2:M2
refers to the first row in the grouped reference items. Below is the
section of code utilized for this. Any help is appreciated.
Sub CopyCellRanges()
--- snip ---
Range("F1").Copy
Worksheets(sht2).Select
Worksheets(sht2).Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1,
1).PasteSpecial Paste:=xlPasteValues
Worksheets(sht1).Select
Range("I2:M2").Select
Do While ActiveCell.Value <> ""
flag = True
valuea = ActiveCell.Value
valueb = ActiveCell.Offset(0, 1).Value
beginaddrs = ActiveCell.Address
endaddrs = ActiveCell.End(xlToRight).Address
Range(beginaddrs & ":" & endaddrs).Copy
Sheets(sht2).Select
Worksheets(sht2).Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1,
1).Select
Do While ActiveCell.Value <> ""
If valuea = ActiveCell.Value And valueb = _
ActiveCell.Offset(0, 1).Value Then
flag = False
rowcount = Range("a1").CurrentRegion.Rows.Count
Range("a" & rowcount).Offset(1, 0).Select
Else
ActiveCell.Offset(3, 0).Select
End If
Loop
If flag Then
rowcount = Range("a1").CurrentRegion.Rows.Count
Range("a" & rowcount).Offset(1, 0).PasteSpecial
End If
Sheets(sht1).Select
Range(beginaddrs).Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
--- snip ---