Macro to copy and paste values (columns)I have a macro file bu
Thanks Norman for your super fast solution. I'll give it a try.
"Norman Jones" wrote:
Hi CO2CO4,
In a standard module try something like:
'=========
Option Explicit
Public Sub tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rNG As Range
Dim arr As Variant
Dim CalcMode As Long
Const sAddress As String = "A:N, AA:AZ" '<<==== CHANGE
Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
arr = VBA.Array("Sheet1", _
"Sheet2", _
"Sheet7") '<<====
CHANGE
On Error GoTo XIT
With Application
.ScreenUpdating = False
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With
For Each SH In WB.Sheets(arr)
With SH
Set rNG = Application.Intersect _
(.Range(sAddress), .UsedRange)
End With
With rNG
.Value = .Value
End With
Next SH
XIT:
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
'<<=========
---
Regards.
Norman
"C02C04" wrote in message
...
I have a macro file built over time from seeking expert advice from this
forum and that explains my limit knowledge on VBA. The macro opens 10
excel
files and the last file (call this master file) fetches data from the
previous files. The macro then does some proven routine and closes all the
files. Please note that the macro reside in a separate excel file.
I need help on this. In the master file I need to copy and paste values
from
column A to column N and column AA to column AZ for multiple sheets (say
sheet1, sheet2€¦..sheetN). Can someone help?
|