Copy and paste a selected set of columns till the last available data

[Jacob John]

Hi all,

I am an amateur in VBA and has been working with a huge amounts of excel data which my boss gave me. I am stuck at one point and hope someone can help me further. The situation is as follows

1. The excel file contains headers and hence filters are used
2. After filtering, some of the columns are hidden
3. The range that I need to copy starts from, lets say I500 to AF(until the last filled column in AF.
4. Catch is, there are many rows that are filtered, hence after I500 the next row that I need to copy is I586 and so on.
5. From I to AF after hiding many columns are just 5 columns left to copy. So need to consider the hidden parameter as well as the filtered parameter.
6. This sheet is updated monthy and hence new data falls in.
7. Which again needs to be automatically selected and copied for another excel file to work. (Hence I asked for last available data in these columns)

Is there an easy VBA code to use. I searched google and tried many codes, but none of them seem to work as the filtering is not included.

Thanks for the help
Jacob

 

[texasalynn]

Welcome to MrExcel board....

so what needs to happen with the "filtered" data you copy? Does the copy always start at row 500? I'm not getting a clear picture of what you are wanting. Maybe you could give actuals for a "this time" scenario, that would then be different the next time based on the conditions that change.

 

[HalfAce]

I'm also unclear on exactly what you're looking to do, but here's how you can determine (and refer to) the last used cell in a column. (In this case column AF)
Dim LstRw As Long
LstRw = Cells(Rows.Count, "AF").End(xlUp).Row

And then, to refer to your range...
Range("I500:AF" & LstRw).Copy '(or .Select, .Cut, - whatever)

Hope it helps.

 

[Jacob John]

Hi, thank you. I never expected an answer so soon. So, to make it clearer. This is a document, updated by the company. Gets about 100 to 200 rows of data every month. Below you will find what I am trying to explain.

Workbooks.Open Filename:="A.xlsx", _
UpdateLinks:=0
ActiveSheet.Range("$A$2:$BJ$57003").AutoFilter Field:=16, Criteria1:= _
"=BASE", Operator:=xlOr, Criteria2:="=CENTRE"
ActiveSheet.Range("$A$2:$BJ$57003").AutoFilter Field:=17, Criteria1:= _
"=T123", Operator:=xlOr, Criteria2:="=T456"
Range("J:M,O:O,O:T").Select
Range("O1").Activate
Range("J:M,O:O,O:T,W:AE").Select
Range("W1").Activate
Selection.EntireColumn.Hidden = True
Range("I634:AF34059").Copy

So, there is a parent workbook which takes data from Sheet "A.xlsx". As shown, it, filters the data, only to "base" and "centre" also in another column, to "T123" and "T456", as there are many columns which are not required, they are hidden, Then, after this, I need to copy the data, which is found after filtered and hidden. So you see, there are 1000's of data in between which are not required and they are the ones that are filtered and hidden. I just did the basic copy and paste function then. But the last column, keeps changing and it will not be "I634 to AF34059", by next month, this will be "I634 to AF34400" Hence, if I don't have a code to handle this automatically, I will have to update the selection range every month, which doesn't solve the purpose of coding then.

Thanks you for the fast response.

Welcome to MrExcel board....

so what needs to happen with the "filtered" data you copy? Does the copy always start at row 500? I'm not getting a clear picture of what you are wanting. Maybe you could give actuals for a "this time" scenario, that would then be different the next time based on the conditions that change.

 

[Jacob John]

Hi HalfAce,

Thanks a lot, this is what I was looking for. Thank you so much, I will get back if I have more questions. I hope you wont mind.

Jacob

I'm also unclear on exactly what you're looking to do, but here's how you can determine (and refer to) the last used cell in a column. (In this case column AF)
Dim LstRw As Long
LstRw = Cells(Rows.Count, "AF").End(xlUp).Row

And then, to refer to your range...
Range("I500:AF" & LstRw).Copy '(or .Select, .Cut, - whatever)

Hope it helps.

 

[HalfAce]

I hope you wont mind.

Not even a little bit.

 

[Jacob John]

Hi Halface, have a question to that initial solution you gave me.

Workbooks.Open Filename:="A.xlsx", _
UpdateLinks:=0
ActiveSheet.Range("$A$2:$BJ$57003").AutoFilter Field:=16, Criteria1:= _
"=BASE", Operator:=xlOr, Criteria2:="=CENTRE"
ActiveSheet.Range("$A$2:$BJ$57003").AutoFilter Field:=17, Criteria1:= _
"=T123", Operator:=xlOr, Criteria2:="=T456"
Range("J:M,O:O,O:T,W:AE").Select
Selection.EntireColumn.Hidden = True
Lastrow = Cells(Rows.count, "AF").End(xlUp).row
Range("I634:AF" & Lastrow).Copy

Now, I came to know that the I634 can also change. What I mean is, after the monthy update. There might be new data above I634 and hence it should copy from the 3rd row. But when I give "I3:AF" & Lastrow. it ignores the hidden columns. But fortunately, it still takes the filter option which I gave by recording the macro. Any suggestions?

 

[HalfAce]

it ignores the hidden columns. But fortunately, it still takes the filter option which I gave by recording the macro.

I'm a little unclear on what you mean by these statements.

Below is an example that will copy only the visible columns & rows of the specified range after hiding the columns & auto filtering. (Is that what you're looking to do?)

Workbooks.Open Filename:="A.xlsx", _
UpdateLinks:=0
ActiveSheet.Range("$A$2:$BJ$57003").AutoFilter Field:=16, Criteria1:= _
"=BASE", Operator:=xlOr, Criteria2:="=CENTRE"
ActiveSheet.Range("$A$2:$BJ$57003").AutoFilter Field:=17, Criteria1:= _
"=T123", Operator:=xlOr, Criteria2:="=T456"

Range("J:M,O:T,W:AE").EntireColumn.Hidden = True

Lastrow = Cells(Rows.Count, "AF").End(xlUp).Row

'''/// Amend [B][COLOR=Blue]Sheets("Sheet2").Range("A1")[/COLOR][/B] with your real destination range.
Range("I3:AF" & Lastrow).Copy Sheets("Sheet2").Range("A1")

ActiveSheet.AutoFilterMode = False

Does that help get you any closer?
(If not then I guess I'll need a little better explanation)

 

[Jacob John]

Hey HalfAce, was on a vacation... Thanks, you solution did work. I am learning a lot from you. Wish I had your kind of knowledge on VBA. Have one question. So if I have questions related to this VBA, do I just ask you directly or I have to post a new question?