Excel - A macro to copy rows and columns

December 2016




Issue


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.CurrentRegion.Select   
Selection.Clear   
Range("A1").Select   
Windows("ReferenceList.xlsm").Activate   
Selection.CurrentRegion.Select   
Selection.Copy   
Windows("refList-output.xltx").Activate   
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   
Range("A1").Select   
Application.CutCopyMode = False   
ActiveWorkbook.Save   
Windows("ReferenceList.xlsm").Activate   
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.

Solution


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
ThisWorkbook.Worksheets("sheet1").UsedRange.SpecialCells(xlCellTypeVisible).Copy  
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.

Note


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.