Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Related to Paste Values
Hi,
Need a help!! I have below mentioned formula: Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+Shift+J ' Cells.Select Range("C12").Activate Sheets(Array("Start", "PO", "NMPF", "FinalApprovalSheet", "Payment Request", "GRN", _ "Sig_Masters", "Vendor_Master", "ITEM_MASTER", "Masters", "Address_Master", _ "Sig_Auth")).Select Sheets("Start").Activate Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("E16").Select Sheets("Start").Select Range("C14").Select End Sub As soon as I run this macro Paste Value options starts. The only issue is it runs on the single file. I have aprox 100 similar Files. I want a macro which ask me to select multiple files and then run a macro to save time. Pls help me to close this. Thanks in Advance Rgds Akash Maheshwari |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Related to Paste Values
"Akash" wrote:
I have below mentioned formula: Sub Macro1() [....] The only issue is it runs on the single file. I have aprox 100 similar Files. I want a macro which ask me to select multiple files and then run a macro to save time. Your macro seems to have a lot of extraneous operations, as is common for recorded macros. But assuming it makes sense to do, try the following. Sub doit() Dim i As Long ' see GetOpenFilename.FileFilter in help page fileNames = Application.GetOpenFilename(MultiSelect:=True, _ Title:="click to select one; then press ctrl+A or ctrl+Click") If TypeName(fileNames) = "Boolean" Then Exit Sub ' cancelled On Error Resume Next For i = 1 To UBound(fileNames, 1) ' alternatively, rely on GetOpenFilename.FileFilter If Right(fileNames(i), 4) = ".xls" Then Workbooks.Open Filename:=fileNames(i) If Err = 0 Then Macro1 Application.DisplayAlerts = False ActiveWorkbook.Close SaveChanges:=True Application.DisplayAlerts = True End If End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Related to Paste Values
You don't need to copy/paste anything to get this done. Simply assign
the range value to the range like so... Selction.Value = Selection.Value ...so formulas are gone and their results are now constant values. You also don't have to open any workbooks directly in Excel if you use ADODB to update the values. Simply load each sheet (table) into a recordset, edit the values, and then update the table in the file. For more info (and examples)... http://www.appspro.com/conference/Da...rogramming.zip -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
Macro paste values help | Excel Programming | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Summing Related Values | Excel Discussion (Misc queries) |