Consulting

Results 1 to 1 of 1

Thread: Automate copy,clear,paste in VBA

  1. #1

    Automate copy,clear,paste in VBA

    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
    Attached Files Attached Files
    Last edited by Aussiebear; 04-13-2023 at 01:55 PM. Reason: Adjusted the code tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •