Paste Values
 

Does anyone have any suggestions for a Macro that would open a workbook
and paste values for all sheets in the workbook?

My current Macro works great when the sheets in the workbook are
consistent. However, the worksheets names and number of sheets in the
workbook are updated weekly and frequently change from the prior week.


Any help would be greatly appreciated.


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=398263

Paste Values
 

STEVEB Wrote:
Does anyone have any suggestions for a Macro that would open a workbook
and paste values for all sheets in the workbook?

My current Macro works great when the sheets in the workbook are
consistent. However, the worksheets names and number of sheets in the
workbook are updated weekly and frequently change from the prior week.


Any help would be greatly appreciated.

Not quite sure what you mean by paste values for all sheets, but
generally speaking you need a loop that doesn't refer to worksheets by
name but loops through each worksheet object in the worksheets
collection.

The following loop will work

For each wks in [workbookname.xls].worksheets
wks.select
[paste routine]
next wks

where wks is a variable (undeclared here but you can get away with
that) that refers to each worksheet in turn. If you need to make any
exceptions then insert

IF wks.name<[criteria] then

after the first line and an

END IF

before the last


--
ronthedog
------------------------------------------------------------------------
ronthedog's Profile: http://www.excelforum.com/member.php...o&userid=26504
View this thread: http://www.excelforum.com/showthread...hreadid=398263

Paste Values
 

Dim bk as Workbook, sh as Worksheet
bk = Workbooks.Open("C:\data\Myfiles.xls")
for each sh in bk.worksheets
sh.UsedRange.Formula = sh.UsedRange.Value
Next
bk.close Savechanges:=True

--
Regards,
Tom Ogilvy


"STEVEB" wrote in
message ...

Does anyone have any suggestions for a Macro that would open a workbook
and paste values for all sheets in the workbook?

My current Macro works great when the sheets in the workbook are
consistent. However, the worksheets names and number of sheets in the
workbook are updated weekly and frequently change from the prior week.


Any help would be greatly appreciated.


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=398263

Paste Values
 

Thanks Tom!

Everything worked great, I appreciate your help!!

One more issue......

Do you have any suggestions on how I can incorporate this code int
another Macro I use? The code you suggested below worked great when
defined the workbook to open (bk=workbooks.Open(MyFile.xls).

Dim bk As Workbook, sh As Worksheet
bk = Workbooks.Open("C:\data\Myfiles.xls")
For Each sh In bk.Worksheets
sh.UsedRange.Formula = sh.UsedRange.Value
Next
bk.Close Savechanges:=True

However,

I use a macro to open files based on ranges. The Macro will open th
file in Cell A1, A2, A3... and update the links automatically. A
example of my code is as follows:


Dim sStr As String

Application.DisplayAlerts = False

sStr = Range("A1").Value
sStr2 = Range("A2").Value
sStr3 = Range("A3").Value
sStr4 = Range("A4").Value
sStr5 = Range("A5").Value
sStr6 = Range("A6").Value

Workbooks.Open sStr, UpdateLinks:=0
Workbooks.Open sStr2, UpdateLinks:=0
Workbooks.Open sStr3, UpdateLinks:=0
Workbooks.Open sStr4, UpdateLinks:=0
Workbooks.Open sStr5, UpdateLinks:=0
Workbooks.Open sStr6, UpdateLinks:=0

Application.DisplayAlerts = False


Is there a way to have these files paste values on all worksheets i
the workbook(i.e. the code you suggested above) by refering to the fil
range(i.e. sStr2, etc.) rather than naming the workbook in the code (b
= Workbooks.Open("C:\data\Myfiles.xls"))?

If you have any questions, please let me know.

Any help would be greatly appreciated!

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=39826

Paste Values
 

Dim sStr(1 to 6) As String

Application.DisplayAlerts = False

for i = 1 to 6
sStr(i) = Range("A1").offset(i-1,0).Value
Next
for i = 1 to 6
set bk = Workbooks.Open sStr(i), UpdateLinks:=0
For Each sh In bk.Worksheets
sh.UsedRange.Formula = sh.UsedRange.Value
Next

Next i

Application.DisplayAlerts = False

or

Application.DisplayAlerts = False

for i = 1 to 6
set bk = Workbooks.Open Range("A1").offset(i-1,0) _
.Value, UpdateLinks:=0
For Each sh In bk.Worksheets
sh.UsedRange.Formula = sh.UsedRange.Value
Next

Next i

Application.DisplayAlerts = False


--
Regards,
Tom Ogilvy

"STEVEB" wrote in
message ...

Thanks Tom!

Everything worked great, I appreciate your help!!

One more issue......

Do you have any suggestions on how I can incorporate this code into
another Macro I use? The code you suggested below worked great when I
defined the workbook to open (bk=workbooks.Open(MyFile.xls).

Dim bk As Workbook, sh As Worksheet
bk = Workbooks.Open("C:\data\Myfiles.xls")
For Each sh In bk.Worksheets
sh.UsedRange.Formula = sh.UsedRange.Value
Next
bk.Close Savechanges:=True

However,

I use a macro to open files based on ranges. The Macro will open the
file in Cell A1, A2, A3... and update the links automatically. An
example of my code is as follows:


Dim sStr As String

Application.DisplayAlerts = False

sStr = Range("A1").Value
sStr2 = Range("A2").Value
sStr3 = Range("A3").Value
sStr4 = Range("A4").Value
sStr5 = Range("A5").Value
sStr6 = Range("A6").Value

Workbooks.Open sStr, UpdateLinks:=0
Workbooks.Open sStr2, UpdateLinks:=0
Workbooks.Open sStr3, UpdateLinks:=0
Workbooks.Open sStr4, UpdateLinks:=0
Workbooks.Open sStr5, UpdateLinks:=0
Workbooks.Open sStr6, UpdateLinks:=0

Application.DisplayAlerts = False


Is there a way to have these files paste values on all worksheets in
the workbook(i.e. the code you suggested above) by refering to the file
range(i.e. sStr2, etc.) rather than naming the workbook in the code (bk
= Workbooks.Open("C:\data\Myfiles.xls"))?

If you have any questions, please let me know.

Any help would be greatly appreciated!!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=398263

Paste Values
 

Hi Tom,

Thanks for getting back to me, I appreciate it.

On both the examples I receive a the following error message:

Compile error - Syntax error

The error occurs at the following lines:

Example 1 - set bk = Workbooks.Open sStr(i), UpdateLinks:=0

Example 2 - set bk = Workbooks.Open Range("A1").offset(i-1,0) _
.Value, UpdateLinks:=0


Have I overlooked someting? Thanks for your help

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=39826

Paste Values
 

no, I was editing your code to present a concept and didn't enclose the
arguments in parenthesis:

Example 1 - set bk = Workbooks.Open( sStr(i), UpdateLinks:=0)

Example 2 - set bk = Workbooks.Open (Range("A1").offset(i-1,0) _
.Value, UpdateLinks:=0)

--
Regards,
Tom Ogilvy


"STEVEB" wrote in
message ...

Hi Tom,

Thanks for getting back to me, I appreciate it.

On both the examples I receive a the following error message:

Compile error - Syntax error

The error occurs at the following lines:

Example 1 - set bk = Workbooks.Open sStr(i), UpdateLinks:=0

Example 2 - set bk = Workbooks.Open Range("A1").offset(i-1,0) _
Value, UpdateLinks:=0


Have I overlooked someting? Thanks for your help!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=398263

Paste Values
 

Thanks Tom!

Sorry for the oversight! Everyhting worked great! I really appreciate
your help!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=398263