Copying select cells from one sheet to another based on criteria

[dave5770]

Building

Apt

Type

Name1

Name2

Rent

Util

Park

Pet

Total

M/I

Exp.

Last increase Date

Last Increase Amount

Next increase Date

Next increase Amount

New Base Rent Renew

New Total Rent Renew

Next increase Amount MTM

New Base Rent MTM

New Total Rent MTM

Building A

101

Apt

John Miller

1395

100

100

35

1630

1/13/17

12/31/19

2/1/18

40

5/1/19

40

1,435

1,670

80

1,475

1,710

<tbody>
</tbody>

I need data from select cells above to copy automatically to another sheet (below) if the Next Increase Date is not blank.

Building	Apt	Name 1	Name 2	New Base Rent Renew	New Total Rent Renew	New Base Rent MTM	New Total Rent MTM
Building A	101	John Miller		$1,435	$1,670	$1,475	$1,710

<tbody>
</tbody>

Rent Increase list (above)

Hello, any help will be appreciated. I have never used VBA outside of a one day class.

I have a list of tenants and need to have select information populate from the tenant list to the rent increase sheet if the next increase date (column W) is not blank in the tenant list (there is a date listed). The data from that row in the tenant list that is designated in the rent increases sheet would be auto-populated so I can use for mail merge letters in Word and make it more automated and less prone to cutting and pasting errors.

This is an abbreviated table and there are more cells that need to copy but I wanted to keep it simple hoping I can adjust as needed once I understand the method.

 

[ClaireS]

Do you even need VBA? You can do this using an Advanced Filter (then perhaps record yourself running it in order to get the code). In order to extract data to another sheet, you must run the Advanced Filter FROM the sheet where the filtered data will go: you cannot extract data TO a different sheet, but you can look up data FROM a different sheet. So you will have your original table in the List Range, then on the other sheet your column headings as the Extract range and a Criteria range that will have the heading Next Increase Date (copy and paste it to be sure it's exact) and the condition >0 in the cell below it. Just re-run the filter whenever you need to update your data.

Or have a look at Get and Transform!

 

[dave5770]

Do you even need VBA? You can do this using an Advanced Filter (then perhaps record yourself running it in order to get the code). In order to extract data to another sheet, you must run the Advanced Filter FROM the sheet where the filtered data will go: you cannot extract data TO a different sheet, but you can look up data FROM a different sheet. So you will have your original table in the List Range, then on the other sheet your column headings as the Extract range and a Criteria range that will have the heading Next Increase Date (copy and paste it to be sure it's exact) and the condition >0 in the cell below it. Just re-run the filter whenever you need to update your data.

Or have a look at Get and Transform!

Hi ClaireS, Thank you for the information. I was hoping to have a way to make this automatic whenever I updated the tenant data because I want to use this same method for vacancies for each building so they will auto-populate on a separate sheet once the status is changed to "VA". With Advanced Filter it appears I need to run the filter each time and I make changes to the tenant data several times per day. Do you or anyone else know how to do this?

 

[ClaireS]

It's just a case of finding the best way for your circumstances. Perhaps Advanced Filter is too fiddly unless you code it. But Get and Transform/Power Query would work very simply. Steps:

1. Convert your data to a table and name it sensibly. I called mine tblRentals
2. Go to Get and Transform/Power Query, and select Get Data, From Table/Range
3. Your query will appear with the first two steps automatically completed: Source and Changed Type. You should name your query - I called mine Filter tblRentals.
4. Select the columns you want to KEEP (because there are fewer of them). Ctrl-click on the column headers of Building; Apt; Name 1; Name 2; Next Increase Date (you need to keep this for now so you can use it in the filter); New Base Rent Renew; New Total Rent Renew; New Base Rent MTM; New Total Rent MTM. Under "Remove columns" select "Remove other columns".
5. Click on the filter drop-down in the column header of the Last Increase Date column and select "Remove Empty". Then remove this column.
6. Finally, Close and Load to... a Table on a New worksheet.

Just Refresh the query any time you want to interrogate the original data again. You will get the new correct results if the source data has changed.
The M Code in my query looks like this:

let
    Source = Excel.CurrentWorkbook(){[Name="tblRentals"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"Apt", Int64.Type}, {"Type", type text}, {"Name1", type text}, {"Name2", type text}, {"Rent", Int64.Type}, {"Util", Int64.Type}, {"Park", Int64.Type}, {"Pet", Int64.Type}, {"Total", Int64.Type}, {"M/I", type datetime}, {"Exp.", type datetime}, {"Last increase Date", type datetime}, {"Last Increase Amount", Int64.Type}, {"Next increase Date", type datetime}, {"Next increase Amount", Int64.Type}, {"New Base Rent Renew", Int64.Type}, {"New Total Rent Renew", Int64.Type}, {"Next increase Amount MTM", Int64.Type}, {"New Base Rent MTM", Int64.Type}, {"New Total Rent MTM", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Building", "Apt", "Name1", "Name2", "Next increase Date", "New Base Rent Renew", "New Total Rent Renew", "New Base Rent MTM", "New Total Rent MTM"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Next increase Date] <> null and [Next increase Date] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Next increase Date"})
in
    #"Removed Columns"

You could just open the query editor and copy and paste it into the Query window. You could also fine-tune it by removing the fields you are about to delete from the Changed Type step. No point in Changing the Type of columns you are about to remove...

Hope this helps.

 

[dave5770]

It's just a case of finding the best way for your circumstances. Perhaps Advanced Filter is too fiddly unless you code it. But Get and Transform/Power Query would work very simply. Steps:

1. Convert your data to a table and name it sensibly. I called mine tblRentals
2. Go to Get and Transform/Power Query, and select Get Data, From Table/Range
3. Your query will appear with the first two steps automatically completed: Source and Changed Type. You should name your query - I called mine Filter tblRentals.
4. Select the columns you want to KEEP (because there are fewer of them). Ctrl-click on the column headers of Building; Apt; Name 1; Name 2; Next Increase Date (you need to keep this for now so you can use it in the filter); New Base Rent Renew; New Total Rent Renew; New Base Rent MTM; New Total Rent MTM. Under "Remove columns" select "Remove other columns".
5. Click on the filter drop-down in the column header of the Last Increase Date column and select "Remove Empty". Then remove this column.
6. Finally, Close and Load to... a Table on a New worksheet.

Just Refresh the query any time you want to interrogate the original data again. You will get the new correct results if the source data has changed.
The M Code in my query looks like this:

let
    Source = Excel.CurrentWorkbook(){[Name="tblRentals"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"Apt", Int64.Type}, {"Type", type text}, {"Name1", type text}, {"Name2", type text}, {"Rent", Int64.Type}, {"Util", Int64.Type}, {"Park", Int64.Type}, {"Pet", Int64.Type}, {"Total", Int64.Type}, {"M/I", type datetime}, {"Exp.", type datetime}, {"Last increase Date", type datetime}, {"Last Increase Amount", Int64.Type}, {"Next increase Date", type datetime}, {"Next increase Amount", Int64.Type}, {"New Base Rent Renew", Int64.Type}, {"New Total Rent Renew", Int64.Type}, {"Next increase Amount MTM", Int64.Type}, {"New Base Rent MTM", Int64.Type}, {"New Total Rent MTM", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Building", "Apt", "Name1", "Name2", "Next increase Date", "New Base Rent Renew", "New Total Rent Renew", "New Base Rent MTM", "New Total Rent MTM"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Next increase Date] <> null and [Next increase Date] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Next increase Date"})
in
    #"Removed Columns"

You could just open the query editor and copy and paste it into the Query window. You could also fine-tune it by removing the fields you are about to delete from the Changed Type step. No point in Changing the Type of columns you are about to remove...

Hope this helps.

That worked beautifully! Thank you!

 

[ClaireS]

Happy to help!