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


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"mike" wrote in message
...
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" wrote:

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

--
Rick (MVP - Excel)


"mike" wrote in message
...
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" wrote:

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?

--
Rick (MVP - Excel)


"mike" wrote in message
...
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("D7:D100").Value = Sheets("Prioritization Brk
Out").Range("L1:L100").Value

Application.ScreenUpdating = True
End Sub