Paste Values

**STEVEB** · 08-23-2005, 12:36 PM

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.

**ronthedog** · 08-23-2005, 01:31 PM

Originally Posted by STEVEB

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

Tom Ogilvy · 08-23-2005, 02:05 PM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

**STEVEB** · 08-23-2005, 04:25 PM

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!!

Tom Ogilvy · 08-23-2005, 10:05 PM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

**STEVEB** · 08-24-2005, 11:00 AM

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!

Tom Ogilvy · 08-24-2005, 04:05 PM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>