Home » Blogs » tigershark45_can's blog

IF VALUE EQUALS X THEN COPY PASTE ROW TO NEW SHEET

Submitted by tigershark45_can on 3 May, 2011 - 15:30

Help!.. I would like to know how to copy paste from one sheet to another within the same file using the IF function.
Basically, looking a thousands of columns, if I want to copy specific rows (A15:K15), (B15:K15) etc.. of "sheet 1" to "sheet 2" only if (employee number) D15 = 11 (of sheet 1).

I would like the rows to follow in "sheet 2" and not appear at row 1500 if there is only 20 rows that were copied.

Does this make any sense? If VBA is needed... what would the code look like?

Thanks,

Tigershark

»

Copying certain rows

Submitted by Jon on 21 June, 2013 - 14:38.

Hi,

It can be done with formulas alone but it will require somewhat complex formulas as well as formulas in additional helper columns. Let me know if you need to do it that way

»

Your VBA question

Submitted by Robert on 4 May, 2011 - 22:36.

If you do want to use VBA, the code could look something like this:
 

Sub CopyRows()
 
strEnr = InputBox("Enter employee number", "Employee number")
Enr = CInt(strEnr)
 
Set s1 = Worksheets(1)
n = Application.CountA(s1.Columns(1))
 
i = 0
 
For j = 1 To n
    If Worksheets(1).Cells(j, 15).Value = Enr Then
        i = i + 1
        Set r = Worksheets(1).Rows(j)
        r.Copy Destination:=Worksheets(2).Rows(i)
    End If
Next j
 
End Sub
»

Need information about this without VBA

Submitted by BeeFrench on 22 January, 2016 - 08:09.

Been searching for vlookup and everything I find has pointed to VBA scripts.
I however have a copy of excel of sorts which does not support VBA scripting.

I have a similar situation,
What I have are 2 sets of import data.
One in a particular format which corresponds to what are called MLS numbers. I have sorted these in a particualr order, but the second list has these numbers, not in the same cells nor are they in the same order.

What I would like to do is correspond these particular numbers in a list, sort them so they are the same but match the first column.

Then taking the newly sorted list, generate the corresponding data from an array, each of the 11 files has their own row, but the data goes from lets say A1 through FZ12

If the second set of data has a sorted value of 2 in the row value then I want to be able to populate A2 through FZ2 in A30, then the second set of data which is property sorted, the A7 through FZ7 would then be populated in Cells A3:FZ3 and so on.

This was the only thread all day long I have found which mentions doing it via formulas without VBA scripting like all the other sites I have found.

Thank you very much in advance for any help.

»
Nick's picture

filter

Submitted by Nick on 4 May, 2011 - 12:19.
  • copy all the data across to sheet2.
  • use Autofilter to filter out the rows you don't want
  • delete them
  • turn off filter, and you're left with the ones you do want

more on autofilter:

excelexperts.com/Teach-Yourself-Excel-Lesson-21-Filter-Data

»