Selectively Transfer Data toPre defined Range
Solved/Closed
Yausi
-
Feb 26, 2010 at 04:44 AM
venkat1926
venkat1926
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Related:
- Selectively Transfer Data toPre defined Range
- Excel - Selectively Transfer Data to Pre-defined Range - How-To - Excel
- Transfer data from one excel worksheet to another automatically - Guide
- How to transfer data from blackberry to android - Guide
- How to transfer data from one excel workbook to another automatically using macro - Guide
- Automatically transfer data from one sheet to another ✓ - Forum - Excel
3 replies
venkat1926
Feb 26, 2010 at 06:25 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Feb 26, 2010 at 06:25 AM
the data in sheet 1 must have column headings in row 1. the sample sheet will be like this
hdng1 hdng2 hdng3 hdng4 hdng5
si d o s xxxxxx
si d o 2 ddddd
pg d r x dddd
pg m r a sdfsdf
fj m w 3 ghghgh
fj d w f dssdg
si m x 1 sfsdfsd
si y x 1 hmhjkk
run this macro on this and see sheet 2
(always keep original file safely somewhere
the macro is
hdng1 hdng2 hdng3 hdng4 hdng5
si d o s xxxxxx
si d o 2 ddddd
pg d r x dddd
pg m r a sdfsdf
fj m w 3 ghghgh
fj d w f dssdg
si m x 1 sfsdfsd
si y x 1 hmhjkk
run this macro on this and see sheet 2
(always keep original file safely somewhere
the macro is
Sub test() Worksheets("sheet2").Cells.Clear Worksheets("sheet1").Activate ActiveSheet.UsedRange.Select Selection.AutoFilter Field:=1, Criteria1:="si" Selection.AutoFilter Field:=2, Criteria1:="=d", Operator:=xlOr, _ Criteria2:="=m" ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Copy With Worksheets("Sheet2") .Range("A1").PasteSpecial .Range("B:B,D:D").Delete shift:=xlToLeft Application.CutCopyMode = False End With Selection.AutoFilter Range("A1").Select End Sub
yausi
Feb 26, 2010 at 07:14 AM
- Posts
- 1
- Registration date
- Friday February 26, 2010
- Status
- Member
- Last seen
- February 26, 2010
Feb 26, 2010 at 07:14 AM
Thanks Venkat1926. Much apreciated. At least you have made me progress a litlle.
I have copied the macro and ran it on worksheet1. That is, I called the macro while in active sheet, Worksheet1. I have however encountered this error message "Run-time error '1004': PasteSpecial of Range class failed" and when I chose "Debug" from the VB dialogue box the line ".Range("A1").PasteSpecial" was highlighted in yellow.
Any suggestions how I can overcome this error?
I have copied the macro and ran it on worksheet1. That is, I called the macro while in active sheet, Worksheet1. I have however encountered this error message "Run-time error '1004': PasteSpecial of Range class failed" and when I chose "Debug" from the VB dialogue box the line ".Range("A1").PasteSpecial" was highlighted in yellow.
Any suggestions how I can overcome this error?
venkat1926
Feb 26, 2010 at 08:00 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Feb 26, 2010 at 08:00 PM
I have once again checked. the macro completely works for me. I know in yourworkbook sheet2 is available otherwise it would have stopped in the line "with worksheets("sheet2")" and the error message would have been "subscription out of range". What is your version of excel. mine excel 2002(windows xp).
I suggest you delete the macro and once again COPY PASTE (not type) the macro in the vb editor and again run the macro give me feedback
I suggest you delete the macro and once again COPY PASTE (not type) the macro in the vb editor and again run the macro give me feedback