How to get Range from A2 to End (No select statement)
I am trying to copy and paste from one workbook to another workbook, but I am getting Autofilter method of Range class failed error. I found out that this error occurs when I do not specify my end row. For example if my copying data field ends at row 500, I have to set my Range exactly (A2,AJ500). However, this data (end row) can be changed every month. It could be AJ700, AJ600, etc. Is there a way to make my range(A2, End Row) and make macro to run without Range class failed error?
Please let me know if you are not clear with my question. Thanks.
Option Explicit
Sub Macro1()
Dim wb As Workbook, wbO As Workbook
Dim ws As Worksheet, wsO As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Copyingfrom")
Set wbO = Workbooks.Add("Output.xlsm")
With wbO
Set wsO = wbO.Sheets("OutputSheet")
ActiveSheet.AutoFilterMode = False
With ws.Range("A2:AJ500") --> this is the field that I want to run as something like (A2:end)
.AutoFilter Field:=36, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).Copy
End With
wsO.Range("I3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End With
End Sub
excel vba
edited Jan 3 at 19:21
Cindy Meister
16k102437
asked Jan 3 at 18:24
EJ. KEJ. K
62
add a comment |
I am trying to copy and paste from one workbook to another workbook, but I am getting Autofilter method of Range class failed error. I found out that this error occurs when I do not specify my end row. For example if my copying data field ends at row 500, I have to set my Range exactly (A2,AJ500). However, this data (end row) can be changed every month. It could be AJ700, AJ600, etc. Is there a way to make my range(A2, End Row) and make macro to run without Range class failed error?
Please let me know if you are not clear with my question. Thanks.
Option Explicit
Sub Macro1()
Dim wb As Workbook, wbO As Workbook
Dim ws As Worksheet, wsO As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Copyingfrom")
Set wbO = Workbooks.Add("Output.xlsm")
With wbO
Set wsO = wbO.Sheets("OutputSheet")
ActiveSheet.AutoFilterMode = False
With ws.Range("A2:AJ500") --> this is the field that I want to run as something like (A2:end)
.AutoFilter Field:=36, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).Copy
End With
wsO.Range("I3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End With
End Sub
excel vba
edited Jan 3 at 19:21
Cindy Meister
16k102437
asked Jan 3 at 18:24
EJ. KEJ. K
62
add a comment |
I am trying to copy and paste from one workbook to another workbook, but I am getting Autofilter method of Range class failed error. I found out that this error occurs when I do not specify my end row. For example if my copying data field ends at row 500, I have to set my Range exactly (A2,AJ500). However, this data (end row) can be changed every month. It could be AJ700, AJ600, etc. Is there a way to make my range(A2, End Row) and make macro to run without Range class failed error?
Please let me know if you are not clear with my question. Thanks.
Option Explicit
Sub Macro1()
Dim wb As Workbook, wbO As Workbook
Dim ws As Worksheet, wsO As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Copyingfrom")
Set wbO = Workbooks.Add("Output.xlsm")
With wbO
Set wsO = wbO.Sheets("OutputSheet")
ActiveSheet.AutoFilterMode = False
With ws.Range("A2:AJ500") --> this is the field that I want to run as something like (A2:end)
.AutoFilter Field:=36, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).Copy
End With
wsO.Range("I3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End With
End Sub
excel vba
edited Jan 3 at 19:21
Cindy Meister
16k102437
asked Jan 3 at 18:24
EJ. KEJ. K
62
I am trying to copy and paste from one workbook to another workbook, but I am getting Autofilter method of Range class failed error. I found out that this error occurs when I do not specify my end row. For example if my copying data field ends at row 500, I have to set my Range exactly (A2,AJ500). However, this data (end row) can be changed every month. It could be AJ700, AJ600, etc. Is there a way to make my range(A2, End Row) and make macro to run without Range class failed error?
Please let me know if you are not clear with my question. Thanks.
Option Explicit
Sub Macro1()
Dim wb As Workbook, wbO As Workbook
Dim ws As Worksheet, wsO As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Copyingfrom")
Set wbO = Workbooks.Add("Output.xlsm")
With wbO
Set wsO = wbO.Sheets("OutputSheet")
ActiveSheet.AutoFilterMode = False
With ws.Range("A2:AJ500") --> this is the field that I want to run as something like (A2:end)
.AutoFilter Field:=36, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).Copy
End With
wsO.Range("I3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End With
End Sub
excel vba
excel vba
edited Jan 3 at 19:21
Cindy Meister
16k102437
asked Jan 3 at 18:24
EJ. KEJ. K
62
edited Jan 3 at 19:21
Cindy Meister
16k102437
asked Jan 3 at 18:24
EJ. KEJ. K
62
edited Jan 3 at 19:21
Cindy Meister
16k102437
edited Jan 3 at 19:21
Cindy Meister
16k102437
edited Jan 3 at 19:21
Cindy Meister
16k102437
16k102437
asked Jan 3 at 18:24
EJ. KEJ. K
62
asked Jan 3 at 18:24
EJ. KEJ. K
62
asked Jan 3 at 18:24
EJ. KEJ. K
62
62
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Copy to Workbook
The Code
Option Explicit
Sub CopyRange()
Dim ws As Worksheet, wsO As Worksheet
Dim lngLastRow As Long
Set ws = ThisWorkbook.Sheets("Copyingfrom")
Set wsO = Workbooks("Output.xlsm").Sheets("OutputSheet")
With ws
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A2:AJ" & lngLastRow)
.AutoFilter Field:=36, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).Copy
End With
wsO.Range("I3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.AutoFilterMode = False
End With
Application.CutCopyMode = False
End Sub
answered Jan 3 at 19:04
VBasic2008VBasic2008
3,5392517
Thank you so much. This works!
– EJ. K
Jan 7 at 14:52
add a comment |
The easiest way:
With ws.Range("A2").CurrentRegion ...
This will automatically define your range no matter which your last row is.
answered Jan 3 at 19:30
JohnyLJohnyL
3,72811025
add a comment |
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%2f54027799%2fhow-to-get-range-from-a2-to-end-no-select-statement%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
Copy to Workbook
The Code
Option Explicit
Sub CopyRange()
Dim ws As Worksheet, wsO As Worksheet
Dim lngLastRow As Long
Set ws = ThisWorkbook.Sheets("Copyingfrom")
Set wsO = Workbooks("Output.xlsm").Sheets("OutputSheet")
With ws
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A2:AJ" & lngLastRow)
.AutoFilter Field:=36, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).Copy
End With
wsO.Range("I3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.AutoFilterMode = False
End With
Application.CutCopyMode = False
End Sub
answered Jan 3 at 19:04
VBasic2008VBasic2008
3,5392517
Thank you so much. This works!
– EJ. K
Jan 7 at 14:52
add a comment |
Copy to Workbook
The Code
Option Explicit
Sub CopyRange()
Dim ws As Worksheet, wsO As Worksheet
Dim lngLastRow As Long
Set ws = ThisWorkbook.Sheets("Copyingfrom")
Set wsO = Workbooks("Output.xlsm").Sheets("OutputSheet")
With ws
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A2:AJ" & lngLastRow)
.AutoFilter Field:=36, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).Copy
End With
wsO.Range("I3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.AutoFilterMode = False
End With
Application.CutCopyMode = False
End Sub
answered Jan 3 at 19:04
VBasic2008VBasic2008
3,5392517
Thank you so much. This works!
– EJ. K
Jan 7 at 14:52
add a comment |
Copy to Workbook
The Code
Option Explicit
Sub CopyRange()
Dim ws As Worksheet, wsO As Worksheet
Dim lngLastRow As Long
Set ws = ThisWorkbook.Sheets("Copyingfrom")
Set wsO = Workbooks("Output.xlsm").Sheets("OutputSheet")
With ws
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A2:AJ" & lngLastRow)
.AutoFilter Field:=36, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).Copy
End With
wsO.Range("I3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.AutoFilterMode = False
End With
Application.CutCopyMode = False
End Sub
answered Jan 3 at 19:04
VBasic2008VBasic2008
3,5392517
Copy to Workbook
The Code
Option Explicit
Sub CopyRange()
Dim ws As Worksheet, wsO As Worksheet
Dim lngLastRow As Long
Set ws = ThisWorkbook.Sheets("Copyingfrom")
Set wsO = Workbooks("Output.xlsm").Sheets("OutputSheet")
With ws
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A2:AJ" & lngLastRow)
.AutoFilter Field:=36, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).Copy
End With
wsO.Range("I3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.AutoFilterMode = False
End With
Application.CutCopyMode = False
End Sub
answered Jan 3 at 19:04
VBasic2008VBasic2008
3,5392517
answered Jan 3 at 19:04
VBasic2008VBasic2008
3,5392517
answered Jan 3 at 19:04
VBasic2008VBasic2008
3,5392517
answered Jan 3 at 19:04
VBasic2008VBasic2008
3,5392517
3,5392517
Thank you so much. This works!
– EJ. K
Jan 7 at 14:52
add a comment |
Thank you so much. This works!
– EJ. K
Jan 7 at 14:52
Thank you so much. This works!
– EJ. K
Jan 7 at 14:52
Thank you so much. This works!
– EJ. K
Jan 7 at 14:52
add a comment |
The easiest way:
With ws.Range("A2").CurrentRegion ...
This will automatically define your range no matter which your last row is.
answered Jan 3 at 19:30
JohnyLJohnyL
3,72811025
add a comment |
The easiest way:
With ws.Range("A2").CurrentRegion ...
This will automatically define your range no matter which your last row is.
answered Jan 3 at 19:30
JohnyLJohnyL
3,72811025
add a comment |
The easiest way:
With ws.Range("A2").CurrentRegion ...
This will automatically define your range no matter which your last row is.
answered Jan 3 at 19:30
JohnyLJohnyL
3,72811025
The easiest way:
With ws.Range("A2").CurrentRegion ...
This will automatically define your range no matter which your last row is.
answered Jan 3 at 19:30
JohnyLJohnyL
3,72811025
answered Jan 3 at 19:30
JohnyLJohnyL
3,72811025
answered Jan 3 at 19:30
JohnyLJohnyL
3,72811025
answered Jan 3 at 19:30
JohnyLJohnyL
3,72811025
3,72811025
add a comment |
add a comment |
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.
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%2f54027799%2fhow-to-get-range-from-a2-to-end-no-select-statement%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
