Excel-Macro Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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..
 
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:
 
Sorry Stefi, u r right!.it is 16.6 and 8.3


Stefi said:
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:
Click to expand...
 
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:
 
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!!
 
Here you are:

Sub test()
Worksheets("Sheet1").Range("A1").Value = "reqslist"
Worksheets("Sheet2").Range("A1").Value = "reqslist"
lastr_sh1 = Worksheets("Sheet1").Columns("A:A").End(xlDown).Row
lastr_sh2 = Worksheets("Sheet2").Columns("A:A").End(xlDown).Row
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).Row
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).Row
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:
 
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
 
Sub test_caller()
Call Par_test(2, 24)
End Sub

Sub Par_test(startr_sh1, startr_sh2)
Worksheets("Sheet1").Range("B1").Value = "reqslist"
Worksheets("Sheet2").Range("B1").Value = "reqslist"
lastr_sh1 = Worksheets("Sheet1").Range("B" & startr_sh1 & ":B" &
Rows.Count).End(xlDown).Row
lastr_sh2 = Worksheets("Sheet2").Range("B" & startr_sh2 & ":B" &
Rows.Count).End(xlDown).Row
Worksheets("Sheet1").Range("B" & startr_sh1 & ":B" & lastr_sh1).Copy _
Destination:=Worksheets("Sheet3").Range("A2")
Worksheets("Sheet2").Range("B" & startr_sh2 & ":B" & lastr_sh2).Copy _
Destination:=Worksheets("Sheet3").Range("A" & lastr_sh1 + 1)
Worksheets("Sheet3").Select
Range("A1").Value = "reqslist" 'column A must have a header to make
"AdvancedFilter Action:=xlFilterInPlace, Unique:=True" work well!
lastr_sh3 = Worksheets("Sheet3").Columns("A:A").End(xlDown).Row
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).Row
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
 
Back
Top