Transfer a Range of data if a requirement is met

[PMJonas]

Hey
I’m about to make a macro that transfer a Range of data from Sheet1 to Sheet2 in the excel. The transfer should take place for each row if a requirement is met.

More specific if the data on Sheet1 is for example:

Staff hours

Consulting hours

Driving

Test

Staff hours

In the macro it only supposed to send the data associated with Staff hours.
I have a macro that works but only if it is with one cell and not with the whole Range:

Sub Identificer()

Dim text As String
text = Range("A1").Value

If text = "Staff hours" Then result = Worksheets("Sheet2").Range("I1")

Range("B1").Value = result

End Sub

It don’t work if you write:
Sub Identificer()

Dim text As String
text = Range("A1:A2").Value

If text = "Personaletimer" Then result = Worksheets("Ark2").Range("I1:I2")

Range("B1:B2").Value = result

End Sub

I think maybe there should be another data before sub.

Can somebody help me?

 

[pmich]

Hope this is what you actually mean:
Sheet1 is renamed as DataSheet.
Say, you have the following in Column A from A1 to A5 of DataSheet, which is Sheet1.
Staff hours
Consulting hours
Driving
Test
Staff hours


Say, you have the following in Column B from B1 to B5 of DataSheet, which is Sheet1.
b1b1b1
b2b2b2
b3b3b3
b4b4b4
b5b5b5


Say, you have the following in Column C from C1 to C5 of DataSheet, which is Sheet1.
c1c1c1
c2c2c2
c3c3c3
c4c4c4
c5c5c5


Sheet2 is renamed as TargetSheet.


In Userform have a command button with the name cmdCopyRange.
Now, try this code:

Private Sub cmdCopyRange_Click()
 Dim rng As Range
 Sheets("DataSheet").Select
 Set rng = ActiveSheet.Range("A1:C5")
 rng.AutoFilter
 rng.AutoFilter Field:=1, Criteria1:="Staff hours" 'filtering the requirement
 Worksheets("TargetSheet").Cells.ClearContents 'preparing next sheet
 Dim FiltRng As Range
Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow 'Filter variable gets filtered data 
FiltRng.Copy Worksheets("TargetSheet").Range("A1") 'Pasting FILTERED to another Sheet
Worksheets("TargetSheet").Select
   Range("A1").Select
Set FiltRng = Nothing
  rng.AutoFilter
Set rng = Nothing
End Sub

 

[PMJonas]

Thanks pmich! It seemed to work.

One more question.
If I have the values in the Columns as you write, is it then possible that the macro just copy the rows with b1b1b1,c1c1c1 & b5b5b5, c5c5c5 and not the Column with Staff hours?
Cause when I use the macro it copy following:
Staff hours b1b1b1 c1c1c1
Staff hours b5b5b5 c5c5c5
And I just want:
b1b1b1 c1c1c1
b5b5b5 c5c5c5

 

[PMJonas]

Thanks pmich! It seemed to work.

One more question.
If I have the values in the Columns as you write, is it then possible that the macro just copy the rows with b1b1b1,c1c1c1 & b5b5b5, c5c5c5 and not the Column with Staff hours?
Cause when I use the macro it copy following:
Staff hours b1b1b1 c1c1c1
Staff hours b5b5b5 c5c5c5
And I just want:
b1b1b1 c1c1c1
b5b5b5 c5c5c5

 

[pmich]

Try to change one line in the above code as given below:

FiltRng.Range("B1:C5").Copy Worksheets("TargetSheet").Range("A1")  'Pasting specific columns from FILTERED to another Sheet

 

[PMJonas]

Thanks for the help!

I made the macro without the line:
Worksheets("TargetSheet").Cells.ClearContents

Because it clear out the text and data already in the sheet.

 

[pmich]

Well done.
Glad your need is met.