range variant looping macro many workbooks

cometorta

New Member
Joined
Nov 4, 2009
Messages
25
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

cometorta

New Member
Joined
Nov 4, 2009
Messages
25
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
save the following codes in a workbook and save the workbook in the folder with 20+ files
Code:
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

New Member
Joined
Nov 4, 2009
Messages
25

ADVERTISEMENT

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

New Member
Joined
Nov 4, 2009
Messages
25

ADVERTISEMENT

Can someone help me with this problem? please

Thanks,

Francisco
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
HI
try these modified codes
Code:
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

New Member
Joined
Nov 4, 2009
Messages
25
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
 

Forum statistics

Threads
1,143,655
Messages
5,720,112
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top