Justplainj
Board Regular
- Joined
- Apr 15, 2021
- Messages
- 50
- Office Version
- 365
- Platform
- 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
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
