Excel

  • Thread starter Thread starter Annette
  • Start date Start date
A

Annette

I have a database with 98 sheets. One sheet for each bus in our fleet. I
need to updated the links for each sheet from xls to xlsm without changing
the bus number in the link. I used to be able to create a macro to make a
change in a few sheets in the old Excel and run it a couple of times until I
had changed each sheet. In the new Excel , I try to do that & it also
changes the bus number in the formula. Why and how can I stop it from doing
that? Example: the old link is ='F:\HOME\Passarea\MILEAGE\Mileage
08.09\[NOV08.xls]3'!$B$35. I need to change it to
='F:\HOME\Passarea\MILEAGE\Mileage 08.09\[NOV08.xlsm]3'!$B$35n 98 times. The
above example is for bus 3. In the past, I could change just one item in the
link and it would only change the one thing. Now it changes the number after
the ] (the bus number)on it's own. How can I stop it from doing that?
 
Just to be save I used two steps: (1) copy & paste and (2) column delete
This code works on one sheet; you can modify if for your 98 sheets knowing
what names they have

Sub tryme()
Worksheets("Sheet1").Select
For Each mycell In Range("A1:Z1")
If mycell.Value = 30 Then
MsgBox mycell.Value
tabcount = tabcount + 1
mycell.Resize(mycell.End(xlDown).Row).Copy
Worksheets("Tab_30").Cells(1, tabcount)
End If
Next
For Each mycell In Range("A1:Z1")
If mycell.Value = 30 Then
mycell.EntireColumn.Delete
End If
Next
End Sub
 
Read 'save' as 'safe' in first line of my reply!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Bernard Liengme said:
Just to be save I used two steps: (1) copy & paste and (2) column delete
This code works on one sheet; you can modify if for your 98 sheets knowing
what names they have

Sub tryme()
Worksheets("Sheet1").Select
For Each mycell In Range("A1:Z1")
If mycell.Value = 30 Then
MsgBox mycell.Value
tabcount = tabcount + 1
mycell.Resize(mycell.End(xlDown).Row).Copy
Worksheets("Tab_30").Cells(1, tabcount)
End If
Next
For Each mycell In Range("A1:Z1")
If mycell.Value = 30 Then
mycell.EntireColumn.Delete
End If
Next
End Sub


--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Annette said:
I have a database with 98 sheets. One sheet for each bus in our fleet. I
need to updated the links for each sheet from xls to xlsm without
changing
the bus number in the link. I used to be able to create a macro to make
a
change in a few sheets in the old Excel and run it a couple of times
until I
had changed each sheet. In the new Excel , I try to do that & it also
changes the bus number in the formula. Why and how can I stop it from
doing
that? Example: the old link is ='F:\HOME\Passarea\MILEAGE\Mileage
08.09\[NOV08.xls]3'!$B$35. I need to change it to
='F:\HOME\Passarea\MILEAGE\Mileage 08.09\[NOV08.xlsm]3'!$B$35n 98 times.
The
above example is for bus 3. In the past, I could change just one item in
the
link and it would only change the one thing. Now it changes the number
after
the ] (the bus number)on it's own. How can I stop it from doing that?
Click to expand...
Click to expand...
 
Thank you. I did get another fix from ShaneD....that works well too however
I will print this out for future reference. I think I can use this same
feature in another project.
--
Annette


Bernard Liengme said:
Just to be save I used two steps: (1) copy & paste and (2) column delete
This code works on one sheet; you can modify if for your 98 sheets knowing
what names they have

Sub tryme()
Worksheets("Sheet1").Select
For Each mycell In Range("A1:Z1")
If mycell.Value = 30 Then
MsgBox mycell.Value
tabcount = tabcount + 1
mycell.Resize(mycell.End(xlDown).Row).Copy
Worksheets("Tab_30").Cells(1, tabcount)
End If
Next
For Each mycell In Range("A1:Z1")
If mycell.Value = 30 Then
mycell.EntireColumn.Delete
End If
Next
End Sub


--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Annette said:
I have a database with 98 sheets. One sheet for each bus in our fleet. I
need to updated the links for each sheet from xls to xlsm without changing
the bus number in the link. I used to be able to create a macro to make a
change in a few sheets in the old Excel and run it a couple of times until
I
had changed each sheet. In the new Excel , I try to do that & it also
changes the bus number in the formula. Why and how can I stop it from
doing
that? Example: the old link is ='F:\HOME\Passarea\MILEAGE\Mileage
08.09\[NOV08.xls]3'!$B$35. I need to change it to
='F:\HOME\Passarea\MILEAGE\Mileage 08.09\[NOV08.xlsm]3'!$B$35n 98 times.
The
above example is for bus 3. In the past, I could change just one item in
the
link and it would only change the one thing. Now it changes the number
after
the ] (the bus number)on it's own. How can I stop it from doing that?
Click to expand...
Click to expand...
 
Back
Top