Fixing Macro for FilteringExcel VBA to determine last non-value (IE may have a formula but no value) row in columnExcel 2010 Importing Data into first blank cellInterior color of cells up to the last cell with data in a columnConditional copy Excel File-2 data to excel file-1?copy data from only 3 sheets and paste it in new sheet - ExcelShow only selected table column after filter to new worksheetsExcel stops responding while executing loopTake the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a loopCopy non-adjacent cells and paste transpose but not to an entire rowVBA not working (copy data from one file and paste to different workbook below last row of data)
Why is the 'in' operator throwing an error with a string literal instead of logging false?
Etiquette around loan refinance - decision is going to cost first broker a lot of money
Can I use a neutral wire from another outlet to repair a broken neutral?
intersection of two sorted vectors in C++
Is it possible to download Internet Explorer on my Mac running OS X El Capitan?
How much of data wrangling is a data scientist's job?
What is the intuition behind short exact sequences of groups; in particular, what is the intuition behind group extensions?
How can I make my BBEG immortal short of making them a Lich or Vampire?
A reference to a well-known characterization of scattered compact spaces
What does it mean to describe someone as a butt steak?
Fully-Firstable Anagram Sets
Has there ever been an airliner design involving reducing generator load by installing solar panels?
Western buddy movie with a supernatural twist where a woman turns into an eagle at the end
Can one be a co-translator of a book, if he does not know the language that the book is translated into?
What exploit are these user agents trying to use?
How could indestructible materials be used in power generation?
1960's book about a plague that kills all white people
Arrow those variables!
Why "Having chlorophyll without photosynthesis is actually very dangerous" and "like living with a bomb"?
How to model explosives?
Does a druid starting with a bow start with no arrows?
Will google still index a page if I use a $_SESSION variable?
Python: return float 1.0 as int 1 but float 1.5 as float 1.5
How do I write bicross product symbols in latex?
Fixing Macro for Filtering
Excel VBA to determine last non-value (IE may have a formula but no value) row in columnExcel 2010 Importing Data into first blank cellInterior color of cells up to the last cell with data in a columnConditional copy Excel File-2 data to excel file-1?copy data from only 3 sheets and paste it in new sheet - ExcelShow only selected table column after filter to new worksheetsExcel stops responding while executing loopTake the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a loopCopy non-adjacent cells and paste transpose but not to an entire rowVBA not working (copy data from one file and paste to different workbook below last row of data)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have this macro to filter a worksheet but right now it is assuming the headrs are only in row 1... how do i have it assume headers are rows 1-4? (aka the filter starts on row 4)
This is basically to filter a table and save them as a PDF in one of our files
Dim TempWks As Worksheet
Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range
'change to match your worksheet name
Set wks = Worksheets("Sheet3")
Set TempWks = Worksheets.Add
wks.AutoFilterMode = False 'remove the arrows
'assumes headers only in row 1
wks.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), Unique:=True
With TempWks
Set myRng = .Range("a4", .Cells(.Rows.Count, "A").End(xlUp))
End With
With wks
For Each myCell In myRng.Cells
.UsedRange.AutoFilter Field:=1, Criteria1:=myCell.Value
Dim MyFileName As Variant
Dim MyfilePath As Variant
MyfilePath = "xxx" 'File Location
MyFileName = MyfilePath & "" & myCell.Value & ".pdf" 'File Name
ChDir _
MyfilePath
wks.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
MyFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next myCell
End With
Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True
excel vba printing
asked Mar 21 at 21:53
Michael MunozMichael Munoz
64
add a comment |
I have this macro to filter a worksheet but right now it is assuming the headrs are only in row 1... how do i have it assume headers are rows 1-4? (aka the filter starts on row 4)
This is basically to filter a table and save them as a PDF in one of our files
Dim TempWks As Worksheet
Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range
'change to match your worksheet name
Set wks = Worksheets("Sheet3")
Set TempWks = Worksheets.Add
wks.AutoFilterMode = False 'remove the arrows
'assumes headers only in row 1
wks.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), Unique:=True
With TempWks
Set myRng = .Range("a4", .Cells(.Rows.Count, "A").End(xlUp))
End With
With wks
For Each myCell In myRng.Cells
.UsedRange.AutoFilter Field:=1, Criteria1:=myCell.Value
Dim MyFileName As Variant
Dim MyfilePath As Variant
MyfilePath = "xxx" 'File Location
MyFileName = MyfilePath & "" & myCell.Value & ".pdf" 'File Name
ChDir _
MyfilePath
wks.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
MyFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next myCell
End With
Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True
excel vba printing
asked Mar 21 at 21:53
Michael MunozMichael Munoz
64
1
Perhaps the cause was the use of.UsedRange. If it's always from 4th row, then try replace the.UsedRange.AutoFilter...withIntersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...
– PatricK
Mar 21 at 23:51
This worked :) thank you so much
– Michael Munoz
Mar 22 at 0:10
add a comment |
I have this macro to filter a worksheet but right now it is assuming the headrs are only in row 1... how do i have it assume headers are rows 1-4? (aka the filter starts on row 4)
This is basically to filter a table and save them as a PDF in one of our files
Dim TempWks As Worksheet
Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range
'change to match your worksheet name
Set wks = Worksheets("Sheet3")
Set TempWks = Worksheets.Add
wks.AutoFilterMode = False 'remove the arrows
'assumes headers only in row 1
wks.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), Unique:=True
With TempWks
Set myRng = .Range("a4", .Cells(.Rows.Count, "A").End(xlUp))
End With
With wks
For Each myCell In myRng.Cells
.UsedRange.AutoFilter Field:=1, Criteria1:=myCell.Value
Dim MyFileName As Variant
Dim MyfilePath As Variant
MyfilePath = "xxx" 'File Location
MyFileName = MyfilePath & "" & myCell.Value & ".pdf" 'File Name
ChDir _
MyfilePath
wks.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
MyFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next myCell
End With
Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True
excel vba printing
asked Mar 21 at 21:53
Michael MunozMichael Munoz
64
I have this macro to filter a worksheet but right now it is assuming the headrs are only in row 1... how do i have it assume headers are rows 1-4? (aka the filter starts on row 4)
This is basically to filter a table and save them as a PDF in one of our files
Dim TempWks As Worksheet
Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range
'change to match your worksheet name
Set wks = Worksheets("Sheet3")
Set TempWks = Worksheets.Add
wks.AutoFilterMode = False 'remove the arrows
'assumes headers only in row 1
wks.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), Unique:=True
With TempWks
Set myRng = .Range("a4", .Cells(.Rows.Count, "A").End(xlUp))
End With
With wks
For Each myCell In myRng.Cells
.UsedRange.AutoFilter Field:=1, Criteria1:=myCell.Value
Dim MyFileName As Variant
Dim MyfilePath As Variant
MyfilePath = "xxx" 'File Location
MyFileName = MyfilePath & "" & myCell.Value & ".pdf" 'File Name
ChDir _
MyfilePath
wks.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
MyFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next myCell
End With
Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True
excel vba printing
excel vba printing
asked Mar 21 at 21:53
Michael MunozMichael Munoz
64
asked Mar 21 at 21:53
Michael MunozMichael Munoz
64
edited Mar 21 at 23:04
Michael Munoz
asked Mar 21 at 21:53
Michael MunozMichael Munoz
64
asked Mar 21 at 21:53
Michael MunozMichael Munoz
64
asked Mar 21 at 21:53
Michael MunozMichael Munoz
64
64
1
Perhaps the cause was the use of.UsedRange. If it's always from 4th row, then try replace the.UsedRange.AutoFilter...withIntersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...
– PatricK
Mar 21 at 23:51
This worked :) thank you so much
– Michael Munoz
Mar 22 at 0:10
add a comment |
1
Perhaps the cause was the use of.UsedRange. If it's always from 4th row, then try replace the.UsedRange.AutoFilter...withIntersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...
– PatricK
Mar 21 at 23:51
This worked :) thank you so much
– Michael Munoz
Mar 22 at 0:10
1
1
Perhaps the cause was the use of
.UsedRange. If it's always from 4th row, then try replace the .UsedRange.AutoFilter... with Intersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...– PatricK
Mar 21 at 23:51
Perhaps the cause was the use of
.UsedRange. If it's always from 4th row, then try replace the .UsedRange.AutoFilter... with Intersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...– PatricK
Mar 21 at 23:51
This worked :) thank you so much
– Michael Munoz
Mar 22 at 0:10
This worked :) thank you so much
– Michael Munoz
Mar 22 at 0:10
add a comment |
2 Answers
2
active
oldest
votes
You can do something like this:
With wks.Range(wks.Cells(4,1), wks.Cells(rows.Count, 1).End(xlUp))
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), Unique:=True
End With
answered Mar 21 at 23:44
Tim WilliamsTim Williams
89.3k97087
It was erroring out lower on the macro. Patrick's fix was able to help :) Thanks for the input
– Michael Munoz
Mar 22 at 0:10
add a comment |
Perhaps the cause was the use of .UsedRange. If it's always from 4th row, then try replace the .UsedRange.AutoFilter... with Intersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...
answered Mar 22 at 0:11
Michael MunozMichael Munoz
64
Hi Michael you are not supposed to write this comment like an answer. Can you delete it and write as an comment?
– Makah
Mar 23 at 16:22
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%2f55289817%2ffixing-macro-for-filtering%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
You can do something like this:
With wks.Range(wks.Cells(4,1), wks.Cells(rows.Count, 1).End(xlUp))
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), Unique:=True
End With
answered Mar 21 at 23:44
Tim WilliamsTim Williams
89.3k97087
It was erroring out lower on the macro. Patrick's fix was able to help :) Thanks for the input
– Michael Munoz
Mar 22 at 0:10
add a comment |
You can do something like this:
With wks.Range(wks.Cells(4,1), wks.Cells(rows.Count, 1).End(xlUp))
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), Unique:=True
End With
answered Mar 21 at 23:44
Tim WilliamsTim Williams
89.3k97087
It was erroring out lower on the macro. Patrick's fix was able to help :) Thanks for the input
– Michael Munoz
Mar 22 at 0:10
add a comment |
You can do something like this:
With wks.Range(wks.Cells(4,1), wks.Cells(rows.Count, 1).End(xlUp))
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), Unique:=True
End With
answered Mar 21 at 23:44
Tim WilliamsTim Williams
89.3k97087
You can do something like this:
With wks.Range(wks.Cells(4,1), wks.Cells(rows.Count, 1).End(xlUp))
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), Unique:=True
End With
answered Mar 21 at 23:44
Tim WilliamsTim Williams
89.3k97087
answered Mar 21 at 23:44
Tim WilliamsTim Williams
89.3k97087
answered Mar 21 at 23:44
Tim WilliamsTim Williams
89.3k97087
answered Mar 21 at 23:44
Tim WilliamsTim Williams
89.3k97087
89.3k97087
It was erroring out lower on the macro. Patrick's fix was able to help :) Thanks for the input
– Michael Munoz
Mar 22 at 0:10
add a comment |
It was erroring out lower on the macro. Patrick's fix was able to help :) Thanks for the input
– Michael Munoz
Mar 22 at 0:10
It was erroring out lower on the macro. Patrick's fix was able to help :) Thanks for the input
– Michael Munoz
Mar 22 at 0:10
It was erroring out lower on the macro. Patrick's fix was able to help :) Thanks for the input
– Michael Munoz
Mar 22 at 0:10
add a comment |
Perhaps the cause was the use of .UsedRange. If it's always from 4th row, then try replace the .UsedRange.AutoFilter... with Intersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...
answered Mar 22 at 0:11
Michael MunozMichael Munoz
64
Hi Michael you are not supposed to write this comment like an answer. Can you delete it and write as an comment?
– Makah
Mar 23 at 16:22
add a comment |
Perhaps the cause was the use of .UsedRange. If it's always from 4th row, then try replace the .UsedRange.AutoFilter... with Intersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...
answered Mar 22 at 0:11
Michael MunozMichael Munoz
64
Hi Michael you are not supposed to write this comment like an answer. Can you delete it and write as an comment?
– Makah
Mar 23 at 16:22
add a comment |
Perhaps the cause was the use of .UsedRange. If it's always from 4th row, then try replace the .UsedRange.AutoFilter... with Intersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...
answered Mar 22 at 0:11
Michael MunozMichael Munoz
64
Perhaps the cause was the use of .UsedRange. If it's always from 4th row, then try replace the .UsedRange.AutoFilter... with Intersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...
answered Mar 22 at 0:11
Michael MunozMichael Munoz
64
answered Mar 22 at 0:11
Michael MunozMichael Munoz
64
answered Mar 22 at 0:11
Michael MunozMichael Munoz
64
answered Mar 22 at 0:11
Michael MunozMichael Munoz
64
64
Hi Michael you are not supposed to write this comment like an answer. Can you delete it and write as an comment?
– Makah
Mar 23 at 16:22
add a comment |
Hi Michael you are not supposed to write this comment like an answer. Can you delete it and write as an comment?
– Makah
Mar 23 at 16:22
Hi Michael you are not supposed to write this comment like an answer. Can you delete it and write as an comment?
– Makah
Mar 23 at 16:22
Hi Michael you are not supposed to write this comment like an answer. Can you delete it and write as an comment?
– Makah
Mar 23 at 16:22
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%2f55289817%2ffixing-macro-for-filtering%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
1
Perhaps the cause was the use of
.UsedRange. If it's always from 4th row, then try replace the.UsedRange.AutoFilter...withIntersect(.UsedRange, .UsedRange.Offset(4,0)).AutoFilter...– PatricK
Mar 21 at 23:51
This worked :) thank you so much
– Michael Munoz
Mar 22 at 0:10