| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL VBA RANGE Question
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL VBA RANGE Question
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?? -- Gary''s Student - gsnu200808 "mike" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL VBA RANGE Question
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL VBA RANGE Question
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 In article , mike wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL VBA RANGE Question
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. "Gary''s Student" wrote: 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?? -- Gary''s Student - gsnu200808 "mike" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL VBA RANGE Question
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL VBA RANGE Question
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL VBA RANGE Question
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL VBA RANGE Question
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL VBA RANGE Question
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheet Range Question - Excel 2003 | Excel Programming | |||
| Excel VBA .... Worksheet_Change(ByVal Target As Range) question | Excel Programming | |||
| Excel Named Range Question | Excel Programming | |||
| Excel Named Range Question | Excel Programming | |||
| Range.Formula and Range question using Excel Automation | Excel Programming | |||
Linear Mode
