Copy/Cut entire row to another sheet based on cell value

**excelkeechak** · 01-31-2010, 02:02 AM

Based on the example and solutions from one of our friends post
http://www.excelforum.com/excel-prog...ell-value.html.

i want to know the code with the same data as posted in THAT example,changing the data slightly like adding "TODAY" & "TOMMORROW" as other key words which will be Cut/Copied as seperate groups one beneath the other.

I had attached the worksheet with the actual data & the final Required format.
Thanks for the help.

**Alf** · 01-31-2010, 06:09 AM

Perhaps something like this?

Sub Macro1()
'
'

Range("Req_Format!A1:H" & Range("Req_Format!A" & Rows.Count).End(xlUp).Row).Clear

Range("ACTUALSHEET!D4").AutoFilter Field:=4, Criteria1:="Everyday"
Range("A1:H" & Range("A" & Rows.Count).End(xlUp).Row).Copy
Range("Req_Format!A1").PasteSpecial Paste:=xlPasteAll

Range("ACTUALSHEET!D4").AutoFilter Field:=4, Criteria1:="Today"
Range("A1:H" & Range("A" & Rows.Count).End(xlUp).Row).Copy
Range("Req_Format!A" & Rows.Count).End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteAll

Range("ACTUALSHEET!D4").AutoFilter Field:=4, Criteria1:="Tommorrow"
Range("A1:H" & Range("A" & Rows.Count).End(xlUp).Row).Copy
Range("Req_Format!A" & Rows.Count).End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteAll

Application.CutCopyMode = False

Selection.AutoFilter

End Sub

I had to rename "Req Format" to "Req_Format". Seems like Excel 2003 do not like blanks in tag names.

HTH

Alf

**Marcol** · 01-31-2010, 07:36 AM

Based on JBeaucaires earlier post -
http://www.excelforum.com/excel-prog...ell-value.html.

Cut entire row to another sheet based on cell value

Please Login or Register  to view this content.

No problem with sheet name "Req Format"

**excelkeechak** · 01-31-2010, 09:39 AM

Sorry Friends.I m not getting the desired result from your code

**Marcol** · 01-31-2010, 10:14 AM

Sorry forgot to reference your sheet name.

error in line

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

Also remember to use

Please Login or Register  to view this content.

**excelkeechak** · 01-31-2010, 11:52 AM

Thanks a lot Friend.That worked well.Thanks for Sparing your time.

**Marcol** · 01-31-2010, 12:13 PM

If you are happy with the solution.

Remember to change the prefix to [SOLVED]

If the solution helped and you wish to add to any of contributors reputations, click the scales icon in top right hand corner of the thread.

**excelkeechak** · 02-01-2010, 09:16 PM

what would be the generalised code for this problem. I mean if i need to change the words in the array

Please Login or Register  to view this content.

to any of my words or if my requirement is more than the three words here how would i do it.is it sufficient to change only the array words or do i need to change the code also. please tell me

**Marcol** · 02-01-2010, 10:06 PM

Just change to suit your needs.

Please Login or Register  to view this content.

for example

Please Login or Register  to view this content.

The code will handle as many, or as little, as you need. Just remember to keep strings in quotes and separate with commas.

If you want to use variables instead of strings the code will still be okay only difference being you will not need to use quotes
for example

Please Login or Register  to view this content.

See VBa help "Array Function"

**excelkeechak** · 02-02-2010, 11:10 AM

OK.Thanks a lot for the Reply.

**Marcol** · 10-12-2010, 02:56 AM

Hi tube

Welcome to the forum.

You should start a new thread, not ask a new question in a thread of another member, even if it seems related.

There is nothing wrong with the content of your question, referencing another thread as you have done is good. The only thing you haven't done is wrap your code in code tags.

Please read the forum rules then post your question accordingly.

Sorry to be a pain, but Rules are Rules.

Cheers

**tube** · 10-12-2010, 07:22 PM

Thanks Marcol,

will do!

**Giftie** · 11-02-2010, 04:21 PM

I am also trying to do something similar. I want to move rows that have entered stage "4" into the current projects worksheet. So when the cell in Column A shows the number 4 I want it to then take that data and copy the row to the Current projects.

**Giftie** · 11-02-2010, 04:23 PM

Ok, wow, just read what you said. I'll start a new thread - I thought that's what I did but it ended up here in the reply section.

Copy/Cut entire row to another sheet based on cell value

LinkBack

Thread Tools

Rate This Thread

Display

Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Re: Copy/Cut entire row to another sheet based on cell value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions