Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copy contents of ALL rows containing flag 2

Status
Not open for further replies.
MeGustaXL

MeGustaXL

Technical User
Aug 6, 2003
1,055
GB
Hi Folks,

I'm pretty sure this is one of those old chestnuts that's so simple I can't see the wood for the....etc

I have a table like this:

Line Desc Ind Time
1 Item 1 0 10:43
2 Item 2 0 10:43
3 Item 3 1 10:43
4 Item 4 0 10:43

etc…

The "1" flag in column "Ind" is set randomly by a program.

How can I step thru the table, find all occurrences of 1 in IND, and copy the entire row to another location on the same spreadsheet, appending to the data that's already there?

As I say, I'm sure this has been explained to me before, but it's lost for now :-(




Chris

Varium et mutabile semper Excel

 
Sort by date Sort by votes
Why not playing with AutoFilter ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Hi PHV, and thanks for your snappy reply [wink]

Sorry, my bad, but I should have said that I want to do the copy/paste 'on the fly' as the program runs, viz;

For c = 1 to Whatever
Generate a random number for Ind
For n = 1 to NumberOfLines
If Ind on this row = 1 Then
Copy this Row across to next available line in data table
Next n
Next c

This looks as if it should work, but it doesn't - maybe I'm not copy/Pasting the data correctly. Grrrr!



Chris

Varium et mutabile semper Excel

 
Upvote 0 Downvote
I don't know any method that's particularly clever but the brute force approach seems straighforward:

Let's say your table is in column A through D, rows 2 through 'n' with the header in row 1.

Further, let's say you want the new table to start on row 'rw_n', column 'c_n'.
Code: 
lastrow=activesheet.usedrange.rows.count
for j=1 to 4
  cells(rw_n,c_n+j-1)=cells(1,j) 'copy header
  rw_n=rw_n+1
next
for i=2 to lastrow
  if cells(i,3)=1 then
     for j=1 to 4
       cells(rw_n,c_n+j-1)=cells(i,j) 
       rw_n=rw_n+1
     next
  end if
next

_________________
Bob Rashkin
 
Upvote 0 Downvote



c

Filter

Select all

Copy

Select other sheet

Edit/Paste Special - VALUES

Turn off your macro recorder.

VOLA!

Skip,

[glasses] [red][/red]
[tongue]
 
Upvote 0 Downvote
Hiya Skip!

Yep, it's coming back to me I think, but 2 things:

1. Select all?

2. Not other sheet, but same sheet, 5 columns to the right, appended to the data that's already there.

Bong:

That gives me a code 400 error [cry]

Chris

Varium et mutabile semper Excel

 
Upvote 0 Downvote
Sorry Skip,

I'm Soooooo Stoooopid, but I dunno how to do record that or code it - if I do Ctrl+A to Select All, I get the whooooole worksheet, which won't paste 'cos it's "not the same size..."

What am I missing?

BTW, this poor cat is beggin' me to divest it of another layer of skin! [rofl]

Chris

Varium et mutabile semper Excel

 
Upvote 0 Downvote
According to VBA help, Error Code 400 is:
Form already displayed; can't show modally (Error 400)


You can't use the Show method to display a visible form as modal. This error has the following cause and solution:

You tried to use Show, with the style argument set to 1 – vbModal, on an already visible form.
Use either the Unload statement or the Hide method on the form before trying to show it as a modal form.
Click to expand...
That doesn't make any sense.

_________________
Bob Rashkin
 
Upvote 0 Downvote
Skip:

I know that, but the table changes size each time I recalculate, 'cos there's a different number of 1's. So if I select 3 rows this time when I record, all's well.

The next time, there might be 5 rows with 1 in them, but the macro still selects only the 3 rows it's been told to.

How can I Select All of the table, dynamically expending or contracting the selection as necessary?

Bong:

Hmmm. What the....?

Chris

Varium et mutabile semper Excel

 
Upvote 0 Downvote
Sorry Skip, I can detect your patience thinning, just like catskin - here's what I got:

Code: 
Sub SkipAcross()
'
' SkipAcross Macro
' Macro recorded 14/09/2006 by MeGustaXL
'

'
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=3, Criteria1:="1"
    Range("A5:D14").Select
    Selection.Copy
    Range("J1").Select
    Range("J1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Selection.AutoFilter Field:=3
End Sub
At the mo' I'm generating random 1's in column C each time the sheet Calculates.



Chris

Varium et mutabile semper Excel

 
Upvote 0 Downvote


Code: 
Sub SkipAcross()
'
' SkipAcross Macro
' Macro recorded 14/09/2006 by MeGustaXL
'

' First make the AUTOFILTER selection

    Range("A1").CurrentRegion.Copy
    Range("J1").PasteSpecial _
        Paste:=xlValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
End Sub

Skip,

[glasses] [red][/red]
[tongue]
 
Upvote 0 Downvote
Skippy My Friend, You are a Gee-Nee-Us! [2thumbsup] + *

It was the
Code: 
Range("A1").CurrentRegion.Copy
part that was eluding me all along of course!

As I said at the start, this had been explained to me before, and in fact it was you who did the 'splainin!

Thanks again for your patience and help to keep the Boss off my back!

Chris

Varium et mutabile semper Excel

 
Upvote 0 Downvote
Darn It, I've got to bump this one up again I'm afraid [blush]

I've got a range from D5 to J28 which is named "EBS_table".

When I run the macro, it stalls at the Autofiltering part with a "400" error [flame] Where am I going wrong?

Code: 
[green]'....Generate random numbers and fill in another table....
'Then go to the AutoFilter bit:
[/green]
[EBS_table].AutoFilter Field:=5, Criteria1:="1"
[D6].CurrentRegion.Copy
    Range("Z65536").End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlValues, _
        Operation:=xlNone, _
        SkipBlanks:=True, _
        Transpose:=False
[EBS_table].AutoFilter

[green]Carry on and do the rest of the macro....[/green]

It appears to do the Copy, because I can manually select the next blank cell down in column Z and PasteSpecial, Values with (nearly) the desired result. I say nearly, because it copies the header row from D5:J5 - but I don't want that.

Chris

Varium et mutabile semper Excel

 
Upvote 0 Downvote


Well SURE it copies the HEADER, because of the .CurrentRegion (and why specify a cell when you have the range name?)
Code: 
With [EBS_table]
   .AutoFilter Field:=5, Criteria1:="1"
   .CurrentRegion.range(cells(2,1), cells(.rows.count, .columns.count)).Copy
    Range("Z65536").End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlValues, _
        Operation:=xlNone, _
        SkipBlanks:=True, _
        Transpose:=False
   .AutoFilter
End With


Skip,

[glasses] [red][/red]
[tongue]
 
Upvote 0 Downvote
Thanks again Skip, but it don't work for me [sad]

It still hangs with a 400 error, and the cursor after End With..

Manually selecting the next cell in Z and Pasting Values puts in some data, but some of them have zeroes in the filtered column!!! [bugeyed]



Chris

Varium et mutabile semper Excel

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Copy data between SQL and Oracle 1
Replies
8
Views
1K
remeng
remeng
remeng
  • Locked
  • Question
Delete dynamic table rows without removing password protection
Replies
7
Views
615
remeng
remeng
EliseFreedman
  • Locked
  • Question
booking tool - all day time slots
Replies
3
Views
729
Andrzejek
Andrzejek
remeng
  • Locked
  • Question
If obsolete - deactivate all but one macro
Replies
6
Views
558
mintjulep
M
MattGreer
  • Locked
  • Question
Excel - Copy Filtered Rows from a table to a new blank worksheet
Replies
7
Views
501
combo
combo

Part and Inventory Search

Sponsor

Back
Top