Excel-Macro Problem
Thanks Stefi,
But could u give me a genaralized (which can have any value for data)
function which meets the following requirements:
Sheet1 --values start from B6, L6
Sheet2---values start from B24, L24
Output Sheet
Sheet3--values start from A2, B2,C2
"Stefi" wrote:
Here you a
Sub test()
Worksheets("Sheet1").Range("A1").Value = "reqslist"
Worksheets("Sheet2").Range("A1").Value = "reqslist"
lastr_sh1 = Worksheets("Sheet1").Columns("A:A").End(xlDown).Ro w
lastr_sh2 = Worksheets("Sheet2").Columns("A:A").End(xlDown).Ro w
Worksheets("Sheet1").Range("A2:A" & lastr_sh1).Copy
Destination:=Worksheets("Sheet3").Range("A2")
Worksheets("Sheet2").Range("A2:A" & lastr_sh2).Copy
Destination:=Worksheets("Sheet3").Range("A" & lastr_sh1 + 1)
Worksheets("Sheet3").Select
Range("A1").Value = "reqslist"
lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Ro w
Columns("A:A").Select
Range("A1:A" & lastr_sh3).AdvancedFilter Action:=xlFilterInPlace,
Unique:=True
Selection.CurrentRegion.Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.ShowAllData
Columns("A").Delete
lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Ro w
Range("B2").Formula = "=SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L)"
Range("C2").Formula = "=SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L)"
Range("B2:C2").AutoFill Destination:=Range("B2:C" & lastr_sh3),
Type:=xlFillDefault
End Sub
Regards,
Stefi
€žSR€ ezt Ã*rta:
Hi,
I need a macro to relect the values in column B of sheet 1 and sheet 2 to be
incorporated without duplicates in column A of sheet3.This needs to be done
along with the summation of values from column L of sheet1 and sheet2 in to
sheet3(column B and column C).
Pls help!!
"Stefi" wrote:
List all instances of Reqn in Sheet3 column A:
Sheet3
Colm A Colm B (as per sheet1) Colm C (as per sheet2)
Req1 formula1 formula2
Req2
Drag down formulae as required!
formula1: =SUMIF(Sheet1!$B:$B,$A2,Sheet1!$L:$L)
formula2: =SUMIF(Sheet2!$B:$B,$A2,Sheet2!$L:$L)
Regards,
Stefi
€žSR€ ezt Ã*rta:
Sorry Stefi, u r right!.it is 16.6 and 8.3
"Stefi" wrote:
Rule of generating Sheet3 is not clear! If you want to sum up values in rows
with Req1, Req2 in column A (like 10.3, 8.5 in column C show), then Sheet3
column B should contain 16.6, 8.3 (and not 8.6, 8.3)! Is it true?
Regards,
Stefi
€žSR€ ezt Ã*rta:
Pls help me find soln to the below problem:
Requirement:
Sheet1
Colm B Colm L
Req1 8.2
Req2 8.3
Req1 8.4
Sheet2
Colm B Colm L
Req1 5.1
Req2 8.5
Req1 5.2
Upon Button click of Sheet2, Sheet3 values should be as shown below:
Sheet3
Colm A Colm B (as per sheet1) Colm C (as per sheet2)
Req1 8.6 10.3
Req2 8.3 8.5
Pls guide..
|