Macro Related to Paste Values

[Akash]

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

 

[joeu2004]

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

 

[GS]

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/DatabaseProgramming.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