Editing multiple workbooks

[Guest]

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.

 

[Guest]

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

 

[Guest]

Frank,

Does this apply to workbooks? I'm struggling to get them to group.

DR

 

[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

 

[Guest]

Ron,

That would be great.

Thanks

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

 

[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

 

[Guest]

Thanks I'll give it a go.

DR

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