Editing multiple workbooks

B

Blackcat

I have approx 20 workbooks which have the same set-up in each.

I need to add three columns in the same place in all the workbooks. Is there
any way of amending all the workbooks at the same time?


Thanks in anticipation.
 
F

Frank Kabel

Hi
before applying your change first select all affected worksheets (hold down
the cTRL while selecting them to group them)

Note: don't forget to ungroup afterwards
 
R

Ron de Bruin

Frank's example is for sheets in the same workbook

With workbooks you can use a macro to loop through the workbooks
I don't know if you want this?

If you want a example post back
 
B

Blackcat

Ron,

That would be great.

Thanks



Ron de Bruin said:
Frank's example is for sheets in the same workbook

With workbooks you can use a macro to loop through the workbooks
I don't know if you want this?

If you want a example post back
Click to expand...
 
R

Ron de Bruin

Hi Blackcat

Try this for all files in the folder C:\Data (all files must be closed)
It will insert 3 columns in the first sheet of each workbook after column B

Copy the code in a normal module in a workbook that is
not in the folder C:\Data.

Open a new workbook
Alt-F11
Insert>Module from the menu bar
paste the sub in there
Alt-Q to go back to Excel

If you do Alt-F8 you get a list of your macro's
Select "TestFile1" and press Run

Sub TestFile1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
mybook.Sheets(1).Range("C1").Resize(, 3).EntireColumn.Insert
mybook.Close True
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
 
B

Blackcat

Thanks I'll give it a go.

DR


Ron de Bruin said:
Hi Blackcat

Try this for all files in the folder C:\Data (all files must be closed)
It will insert 3 columns in the first sheet of each workbook after column B

Copy the code in a normal module in a workbook that is
not in the folder C:\Data.

Open a new workbook
Alt-F11
Insert>Module from the menu bar
paste the sub in there
Alt-Q to go back to Excel

If you do Alt-F8 you get a list of your macro's
Select "TestFile1" and press Run

Sub TestFile1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
mybook.Sheets(1).Range("C1").Resize(, 3).EntireColumn.Insert
mybook.Close True
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
Click to expand...
 
Top