Copy different rows based Excel VBA
I am trying to auto-copy
rows
from a master worksheet
to a separate worksheet
. This occurs when a specific value is entered into Column B
in a Master sheet
. E.g. if ABC
is entered into Column B
in Master, these rows
will get auto-copied into a separate sheet called ABC
.
The issue is I have other values I want to copy into other worksheets. E.g if DEF
is entered in Column B in Master, then auto-copy into separate sheet called DEF
. I dont know how to do this.
The code below automatically copies all rows when Change
is entered into Column B
. This works fine but I also want to add another function that copies
all rows
when 'Delay' is entered.
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Worksheets("Master")
Set sht2 = Worksheets("Change")
sht2.UsedRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
.AutoFilter field:=1, Criteria1:="Change"
.Range("A:F, BL:BO").Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("H:BK").EntireColumn.Hidden = True ' hide columns
End With
End Sub
That code just copies Change rows from the master sheet to change sheet.
However I want to add another function that copies Delay rows from the master sheet to delay sheet. I'm just not sure if this can be incorporated into the code above? Or if I can do the following:
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht3 As Worksheet
Set sht1 = Worksheets("Master")
Set sht3 = Worksheets("Delay")
sht3.UsedRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
.AutoFilter field:=1, Criteria1:="Delay"
.Range("A:B, BJ:BO").Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("D:BI").EntireColumn.Hidden = True ' hide columns
End With
End Sub
PLEASE NOTE:
This macro has to be triggered without running a script.
excel vba copy
add a comment |
I am trying to auto-copy
rows
from a master worksheet
to a separate worksheet
. This occurs when a specific value is entered into Column B
in a Master sheet
. E.g. if ABC
is entered into Column B
in Master, these rows
will get auto-copied into a separate sheet called ABC
.
The issue is I have other values I want to copy into other worksheets. E.g if DEF
is entered in Column B in Master, then auto-copy into separate sheet called DEF
. I dont know how to do this.
The code below automatically copies all rows when Change
is entered into Column B
. This works fine but I also want to add another function that copies
all rows
when 'Delay' is entered.
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Worksheets("Master")
Set sht2 = Worksheets("Change")
sht2.UsedRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
.AutoFilter field:=1, Criteria1:="Change"
.Range("A:F, BL:BO").Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("H:BK").EntireColumn.Hidden = True ' hide columns
End With
End Sub
That code just copies Change rows from the master sheet to change sheet.
However I want to add another function that copies Delay rows from the master sheet to delay sheet. I'm just not sure if this can be incorporated into the code above? Or if I can do the following:
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht3 As Worksheet
Set sht1 = Worksheets("Master")
Set sht3 = Worksheets("Delay")
sht3.UsedRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
.AutoFilter field:=1, Criteria1:="Delay"
.Range("A:B, BJ:BO").Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("D:BI").EntireColumn.Hidden = True ' hide columns
End With
End Sub
PLEASE NOTE:
This macro has to be triggered without running a script.
excel vba copy
See edit, everything is working fine on my test file.
– Display name
Nov 13 '18 at 5:18
Thanks for your help @O.PAL, but this doesnt copy 'all' rows. The macro isnt triggered either.
– JPA0888
Nov 13 '18 at 6:30
add a comment |
I am trying to auto-copy
rows
from a master worksheet
to a separate worksheet
. This occurs when a specific value is entered into Column B
in a Master sheet
. E.g. if ABC
is entered into Column B
in Master, these rows
will get auto-copied into a separate sheet called ABC
.
The issue is I have other values I want to copy into other worksheets. E.g if DEF
is entered in Column B in Master, then auto-copy into separate sheet called DEF
. I dont know how to do this.
The code below automatically copies all rows when Change
is entered into Column B
. This works fine but I also want to add another function that copies
all rows
when 'Delay' is entered.
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Worksheets("Master")
Set sht2 = Worksheets("Change")
sht2.UsedRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
.AutoFilter field:=1, Criteria1:="Change"
.Range("A:F, BL:BO").Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("H:BK").EntireColumn.Hidden = True ' hide columns
End With
End Sub
That code just copies Change rows from the master sheet to change sheet.
However I want to add another function that copies Delay rows from the master sheet to delay sheet. I'm just not sure if this can be incorporated into the code above? Or if I can do the following:
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht3 As Worksheet
Set sht1 = Worksheets("Master")
Set sht3 = Worksheets("Delay")
sht3.UsedRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
.AutoFilter field:=1, Criteria1:="Delay"
.Range("A:B, BJ:BO").Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("D:BI").EntireColumn.Hidden = True ' hide columns
End With
End Sub
PLEASE NOTE:
This macro has to be triggered without running a script.
excel vba copy
I am trying to auto-copy
rows
from a master worksheet
to a separate worksheet
. This occurs when a specific value is entered into Column B
in a Master sheet
. E.g. if ABC
is entered into Column B
in Master, these rows
will get auto-copied into a separate sheet called ABC
.
The issue is I have other values I want to copy into other worksheets. E.g if DEF
is entered in Column B in Master, then auto-copy into separate sheet called DEF
. I dont know how to do this.
The code below automatically copies all rows when Change
is entered into Column B
. This works fine but I also want to add another function that copies
all rows
when 'Delay' is entered.
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Worksheets("Master")
Set sht2 = Worksheets("Change")
sht2.UsedRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
.AutoFilter field:=1, Criteria1:="Change"
.Range("A:F, BL:BO").Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("H:BK").EntireColumn.Hidden = True ' hide columns
End With
End Sub
That code just copies Change rows from the master sheet to change sheet.
However I want to add another function that copies Delay rows from the master sheet to delay sheet. I'm just not sure if this can be incorporated into the code above? Or if I can do the following:
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht3 As Worksheet
Set sht1 = Worksheets("Master")
Set sht3 = Worksheets("Delay")
sht3.UsedRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
.AutoFilter field:=1, Criteria1:="Delay"
.Range("A:B, BJ:BO").Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("D:BI").EntireColumn.Hidden = True ' hide columns
End With
End Sub
PLEASE NOTE:
This macro has to be triggered without running a script.
excel vba copy
excel vba copy
edited Nov 13 '18 at 14:42
Pᴇʜ
20.3k42650
20.3k42650
asked Nov 13 '18 at 3:08
JPA0888JPA0888
9710
9710
See edit, everything is working fine on my test file.
– Display name
Nov 13 '18 at 5:18
Thanks for your help @O.PAL, but this doesnt copy 'all' rows. The macro isnt triggered either.
– JPA0888
Nov 13 '18 at 6:30
add a comment |
See edit, everything is working fine on my test file.
– Display name
Nov 13 '18 at 5:18
Thanks for your help @O.PAL, but this doesnt copy 'all' rows. The macro isnt triggered either.
– JPA0888
Nov 13 '18 at 6:30
See edit, everything is working fine on my test file.
– Display name
Nov 13 '18 at 5:18
See edit, everything is working fine on my test file.
– Display name
Nov 13 '18 at 5:18
Thanks for your help @O.PAL, but this doesnt copy 'all' rows. The macro isnt triggered either.
– JPA0888
Nov 13 '18 at 6:30
Thanks for your help @O.PAL, but this doesnt copy 'all' rows. The macro isnt triggered either.
– JPA0888
Nov 13 '18 at 6:30
add a comment |
2 Answers
2
active
oldest
votes
Back at it again.
Please note that this is tested and working so please double check before changing anything (like you did with B4 to B5 in the previous test).
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Dim Sh1 As Worksheet: Set Sh1 = Me
Dim Sh2 As Worksheet: Set Sh2 = Worksheets("CHANGE OF NO'S")
Dim Sh3 As Worksheet: Set Sh3 = Worksheets("ECS")
Dim R0 As Range
Dim R1 As Range: Set R1 = Intersect(Sh1.UsedRange, Sh1.Columns(2))
'Clear data in sheets
Sh2.Cells.Clear
Sh2.Range("B4") = "start"
Sh3.Cells.Clear
Sh3.Range("B4") = "start"
'Clear autofilter
If Sh1.AutoFilterMode Then Sh1.AutoFilterMode = False
For Each R0 In R1
Select Case Trim(R0.Value)
Case Is = "Change"
Intersect(R0.EntireRow, Sh1.Range("A:F,BL:BO")).Copy Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(1, 0)
Case Is = "Early"
Intersect(R0.EntireRow, Sh1.Range("A:D,O:R,BL:BO")).Copy Sh3.Cells(Sh3.Rows.Count, 2).End(xlUp).Offset(1, 0)
End Select
Next R0
Sh2.Range("B4") = ""
Sh3.Range("B4") = ""
End If
Application.ScreenUpdating = True
End Sub
This is to be inserted on the "Master" sheet code or whatever you called it. See below:
Now the code will run when you type anything in column "B" in Master sheet. See below:
Sheet Master (Entering a new "Change" text in column "B"):
Updated sheets "CHANGE OF NO'S" and "ECS" :
add a comment |
May I suggest a slightly different approach :
Sub Copy_criteria()
Dim Sh1 As Worksheet: Set Sh1 = Worksheets("SHIFT LOG")
Dim Sh2 As Worksheet: Set Sh2 = Worksheets("CHANGE OF NO'S")
Dim Sh3 As Worksheet: Set Sh3 = Worksheets("ECS")
Dim R0 As Range
Dim R1 As Range: Set R1 = Intersect(Sh1.UsedRange, Sh1.Columns(2))
'Clear data in sheets
Sh2.Cells.Clear
Sh2.Range("B4") = "start"
Sh3.Cells.Clear
Sh3.Range("B4") = "start"
'Clear autofilter
If Sh1.AutoFilterMode Then Sh1.AutoFilterMode = False
For Each R0 In R1
Select Case Trim(R0.Value)
Case Is = "Change"
Intersect(R0.EntireRow, Sh1.Range("A:F,BL:BO")).Copy Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(1, 0)
Case Is = "Early"
Intersect(R0.EntireRow, Sh1.Range("A:D,O:R,BL:BO")).Copy Sh3.Cells(Sh3.Rows.Count, 2).End(xlUp).Offset(1, 0)
End Select
Next R0
Sh2.Range("B4") = ""
Sh3.Range("B4") = ""
End Sub
Thanks @O.PAL. How do I change the row (position) of the sheet where it gets copied too?
– JPA0888
Nov 13 '18 at 4:06
Well if you want it to start on 1st row of change/delay sheets, you'll have to delete that 1st blank row at the end of the macro :Sh2.range("A1").entirerow.delete
andSh3.range("A1").entirerow.delete
. If you want to start at any other row, you can addSh2.range("A?")="-"
where?
is the row you want minus one, same for Sh3.
– Display name
Nov 13 '18 at 4:15
It will always be row 5 Column B.
– JPA0888
Nov 13 '18 at 4:21
Brilliant. Thankyou!
– JPA0888
Nov 13 '18 at 4:34
Do I have to edit this code to get every row related toChange
orDelay
. It doesnt seem to be copying everything over?
– JPA0888
Nov 13 '18 at 4:47
|
show 2 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53273202%2fcopy-different-rows-based-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Back at it again.
Please note that this is tested and working so please double check before changing anything (like you did with B4 to B5 in the previous test).
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Dim Sh1 As Worksheet: Set Sh1 = Me
Dim Sh2 As Worksheet: Set Sh2 = Worksheets("CHANGE OF NO'S")
Dim Sh3 As Worksheet: Set Sh3 = Worksheets("ECS")
Dim R0 As Range
Dim R1 As Range: Set R1 = Intersect(Sh1.UsedRange, Sh1.Columns(2))
'Clear data in sheets
Sh2.Cells.Clear
Sh2.Range("B4") = "start"
Sh3.Cells.Clear
Sh3.Range("B4") = "start"
'Clear autofilter
If Sh1.AutoFilterMode Then Sh1.AutoFilterMode = False
For Each R0 In R1
Select Case Trim(R0.Value)
Case Is = "Change"
Intersect(R0.EntireRow, Sh1.Range("A:F,BL:BO")).Copy Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(1, 0)
Case Is = "Early"
Intersect(R0.EntireRow, Sh1.Range("A:D,O:R,BL:BO")).Copy Sh3.Cells(Sh3.Rows.Count, 2).End(xlUp).Offset(1, 0)
End Select
Next R0
Sh2.Range("B4") = ""
Sh3.Range("B4") = ""
End If
Application.ScreenUpdating = True
End Sub
This is to be inserted on the "Master" sheet code or whatever you called it. See below:
Now the code will run when you type anything in column "B" in Master sheet. See below:
Sheet Master (Entering a new "Change" text in column "B"):
Updated sheets "CHANGE OF NO'S" and "ECS" :
add a comment |
Back at it again.
Please note that this is tested and working so please double check before changing anything (like you did with B4 to B5 in the previous test).
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Dim Sh1 As Worksheet: Set Sh1 = Me
Dim Sh2 As Worksheet: Set Sh2 = Worksheets("CHANGE OF NO'S")
Dim Sh3 As Worksheet: Set Sh3 = Worksheets("ECS")
Dim R0 As Range
Dim R1 As Range: Set R1 = Intersect(Sh1.UsedRange, Sh1.Columns(2))
'Clear data in sheets
Sh2.Cells.Clear
Sh2.Range("B4") = "start"
Sh3.Cells.Clear
Sh3.Range("B4") = "start"
'Clear autofilter
If Sh1.AutoFilterMode Then Sh1.AutoFilterMode = False
For Each R0 In R1
Select Case Trim(R0.Value)
Case Is = "Change"
Intersect(R0.EntireRow, Sh1.Range("A:F,BL:BO")).Copy Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(1, 0)
Case Is = "Early"
Intersect(R0.EntireRow, Sh1.Range("A:D,O:R,BL:BO")).Copy Sh3.Cells(Sh3.Rows.Count, 2).End(xlUp).Offset(1, 0)
End Select
Next R0
Sh2.Range("B4") = ""
Sh3.Range("B4") = ""
End If
Application.ScreenUpdating = True
End Sub
This is to be inserted on the "Master" sheet code or whatever you called it. See below:
Now the code will run when you type anything in column "B" in Master sheet. See below:
Sheet Master (Entering a new "Change" text in column "B"):
Updated sheets "CHANGE OF NO'S" and "ECS" :
add a comment |
Back at it again.
Please note that this is tested and working so please double check before changing anything (like you did with B4 to B5 in the previous test).
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Dim Sh1 As Worksheet: Set Sh1 = Me
Dim Sh2 As Worksheet: Set Sh2 = Worksheets("CHANGE OF NO'S")
Dim Sh3 As Worksheet: Set Sh3 = Worksheets("ECS")
Dim R0 As Range
Dim R1 As Range: Set R1 = Intersect(Sh1.UsedRange, Sh1.Columns(2))
'Clear data in sheets
Sh2.Cells.Clear
Sh2.Range("B4") = "start"
Sh3.Cells.Clear
Sh3.Range("B4") = "start"
'Clear autofilter
If Sh1.AutoFilterMode Then Sh1.AutoFilterMode = False
For Each R0 In R1
Select Case Trim(R0.Value)
Case Is = "Change"
Intersect(R0.EntireRow, Sh1.Range("A:F,BL:BO")).Copy Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(1, 0)
Case Is = "Early"
Intersect(R0.EntireRow, Sh1.Range("A:D,O:R,BL:BO")).Copy Sh3.Cells(Sh3.Rows.Count, 2).End(xlUp).Offset(1, 0)
End Select
Next R0
Sh2.Range("B4") = ""
Sh3.Range("B4") = ""
End If
Application.ScreenUpdating = True
End Sub
This is to be inserted on the "Master" sheet code or whatever you called it. See below:
Now the code will run when you type anything in column "B" in Master sheet. See below:
Sheet Master (Entering a new "Change" text in column "B"):
Updated sheets "CHANGE OF NO'S" and "ECS" :
Back at it again.
Please note that this is tested and working so please double check before changing anything (like you did with B4 to B5 in the previous test).
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Dim Sh1 As Worksheet: Set Sh1 = Me
Dim Sh2 As Worksheet: Set Sh2 = Worksheets("CHANGE OF NO'S")
Dim Sh3 As Worksheet: Set Sh3 = Worksheets("ECS")
Dim R0 As Range
Dim R1 As Range: Set R1 = Intersect(Sh1.UsedRange, Sh1.Columns(2))
'Clear data in sheets
Sh2.Cells.Clear
Sh2.Range("B4") = "start"
Sh3.Cells.Clear
Sh3.Range("B4") = "start"
'Clear autofilter
If Sh1.AutoFilterMode Then Sh1.AutoFilterMode = False
For Each R0 In R1
Select Case Trim(R0.Value)
Case Is = "Change"
Intersect(R0.EntireRow, Sh1.Range("A:F,BL:BO")).Copy Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(1, 0)
Case Is = "Early"
Intersect(R0.EntireRow, Sh1.Range("A:D,O:R,BL:BO")).Copy Sh3.Cells(Sh3.Rows.Count, 2).End(xlUp).Offset(1, 0)
End Select
Next R0
Sh2.Range("B4") = ""
Sh3.Range("B4") = ""
End If
Application.ScreenUpdating = True
End Sub
This is to be inserted on the "Master" sheet code or whatever you called it. See below:
Now the code will run when you type anything in column "B" in Master sheet. See below:
Sheet Master (Entering a new "Change" text in column "B"):
Updated sheets "CHANGE OF NO'S" and "ECS" :
answered Nov 13 '18 at 21:59
Display nameDisplay name
55416
55416
add a comment |
add a comment |
May I suggest a slightly different approach :
Sub Copy_criteria()
Dim Sh1 As Worksheet: Set Sh1 = Worksheets("SHIFT LOG")
Dim Sh2 As Worksheet: Set Sh2 = Worksheets("CHANGE OF NO'S")
Dim Sh3 As Worksheet: Set Sh3 = Worksheets("ECS")
Dim R0 As Range
Dim R1 As Range: Set R1 = Intersect(Sh1.UsedRange, Sh1.Columns(2))
'Clear data in sheets
Sh2.Cells.Clear
Sh2.Range("B4") = "start"
Sh3.Cells.Clear
Sh3.Range("B4") = "start"
'Clear autofilter
If Sh1.AutoFilterMode Then Sh1.AutoFilterMode = False
For Each R0 In R1
Select Case Trim(R0.Value)
Case Is = "Change"
Intersect(R0.EntireRow, Sh1.Range("A:F,BL:BO")).Copy Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(1, 0)
Case Is = "Early"
Intersect(R0.EntireRow, Sh1.Range("A:D,O:R,BL:BO")).Copy Sh3.Cells(Sh3.Rows.Count, 2).End(xlUp).Offset(1, 0)
End Select
Next R0
Sh2.Range("B4") = ""
Sh3.Range("B4") = ""
End Sub
Thanks @O.PAL. How do I change the row (position) of the sheet where it gets copied too?
– JPA0888
Nov 13 '18 at 4:06
Well if you want it to start on 1st row of change/delay sheets, you'll have to delete that 1st blank row at the end of the macro :Sh2.range("A1").entirerow.delete
andSh3.range("A1").entirerow.delete
. If you want to start at any other row, you can addSh2.range("A?")="-"
where?
is the row you want minus one, same for Sh3.
– Display name
Nov 13 '18 at 4:15
It will always be row 5 Column B.
– JPA0888
Nov 13 '18 at 4:21
Brilliant. Thankyou!
– JPA0888
Nov 13 '18 at 4:34
Do I have to edit this code to get every row related toChange
orDelay
. It doesnt seem to be copying everything over?
– JPA0888
Nov 13 '18 at 4:47
|
show 2 more comments
May I suggest a slightly different approach :
Sub Copy_criteria()
Dim Sh1 As Worksheet: Set Sh1 = Worksheets("SHIFT LOG")
Dim Sh2 As Worksheet: Set Sh2 = Worksheets("CHANGE OF NO'S")
Dim Sh3 As Worksheet: Set Sh3 = Worksheets("ECS")
Dim R0 As Range
Dim R1 As Range: Set R1 = Intersect(Sh1.UsedRange, Sh1.Columns(2))
'Clear data in sheets
Sh2.Cells.Clear
Sh2.Range("B4") = "start"
Sh3.Cells.Clear
Sh3.Range("B4") = "start"
'Clear autofilter
If Sh1.AutoFilterMode Then Sh1.AutoFilterMode = False
For Each R0 In R1
Select Case Trim(R0.Value)
Case Is = "Change"
Intersect(R0.EntireRow, Sh1.Range("A:F,BL:BO")).Copy Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(1, 0)
Case Is = "Early"
Intersect(R0.EntireRow, Sh1.Range("A:D,O:R,BL:BO")).Copy Sh3.Cells(Sh3.Rows.Count, 2).End(xlUp).Offset(1, 0)
End Select
Next R0
Sh2.Range("B4") = ""
Sh3.Range("B4") = ""
End Sub
Thanks @O.PAL. How do I change the row (position) of the sheet where it gets copied too?
– JPA0888
Nov 13 '18 at 4:06
Well if you want it to start on 1st row of change/delay sheets, you'll have to delete that 1st blank row at the end of the macro :Sh2.range("A1").entirerow.delete
andSh3.range("A1").entirerow.delete
. If you want to start at any other row, you can addSh2.range("A?")="-"
where?
is the row you want minus one, same for Sh3.
– Display name
Nov 13 '18 at 4:15
It will always be row 5 Column B.
– JPA0888
Nov 13 '18 at 4:21
Brilliant. Thankyou!
– JPA0888
Nov 13 '18 at 4:34
Do I have to edit this code to get every row related toChange
orDelay
. It doesnt seem to be copying everything over?
– JPA0888
Nov 13 '18 at 4:47
|
show 2 more comments
May I suggest a slightly different approach :
Sub Copy_criteria()
Dim Sh1 As Worksheet: Set Sh1 = Worksheets("SHIFT LOG")
Dim Sh2 As Worksheet: Set Sh2 = Worksheets("CHANGE OF NO'S")
Dim Sh3 As Worksheet: Set Sh3 = Worksheets("ECS")
Dim R0 As Range
Dim R1 As Range: Set R1 = Intersect(Sh1.UsedRange, Sh1.Columns(2))
'Clear data in sheets
Sh2.Cells.Clear
Sh2.Range("B4") = "start"
Sh3.Cells.Clear
Sh3.Range("B4") = "start"
'Clear autofilter
If Sh1.AutoFilterMode Then Sh1.AutoFilterMode = False
For Each R0 In R1
Select Case Trim(R0.Value)
Case Is = "Change"
Intersect(R0.EntireRow, Sh1.Range("A:F,BL:BO")).Copy Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(1, 0)
Case Is = "Early"
Intersect(R0.EntireRow, Sh1.Range("A:D,O:R,BL:BO")).Copy Sh3.Cells(Sh3.Rows.Count, 2).End(xlUp).Offset(1, 0)
End Select
Next R0
Sh2.Range("B4") = ""
Sh3.Range("B4") = ""
End Sub
May I suggest a slightly different approach :
Sub Copy_criteria()
Dim Sh1 As Worksheet: Set Sh1 = Worksheets("SHIFT LOG")
Dim Sh2 As Worksheet: Set Sh2 = Worksheets("CHANGE OF NO'S")
Dim Sh3 As Worksheet: Set Sh3 = Worksheets("ECS")
Dim R0 As Range
Dim R1 As Range: Set R1 = Intersect(Sh1.UsedRange, Sh1.Columns(2))
'Clear data in sheets
Sh2.Cells.Clear
Sh2.Range("B4") = "start"
Sh3.Cells.Clear
Sh3.Range("B4") = "start"
'Clear autofilter
If Sh1.AutoFilterMode Then Sh1.AutoFilterMode = False
For Each R0 In R1
Select Case Trim(R0.Value)
Case Is = "Change"
Intersect(R0.EntireRow, Sh1.Range("A:F,BL:BO")).Copy Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(1, 0)
Case Is = "Early"
Intersect(R0.EntireRow, Sh1.Range("A:D,O:R,BL:BO")).Copy Sh3.Cells(Sh3.Rows.Count, 2).End(xlUp).Offset(1, 0)
End Select
Next R0
Sh2.Range("B4") = ""
Sh3.Range("B4") = ""
End Sub
edited Nov 13 '18 at 5:14
answered Nov 13 '18 at 3:38
Display nameDisplay name
55416
55416
Thanks @O.PAL. How do I change the row (position) of the sheet where it gets copied too?
– JPA0888
Nov 13 '18 at 4:06
Well if you want it to start on 1st row of change/delay sheets, you'll have to delete that 1st blank row at the end of the macro :Sh2.range("A1").entirerow.delete
andSh3.range("A1").entirerow.delete
. If you want to start at any other row, you can addSh2.range("A?")="-"
where?
is the row you want minus one, same for Sh3.
– Display name
Nov 13 '18 at 4:15
It will always be row 5 Column B.
– JPA0888
Nov 13 '18 at 4:21
Brilliant. Thankyou!
– JPA0888
Nov 13 '18 at 4:34
Do I have to edit this code to get every row related toChange
orDelay
. It doesnt seem to be copying everything over?
– JPA0888
Nov 13 '18 at 4:47
|
show 2 more comments
Thanks @O.PAL. How do I change the row (position) of the sheet where it gets copied too?
– JPA0888
Nov 13 '18 at 4:06
Well if you want it to start on 1st row of change/delay sheets, you'll have to delete that 1st blank row at the end of the macro :Sh2.range("A1").entirerow.delete
andSh3.range("A1").entirerow.delete
. If you want to start at any other row, you can addSh2.range("A?")="-"
where?
is the row you want minus one, same for Sh3.
– Display name
Nov 13 '18 at 4:15
It will always be row 5 Column B.
– JPA0888
Nov 13 '18 at 4:21
Brilliant. Thankyou!
– JPA0888
Nov 13 '18 at 4:34
Do I have to edit this code to get every row related toChange
orDelay
. It doesnt seem to be copying everything over?
– JPA0888
Nov 13 '18 at 4:47
Thanks @O.PAL. How do I change the row (position) of the sheet where it gets copied too?
– JPA0888
Nov 13 '18 at 4:06
Thanks @O.PAL. How do I change the row (position) of the sheet where it gets copied too?
– JPA0888
Nov 13 '18 at 4:06
Well if you want it to start on 1st row of change/delay sheets, you'll have to delete that 1st blank row at the end of the macro :
Sh2.range("A1").entirerow.delete
and Sh3.range("A1").entirerow.delete
. If you want to start at any other row, you can add Sh2.range("A?")="-"
where ?
is the row you want minus one, same for Sh3.– Display name
Nov 13 '18 at 4:15
Well if you want it to start on 1st row of change/delay sheets, you'll have to delete that 1st blank row at the end of the macro :
Sh2.range("A1").entirerow.delete
and Sh3.range("A1").entirerow.delete
. If you want to start at any other row, you can add Sh2.range("A?")="-"
where ?
is the row you want minus one, same for Sh3.– Display name
Nov 13 '18 at 4:15
It will always be row 5 Column B.
– JPA0888
Nov 13 '18 at 4:21
It will always be row 5 Column B.
– JPA0888
Nov 13 '18 at 4:21
Brilliant. Thankyou!
– JPA0888
Nov 13 '18 at 4:34
Brilliant. Thankyou!
– JPA0888
Nov 13 '18 at 4:34
Do I have to edit this code to get every row related to
Change
or Delay
. It doesnt seem to be copying everything over?– JPA0888
Nov 13 '18 at 4:47
Do I have to edit this code to get every row related to
Change
or Delay
. It doesnt seem to be copying everything over?– JPA0888
Nov 13 '18 at 4:47
|
show 2 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53273202%2fcopy-different-rows-based-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
See edit, everything is working fine on my test file.
– Display name
Nov 13 '18 at 5:18
Thanks for your help @O.PAL, but this doesnt copy 'all' rows. The macro isnt triggered either.
– JPA0888
Nov 13 '18 at 6:30