How do I copy a specific range of cells after I use AutoFilter in VBA

0 votes

I'm having a little issue understanding how VBA's AutoFilter function operates. Code like this: Worksheets("my sheet") ("my sheet"). Range("A1"). When I try to replicate the first col using this bit of code, AutoFilter Field:=14, Criteria1:="my criterion" filters the worksheet in col 14 just how I want it to, but when I do:

Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("my sheet").Range("A2:A" & LR).Copy

Then, contrary to what I wanted, the first header of the first col (A1) was duplicated into my newly generated sheet. Except for the header, I want to duplicate everything that is filtered (A2 and down to the end of the filtered col).

In case there was a problem when the sheets were produced, I tried to isolate the functions that create the sheets into which I am copying the cols, but that did not resolve the problem. I did find the following line of code online:

Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row

But to my knowledge, that shouldn't be the issue, because my code does work if I run the macro again (twice), once the new sheets have been created and they have been formatted (just some text in cells A1 and B1 and some formatting of those cells). 

Feb 2, 2023 in Others by Kithuzzz
• 38,010 points
412 views

1 answer to this question.

0 votes

Set the range of filtered data

Set Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
answered Feb 2, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

VBA Loop to select then copy a range of cells based on value in column B

Try this: Sub Macro2() Dim ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,420 points
1,676 views
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,420 points
1,330 views
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
892 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,205 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
501 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
745 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
518 views
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
649 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP