Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find values in multiple cells and paste row values | Excel Discussion (Misc queries) | |||
can you change the default paste method? (paste values) | Excel Discussion (Misc queries) | |||
Paste values only | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
How do i compare values from two sheet and copy & paste if values match? | Excel Programming |