Macro-Range of rows and columns to copy [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

I am working on a Macro to copy from one workbook(sheet) and paste on another workbook (sheet)
office 2007 and Windowxp

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

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

Thanks for help

Ahamd

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
I have not seen your macro. after filtering use specialcells(xlcellstypevisible)--something like this

ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeVisible).Copy
Range("a15").PasteSpecial
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

below is the Macro, but its auto generated code

Sub Macro1()
'
' Macro21 Macro
'

'
Workbooks.Open Filename:= _
"O:\data\order\refList-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 doing fine but have to make selection based on result records, so that can do other formating as well.

thanks
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
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""

thisworkbook means the workbook's module where the macro is parked

can you try to modify the macro .

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!