extracting data of ROW by conditional check of one column

[Ravi kumara]

I am using excel 2007 and 2013 in windows xp and 7, From the below sample data in sheet1 of workbook1- If "C1" is present in column G1 - G10 , then all the data correspanding to that ROW containg "C1" should be written in other sheet of new workbook. Is it possible if so solve my problem.
	Data in sheet1 of workbook1
	A	B	C	D	E	F	G
	332	07/11/2014	CDPO	24/10/2014	950	Office shifting	C1
	333	07/11/2014	DC	31/10/2014	-	Jeep KA-35 G-45	KHR
	334	07/11/2014	AEE, QC	17/10/2014	-	work regarding	ASO
	335	07/11/2014	Prinicpal, ITI	29/10/2014	-	Training programme	C1
	336	07/11/2014	AC	29/10/2014	-	tri-month report	C1
	337	07/11/2014	DC	23/10/2014	887	Fees payment	ASO
	338	07/11/2014	EE,PWD	31/10/2014	1083	building safty	ASO
	339	07/11/2014	EE,PWD	31/10/2014	1072	construction of building	KHR
	340	07/11/2014	EE,PWD	31/10/2014	1082	work regarding	KHR
	341	07/11/2014	EE,PWD	31/10/2014	1077	work regarding	KHR
	Result in sheet1 of workbook2
	332	07/11/2014	CDPO	24/10/2014	950	Office shifting
	335	07/11/2014	Prinicpal, ITI	29/10/2014	-	Training programme
	336	07/11/2014	AC	29/10/2014	-	tri-month report

<tbody>
</tbody>

 

[Peter_SSs]

Ravi

Thanks for answering those questions. Post #10 seems very clear.

However, my suggested code would be very similar to the code I posted before, so we need to resolve my other question:

The first thing the code does is make a copy of the active worksheet, creating a new workbook with just that worksheet in it. Are you saying that not even that happens?

If you run the code when one of those April, May, June etc sheets as the active sheet, don't you get a copy of that worksheet (and no other sheets) in a new workbook called "Book1" or similar?

 

[Ravi kumara]

i am getting nothing in new workbook. after running that code the new book will remains blank without giving any result

 

[Peter_SSs]

i am getting nothing in new workbook. after running that code the new book will remains blank without giving any result

Hmm, if the active sheet when the code is run has data, I fail to see how it can copy that sheet and have no data, unless that active sheet contained no values in column G containing just the text "C1".

1. Are you sure that a monthly sheet with data was active when the code was run?

2. Can you confirm that active sheet had at least one cell in column G with "C1" (and nothing else) in it?

3. How are you actually triggering the code?

 

[Ravi kumara]

replay to #13

1. yes, it is active

2. yes, it contains "C1"

3. in workbook2 by selecting cell A1, I pasted your code in macros programming section and then I closed that macros section.

 

[Peter_SSs]

3. in workbook2 by selecting cell A1, I pasted your code in macros programming section and then I closed that macros section.

In post #10, your answer to Q3 was that the code was to create the second workbook. The quote above seems to imply that you have separately created a second workbook and are putting the code in that workbook then running the code. Is that right? If so, wouldn't that mean the blank worksheet in that new workbook was the active sheet when the code was run, not a monthly sheet from 'Inword_2014-15'?


Let's try to set out the steps in detail.

1. Open the workbook 'Inword_2014-15'
2. Close any other open workbooks.
3. Press Alt+F11 to open the VB window.
4. In the vb window, use the menu to Insert|Module
5. Copy my code from post #4 and Paste it into the main right hand pane that opened at step 4.
6. If there are other Modules in the workbook containing my code from your earlier attempts, delete those Modules or at least the code from them.
7. Close the VB window
8. In 'Inword_2014-15' click on the 'April' tab (assuming it has data)
9. Press Alt+F8 to open the Macro dialog, select 'CopyData', click Run

 

[Ravi kumara]

sir,

I am now out of station , actually now i am in tour. i will try u r steps after 3 days and tell u the result.

thanking u.

 

[Ravi kumara]

I tried ur steps and I got the result what I wanted.
Thank you sir, for ur suggestions . but there is a little bit problems I am facing in that.

Problem I am facing	Actually what I needed
1. The sheet what I have data is protected. So when I triggered the code it displayed “ run time error 1004 you cannot use this command on a protected sheet. To use this commond unprotect the sheet”	But at this time when I run the code , complete sheet is copied to new book without filter. Then I removed the protection and run the code , after this I got result. Is it possible to run the code for protected sheet? If yes tell me what to do. If not, It’s OK let it be as it is.
2. Each result sheet is coming in each new book. ( with existing sheet name-as I wanted)	But I need all the result sheet in a single new book( with existing sheet name). As I told in post no.10
3. In result Column A is displaying “#Ref” The sample is as below 332 07/11/2014 CDPO 24/10/2014 950 Office shifting #REF! 07/11/2014 Prinicpal, ITI 29/10/2014 - Training programme #REF! 07/11/2014 AC 29/10/2014 - tri-month report <tbody> </tbody>	I had given formula to column A . because this is sl.no. so I given =A1+1, below that cell =A2+1, so on…….. I need to display the values of that cells.

<tbody>
</tbody>

 

[Peter_SSs]

I tried ur steps and I got the result what I wanted.

Great! That would indicate the problem all along was just that the relevant sheet wasn't the active sheet when the code was triggered.

For the new issues ..

For 1 and 3 try adding the coloured code lines where shown to the previous code, inserting the correct password of course. If there is no password, then you can remove the red text.

  ActiveSheet.Copy
  With ActiveSheet
    .Unprotect Password:="abc"
    .UsedRange.Value = .UsedRange.Value
    .Rows(1).Insert

For 2, more clarification is needed.

a) Workbook 'Inword_2014-15' contains the monthly sheets

(i) Does it contain other sheets as well as the monthly sheets?
(ii) Do ALL the monthly sheets need to be moved to the new workbook 'Inword_2014-15_emp wise'? If not, how do we work out which ones should be moved and which ones shouldn't be moved?​

b) Do all the monthly sheets have the same password to unprotect them?

c) Does 'Inword_2014-15_emp wise' get created/saved in the same folder as 'Inword_2014-15'? If not, where?

 

[Ravi kumara]

ur new line insertion is working and gives good result. once again thanks for this suggestion.

the clarification as u asked is as below.

What you need	Clarification I am giving
a) Workbook 'Inword_2014-15' contains the monthly sheets (i) Does it contain other sheets as well as the monthly sheets? ----------------------------------- (ii) Do ALL the monthly sheets need to be moved to the new workbook 'Inword_2014-15_emp wise'? If not, how do we work out which ones should be moved and which ones shouldn't be moved? -----------------------------------	(a)(i) No , it doesn’t contains other sheets. It contains only monthly sheets that to after completion of each month ------------------------------------------------------------ (a)(ii) yes , all the monthly sheets need to be moved to the new workbook 'Inword_2014-15_emp wise' but, it should be after completion of each monthly sheets (i.e. after the end of nov-14, that sheet is created and data’s are entered in it . this sheet has to be needed to move. While at this movement dec-14 sheet is not yet created. Then after the end of Dec-14, that sheet is created and data’s are entered in it . this sheet has to be needed to move. While at this movement jan-15 sheet is not yet created. Like this after the end of each month each sheet will be created ) -------------------------------------------------------------------
b) Do all the monthly sheets have the same password to unprotect them? -----------------------------------	(b) Yes ----------------------------------------------------------
c) Does 'Inword_2014-15_emp wise' get created/saved in the same folder as 'Inword_2014-15'? If not, where? -----------------------------------	( c) I am not able to understand u r question, “ created/saved” means it’s by u r code? or by operator? - *u r code is creates new workbook by name “Book1” . while at the time of closing that ”Book1” we have to select the destination for the place to save. - * operator will be created the new file 'Inword_2014- 15_emp wise' with blank sheets,( without sheet name) in same folder as 'Inword_2014-15' - ----------------------------------------------

<tbody>
</tbody>

 

[Peter_SSs]

Next try.

This code looks for a file in the same folder as the one containing the code (Inword_2014-15) but with "_emp wisw" appended to it.
If found, it opens that file and appends the active sheet from Inword_2014-15.
If the "_emp wise" file is not found (presumably when the code is run for the first month) then the code creates such a file.

Sub CopyData()
  Dim Nme As String
  Dim WBemp As Workbook
  Dim WSact As Worksheet
  Dim bFirst As Boolean
  
  Application.ScreenUpdating = False
  Set WSact = ActiveSheet
  With ThisWorkbook
    Nme = .FullName
    Nme = Left(Nme, InStrRev(Nme, ".") - 1) & "_emp wise"
  End With
  On Error Resume Next
  Set WBemp = Workbooks.Open(Nme)
  On Error GoTo 0
  If WBemp Is Nothing Then
    Set WBemp = Workbooks.Add
    WBemp.SaveAs Nme
    bFirst = True
  End If
  WSact.Copy After:=WBemp.Sheets(WBemp.Sheets.Count)
  Set WSact = ActiveSheet
  If bFirst Then
    Application.DisplayAlerts = False
    WBemp.Sheets(1).Delete
    Application.DisplayAlerts = True
  End If
  With WSact
    .Unprotect Password:="abc"
    .UsedRange.Value = .UsedRange.Value
    .Rows(1).Insert
    .Range("G1").Value = 1
    .Columns("G").AutoFilter Field:=1, Criteria1:="<>C1"
    .UsedRange.EntireRow.Delete
    .Columns("G").ClearContents
    .Parent.Save
  End With
  Application.ScreenUpdating = True
End Sub