Copy visible autofiltered data -- VBA

Aznflipfoo

New Member
Joined
Nov 5, 2016
Messages
1
Hey guys so I'm pretty beginner at VBA and have been working on making a macro with VBA to extract some data from a different workbook than the current one that starts the macro.

So right now this is the code that I have:

Code:
Sub Macro8()
 Dim fd As FileDialog
 Dim OpenOrder As String
 Dim MacroBook As String
 Dim LR As Long
 MacroBook = ActiveWorkbook.Name
 


 Set fd = Application.FileDialog(msoFileDialogOpen)
 fd.AllowMultiSelect = False
 fd.Show
 Workbooks.Open fd.SelectedItems(1)
 OpenOrder = ActiveWorkbook.Name
 
    ActiveSheet.Range("$A$1:$X$33646").AutoFilter Field:=2, Criteria1:= _
        "6157 - Las Vegas Premium North"


 LR = ActiveSheet.Range("L" & Rows.Count).End(xlUp).Row
 Range("L" & LR).SpecialCells(xlCellTypeVisible).Select


 ' Not sure how to copy the above selection to the original workbook (MacroBook)




 Windows(OpenOrder).Close SaveChanges:=False
End Sub

A little bit of info on the workbook I'm pulling data from. The workbook is updated every week and can vary in amount of rows used, so the code has to account for this.

Essentially the two problems I've gotten are this:

  1. When using the find last row method, and then selecting an entire visible column, sometimes the selection gets cut off at about 30k rows and nothing below that gets selected. Not entirely sure why.
  2. The most important part of this code that I'm having trouble with, is actually selecting or copy/pasting the result of a column (only visible cells) and not having the entire used worksheet being selected/copied.

I've been working on this for a couple of weeks tweaking my code to get to this point (i know it's sorry, but this is hard stuff for me), and I've been stuck at these two points for a while. Keep in mind I might have a hard time understanding some of your code/replies if it's too complex for me, but I will try to work through it as I have taken some CS courses before.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,122,891
Messages
5,598,680
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top