VBA Copy Clear Paste

[allthingstec]

Hi, the code below that I am using(which someone else created) as you may see is being used to clear data on sheet 2 and paste data from sheet 1.
I am a Facilities Manager who has been tasked with creating a simple but effective bed management system. I would like to change this code so that it only clears specific cells that
are associated with a specified bed number. Please forgive if I'm not articulating this correctly. If one of my clients vacates a bed and I input the new clients info for that bed, then Auto/update the client Bed Roster. Please see my very amateur workbook that I have pieced together from various codes.
I would greatly appreciate any assistance.

Sub copypastecolumndata()
Sheet2.Select
Sheet2.Cells.ClearContents
Range("a1").Value = "Client Name"
Range("B1").Value = "Cares ID"
Range("c1").Value = "Bed No"


Sheet1.Select
Dim Client_Name As String
Dim Cares_ID As String
Dim Bed_No As String
Dim lastrow As Long


lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 2) = "9999" And Cells(i, 3) >= 50 Then
Client_Name = Sheet1.Cells(i, 1)
Cares_ID = Sheet1.Cells(i, 2)
Bed_No = Sheet1.Cells(i, 3)
Sheet2.Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet2.Cells(erow, 1) = Client_Name
Sheet2.Cells(erow, 2) = Cares_ID
Sheet2.Cells(erow, 3) = Bed_No
Range("A:C").Columns.AutoFit
Sheet1.Activate
End If
Next i
End Sub

[Paul_Hossler]

1. I added CODE tags around your macro -- you can use the [#] icon on the tool bar to insert them and just paste the macro, etc. between them

2. Did you attach your "very amateur workbook that I have pieced together from various codes"?

Click Go Advanced lower right and then the paper clip icon to attach a workbook

[allthingstec]

I will upload what I have right now, thank you so much...but as I look further, the code I was trying to use is totally the wrong thing. I will follow your lead. although a vba newbie, I have a very quick understanding. I'll try and make this as painless as possible for you. thanks again

[allthingstec]

I will upload what I have right now, thank you so much...but as I look further, the code I was trying to use is totally the wrong thing. I will follow your lead. although a vba newbie, I have a very quick understanding. I'll try and make this as painless as possible for you. thanks again

[allthingstec]

Managing 143 Bed Facility

[Paul_Hossler]

I looked at the spreadsheet

Can you describe exactly what you want to do, especially referencing specific cells on the spreadsheet?


I.e.,

1. Run a macro to input a bed number

2. On Sheet1 clear data in columns A-F for that bed number in Col A

3. Copy Cols A-D from Sheet2 for that bed number, and copy to col A-D on Sheet1

[allthingstec]

Sorry for delayed response, meetings all day..So, yes please..let's use fictitious names, that was my grave mistake...so let me give you the totality of my project..
1."Report" will be the sheet to enter the client's name and have VLOOKUP (or whatever you suggest) populate client's name, ID#, and Bed#. still on "Report" the drop-down on Col. "I" will allow what action was taken with client. So "Report" is adding a client to available bed, or removing client out of bed.
2. Based on that drop down selection, client is removed from the bed roster("Roster Data") or automatically added to it with the exact bed assignment given to that client name and ID.
3. Any client name that is cleared and replaced for that bed assignment, should not be deleted but moved at end of list or saved somewhere for future reference/use-because the recidivism rate is very high and that "cleared info will be needed again and again.
4. In short..I guess I need the bed numbers to remain static, while the name, ID assigned to that static bed is in constant state of change and have it auto update bed roster.
To say thank you for your help would be a great understatement.

[SamT]

allthingstec,

You do know that the reward for a job well done is a harder job?

Right now it's just managing the Bed assignments. Next thing it will be, "Oh, we need to manage Client's special needs," Then it will be Med schedules, then permitted visitors, then... Well, you get the idea.

If you agree with my prediction I suggest that you start as you mean to go on. I would handle all Data Entry and Data Display with UserForms. I would, (ATT,) have only three Sheets in the Workbook, A Splash(or Billboard) sheet, a Beds Sheet and a Clients sheet. Only the Splash sheet would not be hidden. It could have a nice background image, and would have large buttons to call various Data Entry and Data Display UserForms.

I would Structure the Client Data Sheet like this

Client ID	Bed Number	Name, Last	Name, First	Other Data	More Data
2017.6.8.1	1	Smith	Tom	Rocket	Scientist

And the Bed Data Sheet like this

Bed_Number	Client_ID	Staff_In_Charge	Special Needs	More Data	Whatever Data
1	2017.6.8.1	Suzy Q	Yes

You can always add Data Columns to any data base Worksheet. It would not affect any existing code... If you code properly, always aware that you will be adding to the Project in the future.

If it is a Corporate requirement that there is a Sheet with all the Bed numbers and Client Names always available to Staff, then just "Drive" that sheet from the code in the Data Entry UserForm(s)

Note that every future Data Sheet should have the same first two columns as those two. They are the Primary and Secondary keys and are used to look up any and all data entered about any Bed and any Client. Which column is first depends on if the sheet's Data is about a Client or a bed.

A note about Code... There is, in every group, some clown who thinks they know best and will want to change the worksheet Names (on the Tabs.) If your code references Tab Names, such a change will break the code. OTOH, if your code references Sheet CodeNames, the tab name is not relevant. In VBA, press Ctrl+R and F4 to figure that out.

The differences in code are
Tab Names ("Bed Assignments")

Sheets("Bed Assignments").Range("a1")

CodeNames ("BedData")

BedData.Range("A1")

[SamT]

Yes. You can Drive the Display sheet with code in just the two sheets I mentioned above. In fact that will not hinder you in any way from developing UserForms in the future... As Long as you stick to the recommended sheet Structure of always having Primary and Secondary Key Columns.

Another thing, break your DataPoints into as small of bits as possible. For example: With over 200 beds, I guessing that you have several Floors. Don't rely on room numbers to determine which floor a room is on, Add a Column for Floor number. You have 64K Columns on each sheet. Don't be stingy with them. Such stinginess makes for difficult coding. Better to have a column of never used data than a column that must be deciphered in code.