Copying select cells from one sheet to another based on criteria

D

dave5770

New Member
Joined
Apr 9, 2019
Messages
8
[TABLE="width: 500"]
<tbody>[TR]
[TD]Building
[/TD]
[TD]Apt
[/TD]
[TD]Type
[/TD]
[TD]Name1
[/TD]
[TD]Name2
[/TD]
[TD]Rent
[/TD]
[TD]Util
[/TD]
[TD]Park
[/TD]
[TD]Pet
[/TD]
[TD]Total
[/TD]
[TD]M/I
[/TD]
[TD]Exp.
[/TD]
[TD]Last increase Date
[/TD]
[TD]Last Increase Amount
[/TD]
[TD]Next increase Date
[/TD]
[TD]Next increase Amount
[/TD]
[TD]New Base Rent Renew
[/TD]
[TD]New Total Rent Renew
[/TD]
[TD]Next increase Amount MTM
[/TD]
[TD]New Base Rent MTM
[/TD]
[TD]New Total Rent MTM
[/TD]
[/TR]
[TR]
[TD]Building A
[/TD]
[TD]101
[/TD]
[TD]Apt
[/TD]
[TD]John Miller
[/TD]
[TD][/TD]
[TD]1395
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]35
[/TD]
[TD]1630
[/TD]
[TD]1/13/17
[/TD]
[TD]12/31/19
[/TD]
[TD]2/1/18
[/TD]
[TD]40
[/TD]
[TD]5/1/19
[/TD]
[TD]40
[/TD]
[TD]1,435
[/TD]
[TD]1,670
[/TD]
[TD]80
[/TD]
[TD]1,475
[/TD]
[TD]1,710
[/TD]
[/TR]
</tbody>[/TABLE]
I need data from select cells above to copy automatically to another sheet (below) if the Next Increase Date is not blank.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Building
[/TD]
[TD]Apt
[/TD]
[TD]Name 1
[/TD]
[TD]Name 2
[/TD]
[TD]New Base Rent Renew
[/TD]
[TD]New Total Rent Renew
[/TD]
[TD]New Base Rent MTM
[/TD]
[TD]New Total Rent MTM
[/TD]
[/TR]
[TR]
[TD]Building A
[/TD]
[TD]101
[/TD]
[TD]John Miller
[/TD]
[TD][/TD]
[TD]$1,435
[/TD]
[TD]$1,670
[/TD]
[TD]$1,475
[/TD]
[TD]$1,710
[/TD]
[/TR]
</tbody>[/TABLE]
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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!
 
Upvote 0
ClaireS said:
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!
Click to expand...


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?
 
Upvote 0
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:
Code: 
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.
 
Upvote 0
ClaireS said:
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:
Code: 
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.
Click to expand...

That worked beautifully! Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Automatically move cells from one sheet to another based on criteria using VBA
Replies
5
Views
761
DanteAmor
DanteAmor
M
  • Question Question
VBA Code to copy rows by date from one tab to another / repeat based on new date
Replies
3
Views
561
kevin9999
K
M
  • Question Question
How to copy one row from the specific found cell from one sheet to another
Replies
0
Views
358
mahshadrashidpour
M
Z
  • Question Question
Copy text value from one cell to another if all criterias are met
Replies
3
Views
762
zack8576
Z
M
  • Question Question
How to get a date value from one column to a cell in front of another column based on value of a third column
Replies
0
Views
303
MuhammadZiaUrRehman
M

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

Share this page

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
Back
Top