copying all none blank cells to clipboard

[daveonion]

Hi, the problem is , i have a spreadsheet which will vary in size depending on what is exported.
I need the code to select cells which are tagged.
so for instance there is a field called tagged and a letter is placed next to the corresponding record which needs to then be selected and copied into another sheet.
So all tagged records need to be copied into a new sheet, along with the titles.

thanks in advance

 

[SkipVought]

Hi,

1. loop thru the target range
2. when you encounter a tagged record...

YourRecordRange.Copy _
    Destination:=wsDestinationSheet.rgDestinationRange

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[daveonion]

hi skip thanks for your reply,

could you possibly give me more detail on the coding involved thanks

Dave

 

[xlbo]

Would it not be easier to use autofilter ???
summat like this:

With Sheets("AllData&quot'change to name of base data sheet
.Columns("A:N&quot.AutoFilter Field:=10, Criteria1="TagLetter"
.Cells.SpecialCells(xlCellTypeVisible).Copy Destination:=sheets("Tagged Cells Only&quot.Range("A1&quot
end with
Application.CutCopyMode = False

Within the autofilter, change 10 to whatever column the tag occurs in and change "Tagletter" to whatever the tag is
Also, you will need to change the sheet names to whatever the relevent sheet names are in your workbook

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[daveonion]

hi xlbo or anyone else, i'm not too sure what should be going in Field, its column 'h' that contains the tags, so how does the code reference it.
Currently the code just deletes the whole of the existing file and copies only the titles into the blank sheet, if i change the criteria to only omit blanks then it copies all.
The code i'm using is

With Sheets("Availability Report&quot
.Columns("A:N&quot.AutoFilter Field:=7, Criteria1:="x"
.Cells.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Tagged Cells Only&quot.Range("A1&quot
End With
Application.CutCopyMode = False

Thanks

 

[xlbo]

.Columns("A:N&quot.AutoFilter Field:=7, Criteria1:="x"
should be
.Columns("A:N&quot.AutoFilter Field:=8, Criteria1:="x"
being as column H is the EIGHTH column

and you can use activesheet.autofiltermode = false
to remove the autofilter


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde