VBA - Select a cell in a certain column based on fill color and paste data from a different sheet with loop

[acook1312]

Hi Everyone,

I'm creating a spreadsheet to help track work projects. I'm struggling to create a Macro to paste a new project template in the correct location every time.

Here's what I'm hoping to do:

1. Go to "Template" tab
2. Copy cells B7 through U18
3. Go to "P# Projects" tab
4. Paste copied data to the first grey cell in column B after the last project (In the image this would be B28)
5. I'd like a prompt that asks "Number of new parts" where I can enter a number and the process repeats that many times (IE if I have 5 new parts, I get five new templates pasted

Step 4 and 5 are what I'm struggling with. Any assistance would be appreciated.

Thanks

https://www.mrexcel.com/board/attachments/project-tracker-png.78935/?hash=042ffe1ff4ba7c9e7718b8671aa72dd4

https://www.mrexcel.com/board/attachments/project-tracker-template-png.78936/?hash=042ffe1ff4ba7c9e7718b8671aa72dd4

 

[Sparcot]

Hello. Enjoy:

Sub test()
    Dim lastRow As Long
    Dim enterText As String
    Dim numberOfIterations As Integer
    Application.ScreenUpdating = False
    enterText = InputBox("Number of new parts", "Enter a number", "Enter a number here")
    On Error Resume Next
    numberOfIterations = CInt(enterText)
    If IsNumeric(numberOfIterations) = True And numberOfIterations > 0 Then
        ActiveWorkbook.Worksheets("Template").Activate
        ActiveWorkbook.Worksheets("Template").Range(Cells(7, 2), Cells(18, 21)).Select
        Selection.Copy
        ActiveWorkbook.Worksheets("P# Projects").Activate
        For i = 1 To numberOfIterations
            lastRow = Range("C:C").Find("WOR/RTS", after:=Cells(1, 3), searchdirection:=xlPrevious).Row
            ActiveWorkbook.Worksheets("P# Projects").Cells(lastRow + 2, 2).Select
            ActiveSheet.Paste
        Next i
    Else
        MsgBox "You didn't input a number or your number is <1!", vbCritical
        Application.ScreenUpdating = True
        Exit Sub
    End If
    Application.ScreenUpdating = True
End Sub

 

[acook1312]

Hello. Enjoy:

Sub test()
    Dim lastRow As Long
    Dim enterText As String
    Dim numberOfIterations As Integer
    Application.ScreenUpdating = False
    enterText = InputBox("Number of new parts", "Enter a number", "Enter a number here")
    On Error Resume Next
    numberOfIterations = CInt(enterText)
    If IsNumeric(numberOfIterations) = True And numberOfIterations > 0 Then
        ActiveWorkbook.Worksheets("Template").Activate
        ActiveWorkbook.Worksheets("Template").Range(Cells(7, 2), Cells(18, 21)).Select
        Selection.Copy
        ActiveWorkbook.Worksheets("P# Projects").Activate
        For i = 1 To numberOfIterations
            lastRow = Range("C:C").Find("WOR/RTS", after:=Cells(1, 3), searchdirection:=xlPrevious).Row
            ActiveWorkbook.Worksheets("P# Projects").Cells(lastRow + 2, 2).Select
            ActiveSheet.Paste
        Next i
    Else
        MsgBox "You didn't input a number or your number is <1!", vbCritical
        Application.ScreenUpdating = True
        Exit Sub
    End If
    Application.ScreenUpdating = True
End Sub

This worked perfectly, thank you!

Here's another question I have on the same document if you think you could help here too!

Sort & Filter Help

Hi Everyone, I'm creating a spreadsheet to help track work projects. Here's what I'm hoping to do: 1. Be able to sort by "Project Number" while keeping all the cells in white with it's grey header row. In the image attached, P10001 is out of order. I'd like for this and all the white cells...

www.mrexcel.com