PDA

View Full Version : Automate copy,clear,paste in VBA



allthingstec
06-07-2018, 02:09 PM
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