Macro to copy and paste values (columns)I have a macro file built
 

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?

Macro to copy and paste values (columns)I have a macro file built
 

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?

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?