Autofill a column that changes

Justplainj

New Member
Joined
Apr 15, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Good day All,

This is my first time I am posting so I apologize if I am not expressing the problem correctly.

I have perused several posts in this forum but none of the suggested fixes worked.

Background:
I receive several workbooks that I have to (1) Filter, (2) Copy and Paste, (3) and combine just the filtered data into one workbook.
the reason for this is that the workbooks I receive has all the data in one single sheet and I separate this data based on filtered criteria.

As it is a lot of sheets I use VBA to do this. It used to take me about a day to do this manually, but with VBA code it take far less time and I also spend that time to do other work. (as I run the code on a different PC)

The Problem:
On a few of the sheets, after I, filtered, copied and pasted the data over. It requires me to (1) add a new heading at the very next open column, (2) add a text value below this new heading and (3) Autofill this text value entered below the new heading, down to the last row. This take me an additional 20 to 30 minutes due to the massive amount of sheets.

The code I use for this is.

Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":Z" & LastRow)

The above code works from the currently active cell.
In example: The new heading is inserted into cell Z1 and the text value is entered below the new heading into cell Z2. With the code I already have that adds the new heading and text value, it automatically makes Z2 the current active cell and the above code autofills cell Z2 down.

Important Note: The above code works perfectly, however, as will be noticed the code references the specific column in the autofill destination as 'Z' and the problem is this column Z constantly changes every time i get a new batch of workbooks to extract and separate data from.


Question:
How do make the part where it specifies the column ":Z" dynamic so that when the column is in example Y, it will not add the autofill to column Z or even worse if the last column is AB, it will not overwrite data in column Z.
I currently have to go into the code each time and manually update the code each time, which sometimes takes me longer than doing the autofill manually.

Thanks in advance.
J
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,758
Office Version
  1. 2016
Platform
  1. Windows
My understanding is that you want the column is the selected column, not Z.
You can easily get the column letter using this line

NewCol = Split(ActiveCell.Address, "$")(1)

Range can be defined like this also
Range(ActiveCell.Address, NewCol & LastRow)
 
Solution

Justplainj

New Member
Joined
Apr 15, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Good day Zot

Thank you very much for your quick reply.

This works Perfectly, been trying different permutations for days now :)

Thank you.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,758
Office Version
  1. 2016
Platform
  1. Windows
Good day Zot

Thank you very much for your quick reply.

This works Perfectly, been trying different permutations for days now :)

Thank you.
Glad to help
 

Forum statistics

Threads
1,143,623
Messages
5,719,801
Members
422,245
Latest member
Kebad

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