ExcelBanter - Excel-Macro Problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Excel-Macro Problem (https://www.excelbanter.com/excel-discussion-misc-queries/150811-excel-macro-problem.html)

Excel-Macro Problem

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

Excel-Macro Problem

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

Excel-Macro Problem

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

Excel-Macro Problem

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

Excel-Macro Problem

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

Excel-Macro Problem

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

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

Excel-Macro Problem

Tomorrow or next week!
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

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

Excel-Macro Problem

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