Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy paste values macro | Excel Discussion (Misc queries) | |||
Copy and Paste distinct columns macro | Excel Discussion (Misc queries) | |||
Macro for copy and paste values | Excel Discussion (Misc queries) | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
Copy & Paste values macro...almost there? | Excel Programming |