Need code for copy and paste

[Timothy King]

So I am new to VBA code. I have a program that tracks who is in what locker. What I am basically looking for is a code that will copy a row of data from worksheet "Available" if column F has the word "Closed" and then copy it to the next available line in the worksheet called "In Use Lockers". I would also like to have a code that once I change Column F from closed to open in the "In Use Lockers" it will copy and paste it back to the page "Available"

My page consist of 6 columns and 166 rows

 

[Monty]

Timothy King
Interesting question, can i have sample file to help you.

 

[Timothy King]

Sure thing. Here you go


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Monty]

Sorry could not complete the code...Still to go.

Sub test()

Dim lrow As Long
Dim i As Integer
Dim Val As String


Dim AvalSht As Worksheet
Set AvalSht = Worksheets("Available")

Dim InuseSht As Worksheet
Set InuseSht = Worksheets("In Use Lockers")

lrow = AvalSht.Range("F" & Rows.Count).End(xlUp).Row

For i = 6 To lrow
          AvalSht.Activate
    Val = AvalSht.Range("F" & i).Value
    If Val = "Closed" Then
        lrow1 = InuseSht.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        AvalSht.Range("A" & i).Resize(, 6).Copy
        InuseSht.Activate
        Range("A" & lrow1).PasteSpecial
    End If
    i = i + 1
Next i
End Sub

 

[Timothy King]

Here is the file. As you can see I have the code for the first page working. Now I just need to figure out the code so that it will copy a row from "In Use Lockers" to "Available" when Column F reads "Open"

 

[Monty]

Hello Timothy.

I think what you are looking if status is open in ("D" column ) "In Use Lockers" it should copy row to "Available" sheet ?

is that correct...Correct me if am wrong.

 

[Timothy King]

Hello Timothy.

I think what you are looking if status is open in ("D" column ) "In Use Lockers" it should copy row to "Available" sheet ?

is that correct...Correct me if am wrong.

you are right.

 

[Monty]

Try this!

 

[Timothy King]

Try this!

Thank you so much Monty for all the help....I am actually starting to understand VB

 

[Monty]

Am glad that i could help you...Please let me know any future challenges.