EXCEL VBA RANGE Question

[mike]

Hi,

I wrote the code below I read from a book to pull data from a tab
("Prioritization Brk Out") to update another tab w/in the same spreadsheet.

However, I now need to have the code to look @ "Prioritization Brk Out" tab
and now only pull only the rows of data if that particular row has the value
"Commercial" in it.

I have reached the end of my VBA skills and am asking for this fine
communities help.

Mike

------------

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False


Range("A7:A100").Value = Sheets("Prioritization Brk
Out").Range("G1:G100").Value

Range("B7:B100").Value = Sheets("Prioritization Brk
Out").Range("A1:A100").Value

Range("C7:C100").Value = Sheets("Prioritization Brk
Out").Range("H1:H100").Value

Range("D7100").Value = Sheets("Prioritization Brk
Out").Range("L1:L100").Value

Application.ScreenUpdating = True
End Sub

 

[Gary''s Student]

Say we run your code as-is. Then we delete the unwanted rows after the copy.
Which column in the result sheet should have "Commercial" in it??

 

[Rick Rothstein]

Where is the word "Commercial" located... in any cell within the row or in
the cells of a particular column? Also, is the word Commercial the only word
in the cell or can there be other text in the cell with it? Also, I'm
assuming when you say copy the row, you mean rows A thru D, correct?

 

[JE McGimpsey]

One way:

Private Sub Commandbutton1_Click()
Const csTarget As String = "Commercial"
Dim rSource As Range
Dim rDest As Range
Dim arr As Variant
Dim i As Long

Set rDest = ActiveSheet.Range("A7:A100")
Set rSource = Worksheets( _
"Prioritization Brk Out").Range("A7:A100")
arr = Array(6, 0, 7, 11)
For i = 0 To 3
With rSource.Offset(, arr(i))
If Application.CountIf(.Cells, csTarget) > 0 Then _
rDest.Offset(, i).Value = .Value
End With
Next i
End Sub

 

[mike]

Hi,

In the "Priortization Brk" tab column G has the value "Commercial" - each
cell has only 1 value but it could be either "Commercial" "TELCO" "Retail
Sales" and et cetera.

 

[mike]

Hi,

In the "Priortization Brk" tab column G has the value "Commercial" - each
cell has only 1 value but it could be either "Commercial" "TELCO" "Retail
Sales" and et cetera.

For example:

A1: Project B1: Project Name ... G1: Department (Commercial, TELCO and et
cetera).

So I want to pull from "Priortization Brk" paste the the rows stated below
but only where column G = Commercial.

Thanks


Mike

 

[Rick Rothstein]

Give this macro a try, but first change the "Other Tab" references (there
are 2 of them) to the tab name of the sheet you want to copy your rows
to)...

Sub MoveCommercialRows()
Dim X As Long
Dim Offset As Long
Dim LastRow As Long
Dim OtherLastRow As Long
Const Text As String = "Commercial"
With Worksheets("Other Tab")
OtherLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If OtherLastRow = 1 And .Cells(.Rows.Count, "A").Value = "" Then
OtherLastRow = 0
End If
End With
With Worksheets("Prioritization Brk Out")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
If StrComp(.Cells(X, "G").Value, Text, vbTextCompare) = 0 Then
OtherLastRow = OtherLastRow + 1
.Rows(X).Copy Worksheets("Other Tab").Cells(OtherLastRow, 1)
End If
Next
End With
End Sub

 

[mike]

oh this is nice!

but is it possible to pull only certain columns? i don't need all the
columns of information but just a few columns of information - is there a way
to do that?

 

[Rick Rothstein]

Which columns? My main thrust in this question is whether all the columns
are contiguous or not, but if you tell us which columns, it makes it easier
to modify the code with minimal explanation of how you might have to change
it.

 

[mike]

Hi Rick,

If column G in "Prioritization Brk Out" has the value "Commercial" then I'd
like to update the sheet with that particular row with the column information
layout below.

(I hope my description is clear : /)

Here's the flow of things:

If: G1 or G2 or G3 or G4 and et cetera in "Prioritization Brk Out" has the
value "Commercial"

Then

Update the sheet I'm in ----
Range("A18:A100").Value = Sheets("Prioritization Brk
Out").Range("A1:A100").Value

Range("B18:B100").Value = Sheets("Prioritization Brk
Out").Range("C1:C100").Value

Range("C18:C100").Value = Sheets("Prioritization Brk
Out").Range("DC1:C100").Value

Range("E18:E100").Value = Sheets("Prioritization Brk
Out").Range("G1:G100").Value