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
' Macro21 Macro
Workbooks.Open Filename:= _
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
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 _
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.
This document, titled « Excel - A macro to copy rows and columns », is available under the Creative Commons
license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM