Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi, i have this line of code that copy 6 cells to the right. Can this be
modified to copy the cell before those 6 cells too? dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name The word this macro is searching for is in B and this is why copied range is B:G This code copy from range B:G. Can this code be made to copy from range A:G? Thanks!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi,
This time we need the offset function c.Offset(-1).Resize(2, 7).Copy I assume that c is a Range object so imagine C is actually A10. We offset -1 row to A9 then we resize by 2 rows and it now becomes A9:A10 and then by 7 columns to achieve what you want -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, i have this line of code that copy 6 cells to the right. Can this be modified to copy the cell before those 6 cells too? dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name The word this macro is searching for is in B and this is why copied range is B:G This code copy from range B:G. Can this code be made to copy from range A:G? Thanks!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi, is not working. I haven't explained exactly the first time. Below is the
entire code. I guess is easy for you to see the entire code than my explanation. The code copy 6 cells starting from B column and display them in "search" sheet. The seven cell displayed in search sheet is the sheet's name from where cells were copied. The macro ignore from search, sheets : planning and search. This is the entire code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$H$1" Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False With Sheets("Search") lr = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, 4) ..Range("a3:g" & lr).ClearContents what = UCase(.Range("H1")) On Error Resume Next mydays = Array("LUNI", "MARTI", "MIERCURI", "JOI", "VINERI") For Each sh In mydays With Worksheets(sh).Range("b5:b1000") Set c = .Find(what, LookIn:=xlValues, lookat:=xlPart) If Not c Is Nothing Then firstAddress = c.Address Do dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next sh lr = .Cells(Rows.Count, 1).End(xlUp).Row .Range("a3:g" & lr).Borders.LineStyle = xlNone End With Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks!! "puiuluipui" wrote: Hi, i have this line of code that copy 6 cells to the right. Can this be modified to copy the cell before those 6 cells too? dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name The word this macro is searching for is in B and this is why copied range is B:G This code copy from range B:G. Can this code be made to copy from range A:G? Thanks!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi,
OK so you find some value in column B and then copy columns A to G of that row. What you want to do if I understand correctly is coppy A to G of the row you find AND A to g of the previous row. is that correct? If so i gave you the correct answer last time. Replace this row c.Resize(, 7).Copy Sheets("Search").Cells(dlr, "a") With this one c.Offset(-1).Resize(2, 7).Copy Sheets("Search").Cells(dlr, "a") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, is not working. I haven't explained exactly the first time. Below is the entire code. I guess is easy for you to see the entire code than my explanation. The code copy 6 cells starting from B column and display them in "search" sheet. The seven cell displayed in search sheet is the sheet's name from where cells were copied. The macro ignore from search, sheets : planning and search. This is the entire code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$H$1" Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False With Sheets("Search") lr = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, 4) .Range("a3:g" & lr).ClearContents what = UCase(.Range("H1")) On Error Resume Next mydays = Array("LUNI", "MARTI", "MIERCURI", "JOI", "VINERI") For Each sh In mydays With Worksheets(sh).Range("b5:b1000") Set c = .Find(what, LookIn:=xlValues, lookat:=xlPart) If Not c Is Nothing Then firstAddress = c.Address Do dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next sh lr = .Cells(Rows.Count, 1).End(xlUp).Row .Range("a3:g" & lr).Borders.LineStyle = xlNone End With Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks!! "puiuluipui" wrote: Hi, i have this line of code that copy 6 cells to the right. Can this be modified to copy the cell before those 6 cells too? dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name The word this macro is searching for is in B and this is why copied range is B:G This code copy from range B:G. Can this code be made to copy from range A:G? Thanks!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi,
What i want to do is copy A to G of the row i find. This is all. I don't want another row. If i search for "John" and the macro finds "John" in B5, then the macro to copy A5:G5. I tried your ideea and it's fine, but although copy the row above, doesn't copy from A to G. It copy from B to H. Thanks! "Mike H" wrote: Hi, OK so you find some value in column B and then copy columns A to G of that row. What you want to do if I understand correctly is coppy A to G of the row you find AND A to g of the previous row. is that correct? If so i gave you the correct answer last time. Replace this row c.Resize(, 7).Copy Sheets("Search").Cells(dlr, "a") With this one c.Offset(-1).Resize(2, 7).Copy Sheets("Search").Cells(dlr, "a") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, is not working. I haven't explained exactly the first time. Below is the entire code. I guess is easy for you to see the entire code than my explanation. The code copy 6 cells starting from B column and display them in "search" sheet. The seven cell displayed in search sheet is the sheet's name from where cells were copied. The macro ignore from search, sheets : planning and search. This is the entire code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$H$1" Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False With Sheets("Search") lr = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, 4) .Range("a3:g" & lr).ClearContents what = UCase(.Range("H1")) On Error Resume Next mydays = Array("LUNI", "MARTI", "MIERCURI", "JOI", "VINERI") For Each sh In mydays With Worksheets(sh).Range("b5:b1000") Set c = .Find(what, LookIn:=xlValues, lookat:=xlPart) If Not c Is Nothing Then firstAddress = c.Address Do dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next sh lr = .Cells(Rows.Count, 1).End(xlUp).Row .Range("a3:g" & lr).Borders.LineStyle = xlNone End With Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks!! "puiuluipui" wrote: Hi, i have this line of code that copy 6 cells to the right. Can this be modified to copy the cell before those 6 cells too? dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name The word this macro is searching for is in B and this is why copied range is B:G This code copy from range B:G. Can this code be made to copy from range A:G? Thanks!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
Hi,
Now I understand. This is the correct line c.Offset(, -1).Resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Note this steps 1 column to the left c.Offset(, -1) and then resizes to 7 columns from column A so A to g are copied But now there's a problem because in the next line of your code Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name You write the sheet name to column G which will overwrite the last cell you just copied. You will have to copy 6 columns or put the sheet name somewhere else (say) column H -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, What i want to do is copy A to G of the row i find. This is all. I don't want another row. If i search for "John" and the macro finds "John" in B5, then the macro to copy A5:G5. I tried your ideea and it's fine, but although copy the row above, doesn't copy from A to G. It copy from B to H. Thanks! "Mike H" wrote: Hi, OK so you find some value in column B and then copy columns A to G of that row. What you want to do if I understand correctly is coppy A to G of the row you find AND A to g of the previous row. is that correct? If so i gave you the correct answer last time. Replace this row c.Resize(, 7).Copy Sheets("Search").Cells(dlr, "a") With this one c.Offset(-1).Resize(2, 7).Copy Sheets("Search").Cells(dlr, "a") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, is not working. I haven't explained exactly the first time. Below is the entire code. I guess is easy for you to see the entire code than my explanation. The code copy 6 cells starting from B column and display them in "search" sheet. The seven cell displayed in search sheet is the sheet's name from where cells were copied. The macro ignore from search, sheets : planning and search. This is the entire code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$H$1" Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False With Sheets("Search") lr = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, 4) .Range("a3:g" & lr).ClearContents what = UCase(.Range("H1")) On Error Resume Next mydays = Array("LUNI", "MARTI", "MIERCURI", "JOI", "VINERI") For Each sh In mydays With Worksheets(sh).Range("b5:b1000") Set c = .Find(what, LookIn:=xlValues, lookat:=xlPart) If Not c Is Nothing Then firstAddress = c.Address Do dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next sh lr = .Cells(Rows.Count, 1).End(xlUp).Row .Range("a3:g" & lr).Borders.LineStyle = xlNone End With Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks!! "puiuluipui" wrote: Hi, i have this line of code that copy 6 cells to the right. Can this be modified to copy the cell before those 6 cells too? dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name The word this macro is searching for is in B and this is why copied range is B:G This code copy from range B:G. Can this code be made to copy from range A:G? Thanks!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro
This is exactly what i needed.
Excellent work! Thanks! I have one more question. Is there any way i can insert in this macro some names that will belong to certain groups? Ex: Group 1 John Mary Group 2 Sam George Jim What i need: If i search John, then the result to be all rows containing "John" If i search Group 1, than the result to be all rows containing "John" and all rows containing "Mary" The same with group 2. If i search by name to display only rows containing that name. if i search by group, than the macro to display rows containing all names in that group. This thing will be so great. Can this be done? Thanks!!! "Mike H" wrote: Hi, Now I understand. This is the correct line c.Offset(, -1).Resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Note this steps 1 column to the left c.Offset(, -1) and then resizes to 7 columns from column A so A to g are copied But now there's a problem because in the next line of your code Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name You write the sheet name to column G which will overwrite the last cell you just copied. You will have to copy 6 columns or put the sheet name somewhere else (say) column H -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, What i want to do is copy A to G of the row i find. This is all. I don't want another row. If i search for "John" and the macro finds "John" in B5, then the macro to copy A5:G5. I tried your ideea and it's fine, but although copy the row above, doesn't copy from A to G. It copy from B to H. Thanks! "Mike H" wrote: Hi, OK so you find some value in column B and then copy columns A to G of that row. What you want to do if I understand correctly is coppy A to G of the row you find AND A to g of the previous row. is that correct? If so i gave you the correct answer last time. Replace this row c.Resize(, 7).Copy Sheets("Search").Cells(dlr, "a") With this one c.Offset(-1).Resize(2, 7).Copy Sheets("Search").Cells(dlr, "a") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, is not working. I haven't explained exactly the first time. Below is the entire code. I guess is easy for you to see the entire code than my explanation. The code copy 6 cells starting from B column and display them in "search" sheet. The seven cell displayed in search sheet is the sheet's name from where cells were copied. The macro ignore from search, sheets : planning and search. This is the entire code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$H$1" Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False With Sheets("Search") lr = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, 4) .Range("a3:g" & lr).ClearContents what = UCase(.Range("H1")) On Error Resume Next mydays = Array("LUNI", "MARTI", "MIERCURI", "JOI", "VINERI") For Each sh In mydays With Worksheets(sh).Range("b5:b1000") Set c = .Find(what, LookIn:=xlValues, lookat:=xlPart) If Not c Is Nothing Then firstAddress = c.Address Do dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next sh lr = .Cells(Rows.Count, 1).End(xlUp).Row .Range("a3:g" & lr).Borders.LineStyle = xlNone End With Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks!! "puiuluipui" wrote: Hi, i have this line of code that copy 6 cells to the right. Can this be modified to copy the cell before those 6 cells too? dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1 c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a") Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name The word this macro is searching for is in B and this is why copied range is B:G This code copy from range B:G. Can this code be made to copy from range A:G? Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) |