range variant looping macro many workbooks

[cometorta]

Hi,

I have a complicated problem for excel 2003 , maybe not for a power user.

I have about 20 different workbooks and i want to create identical tables and graphs in each one of them using the least number of steps.

The number of the rows changes by workbook but the colums are identical. So the range will vary by workbook.

so for example:

workbook1=

name sales
a 10
b 20
c 30


workbook2=
name sales
a 10

So I want a macro that does the tabling and charting in workbook1 and automatically moves to workbook2 and so on.


Thanks in advance for the informantion.

Francisco

 

[ravishankar]

HI
What type of chart you wish to draw?
Ravi

 

[cometorta]

Hi Ravi,

I will like to draw a simple bar chart. So the number of bars may vary across workbooks.

Thanks for the help,

Francisco

 

[ravishankar]

Hi
save the following codes in a workbook and save the workbook in the folder with 20+ files

Sub Macro1()
Dim z  As Long, e As Long
Dim f As String
d = 2
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
    Do While Len(f) > 0
    ActiveCell.Formula = f
    ActiveCell.Offset(1, 0).Select
    f = Dir()
    Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
    For e = 2 To 3
        If Cells(e, 1) <> ActiveWorkbook.Name Then
        Workbooks.Open Filename:=Cells(1, 2) & Cells(e, 1)
        x = Cells(Rows.Count, 1).End(xlUp).Row
        
        
    Range("A1:B4").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("A1:B4"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Sales"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Item"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
    End With
ActiveWorkbook.Close False
        End If
    Next e
    Application.ScreenUpdating = True
MsgBox "collating is complete."
End Sub

It assumes that col A and B has data(variable rows). On running the macro, it lists all files in col A and opens each one of them, creates a bar graph, saves it , closes it and moves on to next. so I suggest you test on 2-3 workbooks in a separate folder before using it. Change axis titles from item and sales to whatever you choose
Ravi

 

[cometorta]

Hi Ravi,

Thank you very much for all your help so far. However I am having problems getting results. I created 3 new workbooks in the same directory. Workbook A contains the macro and B and C workbooks contain 2 columns A and B with data to graph in sheet 1 (the workbooks have sheets 1,2,3). When running your macro I everything runs smoothly and I get the final result in workbook A saying "collating is complete", but when i check for the charts in workbooks B and C these are not there. I tried a couple of things such as deleting sheet 2 and 3 in workbooks b and c and then i get graphs for only workbook B with an error message.

could you please help me with this.

Thanks in advance,

Sincerely,

Francisco

 

[cometorta]

a

 

[cometorta]

Can someone help me with this problem? please

Thanks,

Francisco

 

[ravishankar]

HI
try these modified codes

Sub final()
Dim z  As Long, e As Long, x As Long
Dim f As String, m As String
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
    Do While Len(f) > 0
    ActiveCell.Formula = f
    ActiveCell.Offset(1, 0).Select
    f = Dir()
    Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
    For e = 2 To z
        If Cells(e, 1) <> ActiveWorkbook.Name Then
        n = Cells(e, 1)
        Workbooks.Open Filename:=Cells(1, 2) & Cells(e, 1)
        x = Cells(Rows.Count, 1).End(xlUp).Row
        m = ActiveSheet.Name
   Sheets(m).Range("A2:B" & x).Select
   Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets(m).Range("A2:B" & x), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:=m
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Bar chart"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "sales"
    End With
ActiveWorkbook.Close True
        End If
    Next e
    Application.ScreenUpdating = True
MsgBox "collating is complete."
End Sub

Ravi

 

[cometorta]

Ravi,

You are genious! Thank you very much!

Francisco

 

[cometorta]

range variant looping macro many workbooks in all sheets

Hi Ravi,

Now I'm trying to expand your code to do the same bar graph in each sheet (sheets 1 thru 3) in each workbook within the directory.

Could you please help me with this problem?

Sincerely,

Francisco