| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro - ignore row
Hi, i have a macro that copy a range (B2:J150)...and i need the macro to
ignore row that begin with "zzzz". I need macro to copy everything in range, except the row that begin with "zzzz". I only need this part of the code. Can this be done? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro - ignore row
Try the below macro which copy the range to Sheet2 ignoring the rows which
start with "zzzz" in Column B. Sub Macro() Dim lngRow As Long For Each cell In Range("B2:J150").Rows If Not cell.Cells(1).Text Like "zzzz*" Then lngRow = lngRow + 1 cell.Copy Sheets("Sheet2").Range("A" & lngRow) End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have a macro that copy a range (B2:J150)...and i need the macro to ignore row that begin with "zzzz". I need macro to copy everything in range, except the row that begin with "zzzz". I only need this part of the code. Can this be done? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro - ignore row
Hi, your code is working, but i need the same thing applied to this code.
I can't make this work. Sub CopyPaste() ' ' CopyPaste Macro ' Macro recorded 10.09.2009 by J ' ' Range("B2").Select Windows("ev com.xls").Activate Range("B5:G150").Select ActiveWindow.SmallScroll Down:=-129 Range("B5:G150,J5:K150").Select Range("J5").Activate Selection.Copy Windows("Copy To.xls").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B2").Select ActiveWindow.SmallScroll Down:=-18 End Sub Thanks allot! "Jacob Skaria" a scris: Try the below macro which copy the range to Sheet2 ignoring the rows which start with "zzzz" in Column B. Sub Macro() Dim lngRow As Long For Each cell In Range("B2:J150").Rows If Not cell.Cells(1).Text Like "zzzz*" Then lngRow = lngRow + 1 cell.Copy Sheets("Sheet2").Range("A" & lngRow) End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have a macro that copy a range (B2:J150)...and i need the macro to ignore row that begin with "zzzz". I need macro to copy everything in range, except the row that begin with "zzzz". I only need this part of the code. Can this be done? Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro - ignore row
Try the below
This gets copied to the destination workbook. ColB. Change if required...Try and feedback Sub CopyPaste() Dim lngRow As Long, lngNRow As Long, wb As Workbook Set wb = Workbooks("Copy To.xls") Windows("ev com.xls").Activate For lngRow = 2 To 150 If Not Range("B" & lngRow) Like "zzzz*" Then lngNRow = lngNRow + 1 Range("B" & lngRow & ":G" & lngRow & ",J" & lngRow & _ ":K" & lngRow).Copy wb.ActiveSheet.Range("B" & lngRow) End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, your code is working, but i need the same thing applied to this code. I can't make this work. Sub CopyPaste() ' ' CopyPaste Macro ' Macro recorded 10.09.2009 by J ' ' Range("B2").Select Windows("ev com.xls").Activate Range("B5:G150").Select ActiveWindow.SmallScroll Down:=-129 Range("B5:G150,J5:K150").Select Range("J5").Activate Selection.Copy Windows("Copy To.xls").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B2").Select ActiveWindow.SmallScroll Down:=-18 End Sub Thanks allot! "Jacob Skaria" a scris: Try the below macro which copy the range to Sheet2 ignoring the rows which start with "zzzz" in Column B. Sub Macro() Dim lngRow As Long For Each cell In Range("B2:J150").Rows If Not cell.Cells(1).Text Like "zzzz*" Then lngRow = lngRow + 1 cell.Copy Sheets("Sheet2").Range("A" & lngRow) End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have a macro that copy a range (B2:J150)...and i need the macro to ignore row that begin with "zzzz". I need macro to copy everything in range, except the row that begin with "zzzz". I only need this part of the code. Can this be done? Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro - ignore row
Hi, i need two small changes. I nee the code to copy/paste special only
values, without borders, and after the macro copy from B5:G150 and J5:K150, to paste in workbook COPY TO starting from B2. Can this be done? Thanks! "Jacob Skaria" a scris: Try the below This gets copied to the destination workbook. ColB. Change if required...Try and feedback Sub CopyPaste() Dim lngRow As Long, lngNRow As Long, wb As Workbook Set wb = Workbooks("Copy To.xls") Windows("ev com.xls").Activate For lngRow = 2 To 150 If Not Range("B" & lngRow) Like "zzzz*" Then lngNRow = lngNRow + 1 Range("B" & lngRow & ":G" & lngRow & ",J" & lngRow & _ ":K" & lngRow).Copy wb.ActiveSheet.Range("B" & lngRow) End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, your code is working, but i need the same thing applied to this code. I can't make this work. Sub CopyPaste() ' ' CopyPaste Macro ' Macro recorded 10.09.2009 by J ' ' Range("B2").Select Windows("ev com.xls").Activate Range("B5:G150").Select ActiveWindow.SmallScroll Down:=-129 Range("B5:G150,J5:K150").Select Range("J5").Activate Selection.Copy Windows("Copy To.xls").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B2").Select ActiveWindow.SmallScroll Down:=-18 End Sub Thanks allot! "Jacob Skaria" a scris: Try the below macro which copy the range to Sheet2 ignoring the rows which start with "zzzz" in Column B. Sub Macro() Dim lngRow As Long For Each cell In Range("B2:J150").Rows If Not cell.Cells(1).Text Like "zzzz*" Then lngRow = lngRow + 1 cell.Copy Sheets("Sheet2").Range("A" & lngRow) End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have a macro that copy a range (B2:J150)...and i need the macro to ignore row that begin with "zzzz". I need macro to copy everything in range, except the row that begin with "zzzz". I only need this part of the code. Can this be done? Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro - ignore row
Try the below and feedback
Sub CopyPaste() Dim lngRow As Long, lngNRow As Long, wb As Workbook Set wb = Workbooks("Copy To.xls") Windows("ev com.xls").Activate lngNRow = 1 For lngRow = 2 To 150 If Not Range("B" & lngRow) Like "zzzz*" Then lngNRow = lngNRow + 1 wb.ActiveSheet.Range("B" & lngNRow & ":G" & lngNRow & ",J" & _ lngNRow & ":K" & lngNRow).Value = Range("B" & lngRow & ":G" & _ lngRow & ",J" & lngRow & ":K" & lngRow).Value End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need two small changes. I nee the code to copy/paste special only values, without borders, and after the macro copy from B5:G150 and J5:K150, to paste in workbook COPY TO starting from B2. Can this be done? Thanks! "Jacob Skaria" a scris: Try the below This gets copied to the destination workbook. ColB. Change if required...Try and feedback Sub CopyPaste() Dim lngRow As Long, lngNRow As Long, wb As Workbook Set wb = Workbooks("Copy To.xls") Windows("ev com.xls").Activate For lngRow = 2 To 150 If Not Range("B" & lngRow) Like "zzzz*" Then lngNRow = lngNRow + 1 Range("B" & lngRow & ":G" & lngRow & ",J" & lngRow & _ ":K" & lngRow).Copy wb.ActiveSheet.Range("B" & lngRow) End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, your code is working, but i need the same thing applied to this code. I can't make this work. Sub CopyPaste() ' ' CopyPaste Macro ' Macro recorded 10.09.2009 by J ' ' Range("B2").Select Windows("ev com.xls").Activate Range("B5:G150").Select ActiveWindow.SmallScroll Down:=-129 Range("B5:G150,J5:K150").Select Range("J5").Activate Selection.Copy Windows("Copy To.xls").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B2").Select ActiveWindow.SmallScroll Down:=-18 End Sub Thanks allot! "Jacob Skaria" a scris: Try the below macro which copy the range to Sheet2 ignoring the rows which start with "zzzz" in Column B. Sub Macro() Dim lngRow As Long For Each cell In Range("B2:J150").Rows If Not cell.Cells(1).Text Like "zzzz*" Then lngRow = lngRow + 1 cell.Copy Sheets("Sheet2").Range("A" & lngRow) End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have a macro that copy a range (B2:J150)...and i need the macro to ignore row that begin with "zzzz". I need macro to copy everything in range, except the row that begin with "zzzz". I only need this part of the code. Can this be done? Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro - ignore row
It's still copy border. And now i have 2 blank columns between the other
columns. And i need the code to copy from row 5 to row 2. I need the macro to do everything exactly like "my macro" but to ignore "zzzz" exactly like "your macro". can this be done? Thanks! "Jacob Skaria" a scris: Try the below and feedback Sub CopyPaste() Dim lngRow As Long, lngNRow As Long, wb As Workbook Set wb = Workbooks("Copy To.xls") Windows("ev com.xls").Activate lngNRow = 1 For lngRow = 2 To 150 If Not Range("B" & lngRow) Like "zzzz*" Then lngNRow = lngNRow + 1 wb.ActiveSheet.Range("B" & lngNRow & ":G" & lngNRow & ",J" & _ lngNRow & ":K" & lngNRow).Value = Range("B" & lngRow & ":G" & _ lngRow & ",J" & lngRow & ":K" & lngRow).Value End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need two small changes. I nee the code to copy/paste special only values, without borders, and after the macro copy from B5:G150 and J5:K150, to paste in workbook COPY TO starting from B2. Can this be done? Thanks! "Jacob Skaria" a scris: Try the below This gets copied to the destination workbook. ColB. Change if required...Try and feedback Sub CopyPaste() Dim lngRow As Long, lngNRow As Long, wb As Workbook Set wb = Workbooks("Copy To.xls") Windows("ev com.xls").Activate For lngRow = 2 To 150 If Not Range("B" & lngRow) Like "zzzz*" Then lngNRow = lngNRow + 1 Range("B" & lngRow & ":G" & lngRow & ",J" & lngRow & _ ":K" & lngRow).Copy wb.ActiveSheet.Range("B" & lngRow) End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, your code is working, but i need the same thing applied to this code. I can't make this work. Sub CopyPaste() ' ' CopyPaste Macro ' Macro recorded 10.09.2009 by J ' ' Range("B2").Select Windows("ev com.xls").Activate Range("B5:G150").Select ActiveWindow.SmallScroll Down:=-129 Range("B5:G150,J5:K150").Select Range("J5").Activate Selection.Copy Windows("Copy To.xls").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B2").Select ActiveWindow.SmallScroll Down:=-18 End Sub Thanks allot! "Jacob Skaria" a scris: Try the below macro which copy the range to Sheet2 ignoring the rows which start with "zzzz" in Column B. Sub Macro() Dim lngRow As Long For Each cell In Range("B2:J150").Rows If Not cell.Cells(1).Text Like "zzzz*" Then lngRow = lngRow + 1 cell.Copy Sheets("Sheet2").Range("A" & lngRow) End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have a macro that copy a range (B2:J150)...and i need the macro to ignore row that begin with "zzzz". I need macro to copy everything in range, except the row that begin with "zzzz". I only need this part of the code. Can this be done? Thanks! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro - ignore row
I have tested that; that should not copy the borders..However I have modified
your macro now..as below..Try and feedback Sub CopyPaste() Application.ScreenUpdating = False Windows("ev com.xls").Activate Range("B5:G150,J5:K150").Copy Windows("Copy To.xls").Activate ActiveSheet.Range("B2").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("B2").Select For lngRow = 150 To 2 Step -1 If Range("B" & lngRow) Like "zzzz*" Then Rows(lngRow).Delete Next Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: It's still copy border. And now i have 2 blank columns between the other columns. And i need the code to copy from row 5 to row 2. I need the macro to do everything exactly like "my macro" but to ignore "zzzz" exactly like "your macro". can this be done? Thanks! "Jacob Skaria" a scris: Try the below and feedback Sub CopyPaste() Dim lngRow As Long, lngNRow As Long, wb As Workbook Set wb = Workbooks("Copy To.xls") Windows("ev com.xls").Activate lngNRow = 1 For lngRow = 2 To 150 If Not Range("B" & lngRow) Like "zzzz*" Then lngNRow = lngNRow + 1 wb.ActiveSheet.Range("B" & lngNRow & ":G" & lngNRow & ",J" & _ lngNRow & ":K" & lngNRow).Value = Range("B" & lngRow & ":G" & _ lngRow & ",J" & lngRow & ":K" & lngRow).Value End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need two small changes. I nee the code to copy/paste special only values, without borders, and after the macro copy from B5:G150 and J5:K150, to paste in workbook COPY TO starting from B2. Can this be done? Thanks! "Jacob Skaria" a scris: Try the below This gets copied to the destination workbook. ColB. Change if required...Try and feedback Sub CopyPaste() Dim lngRow As Long, lngNRow As Long, wb As Workbook Set wb = Workbooks("Copy To.xls") Windows("ev com.xls").Activate For lngRow = 2 To 150 If Not Range("B" & lngRow) Like "zzzz*" Then lngNRow = lngNRow + 1 Range("B" & lngRow & ":G" & lngRow & ",J" & lngRow & _ ":K" & lngRow).Copy wb.ActiveSheet.Range("B" & lngRow) End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, your code is working, but i need the same thing applied to this code. I can't make this work. Sub CopyPaste() ' ' CopyPaste Macro ' Macro recorded 10.09.2009 by J ' ' Range("B2").Select Windows("ev com.xls").Activate Range("B5:G150").Select ActiveWindow.SmallScroll Down:=-129 Range("B5:G150,J5:K150").Select Range("J5").Activate Selection.Copy Windows("Copy To.xls").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B2").Select ActiveWindow.SmallScroll Down:=-18 End Sub Thanks allot! "Jacob Skaria" a scris: Try the below macro which copy the range to Sheet2 ignoring the rows which start with "zzzz" in Column B. Sub Macro() Dim lngRow As Long For Each cell In Range("B2:J150").Rows If Not cell.Cells(1).Text Like "zzzz*" Then lngRow = lngRow + 1 cell.Copy Sheets("Sheet2").Range("A" & lngRow) End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have a macro that copy a range (B2:J150)...and i need the macro to ignore row that begin with "zzzz". I need macro to copy everything in range, except the row that begin with "zzzz". I only need this part of the code. Can this be done? Thanks! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro - ignore row
It's working perfect! You are very patient.
Thanks allot! "Jacob Skaria" wrote: I have tested that; that should not copy the borders..However I have modified your macro now..as below..Try and feedback Sub CopyPaste() Application.ScreenUpdating = False Windows("ev com.xls").Activate Range("B5:G150,J5:K150").Copy Windows("Copy To.xls").Activate ActiveSheet.Range("B2").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("B2").Select For lngRow = 150 To 2 Step -1 If Range("B" & lngRow) Like "zzzz*" Then Rows(lngRow).Delete Next Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: It's still copy border. And now i have 2 blank columns between the other columns. And i need the code to copy from row 5 to row 2. I need the macro to do everything exactly like "my macro" but to ignore "zzzz" exactly like "your macro". can this be done? Thanks! "Jacob Skaria" a scris: Try the below and feedback Sub CopyPaste() Dim lngRow As Long, lngNRow As Long, wb As Workbook Set wb = Workbooks("Copy To.xls") Windows("ev com.xls").Activate lngNRow = 1 For lngRow = 2 To 150 If Not Range("B" & lngRow) Like "zzzz*" Then lngNRow = lngNRow + 1 wb.ActiveSheet.Range("B" & lngNRow & ":G" & lngNRow & ",J" & _ lngNRow & ":K" & lngNRow).Value = Range("B" & lngRow & ":G" & _ lngRow & ",J" & lngRow & ":K" & lngRow).Value End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need two small changes. I nee the code to copy/paste special only values, without borders, and after the macro copy from B5:G150 and J5:K150, to paste in workbook COPY TO starting from B2. Can this be done? Thanks! "Jacob Skaria" a scris: Try the below This gets copied to the destination workbook. ColB. Change if required...Try and feedback Sub CopyPaste() Dim lngRow As Long, lngNRow As Long, wb As Workbook Set wb = Workbooks("Copy To.xls") Windows("ev com.xls").Activate For lngRow = 2 To 150 If Not Range("B" & lngRow) Like "zzzz*" Then lngNRow = lngNRow + 1 Range("B" & lngRow & ":G" & lngRow & ",J" & lngRow & _ ":K" & lngRow).Copy wb.ActiveSheet.Range("B" & lngRow) End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, your code is working, but i need the same thing applied to this code. I can't make this work. Sub CopyPaste() ' ' CopyPaste Macro ' Macro recorded 10.09.2009 by J ' ' Range("B2").Select Windows("ev com.xls").Activate Range("B5:G150").Select ActiveWindow.SmallScroll Down:=-129 Range("B5:G150,J5:K150").Select Range("J5").Activate Selection.Copy Windows("Copy To.xls").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B2").Select ActiveWindow.SmallScroll Down:=-18 End Sub Thanks allot! "Jacob Skaria" a scris: Try the below macro which copy the range to Sheet2 ignoring the rows which start with "zzzz" in Column B. Sub Macro() Dim lngRow As Long For Each cell In Range("B2:J150").Rows If Not cell.Cells(1).Text Like "zzzz*" Then lngRow = lngRow + 1 cell.Copy Sheets("Sheet2").Range("A" & lngRow) End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have a macro that copy a range (B2:J150)...and i need the macro to ignore row that begin with "zzzz". I need macro to copy everything in range, except the row that begin with "zzzz". I only need this part of the code. Can this be done? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Ignore macro run time error | Excel Discussion (Misc queries) | |||
| ignore #N/A in a sum? | Excel Worksheet Functions | |||
| IF to ignore #N/A | Excel Discussion (Misc queries) | |||
| please ignore | Excel Worksheet Functions | |||
| Ignore #DIV/0! | Excel Worksheet Functions | |||
Linear Mode
