Auto-copy rows out of a table to a new worksheet
0
I am trying to auto-copy specific rows in a Table from one worksheet to a separate worksheet. Below is my code. The first half hides/unhides specific Columns based on the value entered into Column B. The second half aims to copy across specific rows when a designated value is entered into Column B.
I can get this working when the Master worksheet is a Range but not a Table. This is my attempt when formatted as a Table.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
On Error GoTo safe_exit
Application.EnableEvents = False
'****************
FilterAndCopy
'****************
Dim t As Range
For Each t In Intersect(Target, Range("B:B"))
Select Case (t.Value)
Case "Change of Numbers"
Columns("B:BP").EntireColumn.Hidden = False
Columns("H:BL").EntireColumn.Hidden = True
'do nothing
End Select
Next t
End If
safe_exit:
Application.EnableEvents = True
End Sub
'****************
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Worksheets("MASTER")
Set sht2 = Worksheets("CON")
sht2.ListObjects(1).DataBodyRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.ListObjects(1).DataBodyRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
Dim rngToCopy As Range
.AutoFilter field:=1, Criteria1:="Change of Numbers"
Set rngToCopy = Intersect(.SpecialCells(xlCellTypeVisible), sht1.Range("A:F, BL:BO"))
Debug.Print rngToCopy.Address
rngToCopy.Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("H:BK").EntireColumn.Hidden = True ' hide columns
End With
End Sub
The error is:
Subscript out of range
excel vba sorting copy
asked Nov 13 at 0:45
JPA0888
9010
add a comment |
0
I am trying to auto-copy specific rows in a Table from one worksheet to a separate worksheet. Below is my code. The first half hides/unhides specific Columns based on the value entered into Column B. The second half aims to copy across specific rows when a designated value is entered into Column B.
I can get this working when the Master worksheet is a Range but not a Table. This is my attempt when formatted as a Table.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
On Error GoTo safe_exit
Application.EnableEvents = False
'****************
FilterAndCopy
'****************
Dim t As Range
For Each t In Intersect(Target, Range("B:B"))
Select Case (t.Value)
Case "Change of Numbers"
Columns("B:BP").EntireColumn.Hidden = False
Columns("H:BL").EntireColumn.Hidden = True
'do nothing
End Select
Next t
End If
safe_exit:
Application.EnableEvents = True
End Sub
'****************
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Worksheets("MASTER")
Set sht2 = Worksheets("CON")
sht2.ListObjects(1).DataBodyRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.ListObjects(1).DataBodyRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
Dim rngToCopy As Range
.AutoFilter field:=1, Criteria1:="Change of Numbers"
Set rngToCopy = Intersect(.SpecialCells(xlCellTypeVisible), sht1.Range("A:F, BL:BO"))
Debug.Print rngToCopy.Address
rngToCopy.Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("H:BK").EntireColumn.Hidden = True ' hide columns
End With
End Sub
The error is:
Subscript out of range
excel vba sorting copy
asked Nov 13 at 0:45
JPA0888
9010
Which line has the error?
– Tim Williams
Nov 13 at 1:34
Care that each listobject has its own autofilter property so maybe add something likesht1.listobjects(1).autofilter.showalldatabefore doing your filter. Not sure thats the issue tho.
– Display name
Nov 13 at 1:43
add a comment |
0
0
0
I am trying to auto-copy specific rows in a Table from one worksheet to a separate worksheet. Below is my code. The first half hides/unhides specific Columns based on the value entered into Column B. The second half aims to copy across specific rows when a designated value is entered into Column B.
I can get this working when the Master worksheet is a Range but not a Table. This is my attempt when formatted as a Table.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
On Error GoTo safe_exit
Application.EnableEvents = False
'****************
FilterAndCopy
'****************
Dim t As Range
For Each t In Intersect(Target, Range("B:B"))
Select Case (t.Value)
Case "Change of Numbers"
Columns("B:BP").EntireColumn.Hidden = False
Columns("H:BL").EntireColumn.Hidden = True
'do nothing
End Select
Next t
End If
safe_exit:
Application.EnableEvents = True
End Sub
'****************
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Worksheets("MASTER")
Set sht2 = Worksheets("CON")
sht2.ListObjects(1).DataBodyRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.ListObjects(1).DataBodyRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
Dim rngToCopy As Range
.AutoFilter field:=1, Criteria1:="Change of Numbers"
Set rngToCopy = Intersect(.SpecialCells(xlCellTypeVisible), sht1.Range("A:F, BL:BO"))
Debug.Print rngToCopy.Address
rngToCopy.Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("H:BK").EntireColumn.Hidden = True ' hide columns
End With
End Sub
The error is:
Subscript out of range
excel vba sorting copy
asked Nov 13 at 0:45
JPA0888
9010
I am trying to auto-copy specific rows in a Table from one worksheet to a separate worksheet. Below is my code. The first half hides/unhides specific Columns based on the value entered into Column B. The second half aims to copy across specific rows when a designated value is entered into Column B.
I can get this working when the Master worksheet is a Range but not a Table. This is my attempt when formatted as a Table.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
On Error GoTo safe_exit
Application.EnableEvents = False
'****************
FilterAndCopy
'****************
Dim t As Range
For Each t In Intersect(Target, Range("B:B"))
Select Case (t.Value)
Case "Change of Numbers"
Columns("B:BP").EntireColumn.Hidden = False
Columns("H:BL").EntireColumn.Hidden = True
'do nothing
End Select
Next t
End If
safe_exit:
Application.EnableEvents = True
End Sub
'****************
Sub FilterAndCopy()
Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Worksheets("MASTER")
Set sht2 = Worksheets("CON")
sht2.ListObjects(1).DataBodyRange.ClearContents
With Intersect(sht1.Columns("B:BP"), sht1.ListObjects(1).DataBodyRange)
.Cells.EntireColumn.Hidden = False ' unhide columns
If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
Dim rngToCopy As Range
.AutoFilter field:=1, Criteria1:="Change of Numbers"
Set rngToCopy = Intersect(.SpecialCells(xlCellTypeVisible), sht1.Range("A:F, BL:BO"))
Debug.Print rngToCopy.Address
rngToCopy.Copy Destination:=sht2.Cells(4, "B")
.Parent.AutoFilterMode = False
.Range("H:BK").EntireColumn.Hidden = True ' hide columns
End With
End Sub
The error is:
Subscript out of range
excel vba sorting copy
excel vba sorting copy
asked Nov 13 at 0:45
JPA0888
9010
asked Nov 13 at 0:45
JPA0888
9010
edited Nov 13 at 1:07
asked Nov 13 at 0:45
JPA0888
9010
asked Nov 13 at 0:45
JPA0888
9010
asked Nov 13 at 0:45
JPA0888
9010
9010
Which line has the error?
– Tim Williams
Nov 13 at 1:34
Care that each listobject has its own autofilter property so maybe add something likesht1.listobjects(1).autofilter.showalldatabefore doing your filter. Not sure thats the issue tho.
– Display name
Nov 13 at 1:43
add a comment |
Which line has the error?
– Tim Williams
Nov 13 at 1:34
Care that each listobject has its own autofilter property so maybe add something likesht1.listobjects(1).autofilter.showalldatabefore doing your filter. Not sure thats the issue tho.
– Display name
Nov 13 at 1:43
Which line has the error?
– Tim Williams
Nov 13 at 1:34
Which line has the error?
– Tim Williams
Nov 13 at 1:34
Care that each listobject has its own autofilter property so maybe add something like
sht1.listobjects(1).autofilter.showalldata before doing your filter. Not sure thats the issue tho.– Display name
Nov 13 at 1:43
Care that each listobject has its own autofilter property so maybe add something like
sht1.listobjects(1).autofilter.showalldata before doing your filter. Not sure thats the issue tho.– Display name
Nov 13 at 1:43
add a comment |
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f53272180%2fauto-copy-rows-out-of-a-table-to-a-new-worksheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53272180%2fauto-copy-rows-out-of-a-table-to-a-new-worksheet%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
Which line has the error?
– Tim Williams
Nov 13 at 1:34
Care that each listobject has its own autofilter property so maybe add something like
sht1.listobjects(1).autofilter.showalldatabefore doing your filter. Not sure thats the issue tho.– Display name
Nov 13 at 1:43