Excel - A macro to copy rows and columns

October 2016


I am working on a Macro to copy from one workbook(sheet) and paste on another workbook (sheet)
Office 2007 and Windows XP

In the first workbook, I have all the data which according to reqs. is filtered. How to tell in macro to copy the 'result' records? These result records are always changing.

I am recording macro with these steps:
  • 1. open output.xlsx (target workbook)
  • 2. ctrl+A (to select all data already there) and clear (all)
  • 3. source workbook (input.xlsm) copy result records
  • 4. paste special in target workbook
  • 5. Souce workbook and stop recording macro

The rest of the macro is working fine, problem is with how to set the range of result records which you can say is dynamic.

Below is the Macro, but its auto generated code :

Sub Macro1()   
' Macro21 Macro   

Workbooks.Open Filename:= _   
"O:dataorderrefList-output.xltx" _   
, Editable:=True   
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _   
SkipBlanks:=False, Transpose:=False   
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _   
SkipBlanks:=False, Transpose:=False   
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _   
:=False, Transpose:=False   
Application.CutCopyMode = False   
Range("Table_Query_from_MS_Access_Database[#Headers,[Order No]]").Select   
End Sub 

It is working fine but I have to make selection based on result records and other formatting as well.


Have tried my suggestion. when you MERELY select a filtered data selection includes the hidden cells also (hidden due to filtering ) instead you must use "specialcells" property as suggested my me. can you modify on that basis.

There is also a provision WITHOUT activating the different windows or selecting diferent ranges.

I have giving alternative for pastespecial portion of your macro
Workbooks("book4").Worksheets("sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteColumnWidths, Paste:=xlPasteFormats _  
   , Paste:=xlPasteColumnWidths

If the two books are saved books you should use "book4.xls" or "book4.xlsm"" this workbook means the workbook's module where the macro is parked can you try to modify the macro.


Thanks to venkat1926 for this tip on the forum.

Related :

This document entitled « Excel - A macro to copy rows and columns » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.