ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Urgent Help .... (https://www.excelbanter.com/excel-programming/416588-urgent-help.html)

kiran
Urgent Help ....
 
Hi All,
I have 2 excel workbook's moc.xls & product.xls.

In moc.xls I have Baking_powder, Coffee, Cold_Beverages, Cooking_prds as
diffrent sheets and in each sheet I have the columns as follows....

A B C
Fiscal year/period Products Total
MOC 11 2008 CSD
MT
NONCSD

MOC 12 2008 CSD
MT
NONCSD

MOC 01 2009 CSD
MT
NONCSD

MOC 02 2009 CSD
MT
NONCSD
and so on........

In product.xls I have the data as follows.....

A B C D E F

Products Fiscal year/period MOC 11 2008 MOC 12 2008 MOC 01 2009 MOC 02 2009
Baking_powder CSD 872.1430000 634.2780000 - -
MT 634.2780000 450.5190000 - 441.0790000
NON-CSD 27,257.6850000 - - 29,874.8730000
Coffee MT 57,291.6380000 50,218.9150000 44,918.4620000 54,963.0090000
NON-CSD 200,429.8260000 25,509.5120000 -18,464.5840000 36,327.2910000
Cold_Beverages CSD 39,845.2710000 19,276.5840000 9,733.2720000 9,404.9280000
MT 28,048.5780000 12,190.9350000 5,329.5180000 6,241.5160000
NON-CSD 13,864.3480000 49,923.3500000 95,780.9700000 90,578.8540000
Cooking_prds MT 6,543.5950000 5,685.3880000 5,674.5460000 6,023.5630000
NON-CSD 12,740.3760000 332.5570000 327.8850000 1,962.6270000

as follows.... my query is I want a macro to copy the data from product.xls
to moc.xls in column C, such a way that if Baking_powder sheet is activated I
want CSD,

MT & NONCSD of respective column to be coppied in moc.xls eg: if I select
Baking_powder sheet I want the Baking_powder CSD of that fiscal year/period
to be

coppied in moc.xls of fiscal yerar/period column.

Out put should look as follows:-
Baking_powder sheet activated
Fiscal year/period Products Total
MOC 11 2008 CSD 872.1430000
MT 634.2780000
NONCSD 27,257.6850000

MOC 12 2008 CSD 634.2780000
MT 450.5190000
NONCSD -

MOC 01 2009 CSD -
MT -
NONCSD -

MOC 02 2009 CSD -
MT 441.0790000
NONCSD 29,874.8730000

like wise I want in all the sheets for Coffee, Cold_Beverages & Cooking_prds.

TIA

joel
Urgent Help ....
 
I did part of the task. I took the Product.xls book and made seperate pages
for each product. could easily change this to make a new workbook. I
assumed the Sheet that you posted was call Products. Change if necessary.


Sub MakeProductPages()

Set ProdSummaryBK = Workbooks("Product.xls")
Set ProdSummary = ProdSummaryBK.Sheets("Products")

With ProdSummary
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
SummaryRowCount = 2
Do While SummaryRowCount <= LastRow
If .Range("A" & SummaryRowCount) < "" Then
'get Product Sheets and clear sheets
Product = .Range("A" & SummaryRowCount)
Found = False
For Each sht In ProdSummaryBK.Sheets
If sht.Name = Product Then
Found = True
Exit For
End If
Next sht
With ProdSummaryBK
If Found = False Then
Set Prodsht = .Sheets.Add(after:=.Sheets(.Sheets.Count))
Prodsht.Name = Product
Else
Set Prodsht = .Sheets(Product)
Prodsht.Cells.ClearContents
Prodsht.Range("A1") = "Fiscal year/period"
Prodsht.Range("B1") = "Products"
Prodsht.Range("C1") = "Total"
End If
End With
End If
'Get Last row of Product
ProdLastRow = SummaryRowCount
Do While .Range("A" & (ProdLastRow + 1)) = "" And _
ProdLastRow < LastRow

ProdLastRow = ProdLastRow + 1
Loop

ProductShtRowCount = 2
'Span Summary Sheet
For ColCount = 3 To LastCol
For SummaryProdRowCount = SummaryRowCount To ProdLastRow
ProdType = .Range("B" & SummaryProdRowCount)
Amount = .Cells(SummaryProdRowCount, ColCount)
Period = .Cells(1, ColCount)

With Prodsht
If SummaryProdRowCount = SummaryRowCount Then
.Range("A" & ProductShtRowCount) = Period
End If
.Range("B" & ProductShtRowCount) = ProdType
.Range("C" & ProductShtRowCount) = Amount
ProductShtRowCount = ProductShtRowCount + 1
End With
Next SummaryProdRowCount
ProductShtRowCount = ProductShtRowCount + 1
Next ColCount
Prodsht.Columns("A:C").AutoFit
SummaryRowCount = ProdLastRow + 1
Loop
End With
End Sub


"kiran" wrote:

Hi All,
I have 2 excel workbook's moc.xls & product.xls.

In moc.xls I have Baking_powder, Coffee, Cold_Beverages, Cooking_prds as
diffrent sheets and in each sheet I have the columns as follows....

A B C
Fiscal year/period Products Total
MOC 11 2008 CSD
MT
NONCSD

MOC 12 2008 CSD
MT
NONCSD

MOC 01 2009 CSD
MT
NONCSD

MOC 02 2009 CSD
MT
NONCSD
and so on........

In product.xls I have the data as follows.....

A B C D E F

Products Fiscal year/period MOC 11 2008 MOC 12 2008 MOC 01 2009 MOC 02 2009
Baking_powder CSD 872.1430000 634.2780000 - -
MT 634.2780000 450.5190000 - 441.0790000
NON-CSD 27,257.6850000 - - 29,874.8730000
Coffee MT 57,291.6380000 50,218.9150000 44,918.4620000 54,963.0090000
NON-CSD 200,429.8260000 25,509.5120000 -18,464.5840000 36,327.2910000
Cold_Beverages CSD 39,845.2710000 19,276.5840000 9,733.2720000 9,404.9280000
MT 28,048.5780000 12,190.9350000 5,329.5180000 6,241.5160000
NON-CSD 13,864.3480000 49,923.3500000 95,780.9700000 90,578.8540000
Cooking_prds MT 6,543.5950000 5,685.3880000 5,674.5460000 6,023.5630000
NON-CSD 12,740.3760000 332.5570000 327.8850000 1,962.6270000

as follows.... my query is I want a macro to copy the data from product.xls
to moc.xls in column C, such a way that if Baking_powder sheet is activated I
want CSD,

MT & NONCSD of respective column to be coppied in moc.xls eg: if I select
Baking_powder sheet I want the Baking_powder CSD of that fiscal year/period
to be

coppied in moc.xls of fiscal yerar/period column.

Out put should look as follows:-
Baking_powder sheet activated
Fiscal year/period Products Total
MOC 11 2008 CSD 872.1430000
MT 634.2780000
NONCSD 27,257.6850000

MOC 12 2008 CSD 634.2780000
MT 450.5190000
NONCSD -

MOC 01 2009 CSD -
MT -
NONCSD -

MOC 02 2009 CSD -
MT 441.0790000
NONCSD 29,874.8730000

like wise I want in all the sheets for Coffee, Cold_Beverages & Cooking_prds.

TIA

kiran
Urgent Help ....
 
Great thanks a lot Joel

"Joel" wrote:

I did part of the task. I took the Product.xls book and made seperate pages
for each product. could easily change this to make a new workbook. I
assumed the Sheet that you posted was call Products. Change if necessary.


Sub MakeProductPages()

Set ProdSummaryBK = Workbooks("Product.xls")
Set ProdSummary = ProdSummaryBK.Sheets("Products")

With ProdSummary
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
SummaryRowCount = 2
Do While SummaryRowCount <= LastRow
If .Range("A" & SummaryRowCount) < "" Then
'get Product Sheets and clear sheets
Product = .Range("A" & SummaryRowCount)
Found = False
For Each sht In ProdSummaryBK.Sheets
If sht.Name = Product Then
Found = True
Exit For
End If
Next sht
With ProdSummaryBK
If Found = False Then
Set Prodsht = .Sheets.Add(after:=.Sheets(.Sheets.Count))
Prodsht.Name = Product
Else
Set Prodsht = .Sheets(Product)
Prodsht.Cells.ClearContents
Prodsht.Range("A1") = "Fiscal year/period"
Prodsht.Range("B1") = "Products"
Prodsht.Range("C1") = "Total"
End If
End With
End If
'Get Last row of Product
ProdLastRow = SummaryRowCount
Do While .Range("A" & (ProdLastRow + 1)) = "" And _
ProdLastRow < LastRow

ProdLastRow = ProdLastRow + 1
Loop

ProductShtRowCount = 2
'Span Summary Sheet
For ColCount = 3 To LastCol
For SummaryProdRowCount = SummaryRowCount To ProdLastRow
ProdType = .Range("B" & SummaryProdRowCount)
Amount = .Cells(SummaryProdRowCount, ColCount)
Period = .Cells(1, ColCount)

With Prodsht
If SummaryProdRowCount = SummaryRowCount Then
.Range("A" & ProductShtRowCount) = Period
End If
.Range("B" & ProductShtRowCount) = ProdType
.Range("C" & ProductShtRowCount) = Amount
ProductShtRowCount = ProductShtRowCount + 1
End With
Next SummaryProdRowCount
ProductShtRowCount = ProductShtRowCount + 1
Next ColCount
Prodsht.Columns("A:C").AutoFit
SummaryRowCount = ProdLastRow + 1
Loop
End With
End Sub


"kiran" wrote:

Hi All,
I have 2 excel workbook's moc.xls & product.xls.

In moc.xls I have Baking_powder, Coffee, Cold_Beverages, Cooking_prds as
diffrent sheets and in each sheet I have the columns as follows....

A B C
Fiscal year/period Products Total
MOC 11 2008 CSD
MT
NONCSD

MOC 12 2008 CSD
MT
NONCSD

MOC 01 2009 CSD
MT
NONCSD

MOC 02 2009 CSD
MT
NONCSD
and so on........

In product.xls I have the data as follows.....

A B C D E F

Products Fiscal year/period MOC 11 2008 MOC 12 2008 MOC 01 2009 MOC 02 2009
Baking_powder CSD 872.1430000 634.2780000 - -
MT 634.2780000 450.5190000 - 441.0790000
NON-CSD 27,257.6850000 - - 29,874.8730000
Coffee MT 57,291.6380000 50,218.9150000 44,918.4620000 54,963.0090000
NON-CSD 200,429.8260000 25,509.5120000 -18,464.5840000 36,327.2910000
Cold_Beverages CSD 39,845.2710000 19,276.5840000 9,733.2720000 9,404.9280000
MT 28,048.5780000 12,190.9350000 5,329.5180000 6,241.5160000
NON-CSD 13,864.3480000 49,923.3500000 95,780.9700000 90,578.8540000
Cooking_prds MT 6,543.5950000 5,685.3880000 5,674.5460000 6,023.5630000
NON-CSD 12,740.3760000 332.5570000 327.8850000 1,962.6270000

as follows.... my query is I want a macro to copy the data from product.xls
to moc.xls in column C, such a way that if Baking_powder sheet is activated I
want CSD,

MT & NONCSD of respective column to be coppied in moc.xls eg: if I select
Baking_powder sheet I want the Baking_powder CSD of that fiscal year/period
to be

coppied in moc.xls of fiscal yerar/period column.

Out put should look as follows:-
Baking_powder sheet activated
Fiscal year/period Products Total
MOC 11 2008 CSD 872.1430000
MT 634.2780000
NONCSD 27,257.6850000

MOC 12 2008 CSD 634.2780000
MT 450.5190000
NONCSD -

MOC 01 2009 CSD -
MT -
NONCSD -

MOC 02 2009 CSD -
MT 441.0790000
NONCSD 29,874.8730000

like wise I want in all the sheets for Coffee, Cold_Beverages & Cooking_prds.

TIA


All times are GMT +1. The time now is 09:09 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com