3

I have a macro that pastes a number of rows from a "Template" sheet into the next blank row on the active sheet.

In column 2 of the first row is the value "variable". In Column 6 of the first row is a 3 digit number.

What I am wanting to do is increment the number in Column 6 by 1 when it is pasted. If there is no previous number on the active sheet, then it starts with 001.

As the sheet has other rows that don't contain numbers, and the rows with numbers are not at regular intervals, I am thinking the cell to increment needs to be determined in the following way (unless there is an easier logic) :

  • In Active Sheet, find last row in Column 2 that has value "variable". Offset Column by 4, to get to cell in Column 6.

  • Take active cell value and increment by 1 in the pasted rows, using the same criteria as above to determine which cell.

  • If there is no previous value of "variable" in Column 2 then value=001.

Here is the code I use to paste below into the next blank row.

Sub Paste_New_Product_from_Template()
  Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet

  Set copySheet = Worksheets("Template")
  Set pasteSheet = ActiveSheet

  copySheet.Range("2:17").Copy
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).OFFSET(1, 0).PasteSpecial xlPasteAll
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

How could I incorporate the incrementing of numbers mentioned above?

EDIT

This is a sample of what the rows would look like on the Template sheet enter image description here

And this is what the rows look like on Sheet1

enter image description here

Improve this question
6
  • It is prettry simple actually... You want the number increment in Col 6 only? Also for a better undertsanding, if the initial number is 5 and 1. The output sheet had no data then the numbering starts from 6? 2. The output sheet has data and the new data was pasted to say row 10, then the numbering starts from 6 from row 10 or row 1 in the new sheet? Commented May 25, 2021 at 5:26
  • Yes only incrementing Row 6. If no data in sheet then numbering starts from 001. Each sheet has independent numbering. If sheet has data then numbering starts from pasted row e.g. row 10. Commented May 25, 2021 at 5:31
  • Can I see how your sample data looks like just first 3 rows. Commented May 25, 2021 at 7:02
  • Have just edited question with images. Commented May 25, 2021 at 7:18
  • In column 2 of the first row is the value "variable". Aha. this should be In row 2, second column is the value "variable". Commented May 25, 2021 at 7:20

1 Answer 1

Reset to default
3

Yes only incrementing Row 6. If no data in sheet then numbering starts from 001. Each sheet has independent numbering. If sheet has data then numbering starts from pasted row e.g. row 10. – aye cee

Let's say our sample data looks like this

enter image description here

LOGIC:

  1. Set your input/output sheets.
  2. Find the last cell to write to in the output sheet. Have to check if there is data previously or not.
  3. If there is no data then copy across the header row.
  4. Copy the range.
  5. Ascertain the next number to be written in column 6.
  6. Enter the number in the relevant cell in column 6 of the copied data and apply the 000 format.

CODE:

Is this what you are trying? I have commented the code so you should not have a problem understanding it but if you do them simply ask :)

Option Explicit

Sub Paste_New_Product_from_Template()
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim LRow As Long, i As Long
    Dim StartNumber As Long
    Dim varString As String
    
    '~~> This is your input sheet
    Set copySheet = Worksheets("Template")
    '~~> Variable
    varString = copySheet.Cells(2, 2).Value2
    
    '~~> Change this to the relevant sheet
    Set pasteSheet = Sheet2
    
    '~~> Initialize the start number
    StartNumber = 1
    
    With pasteSheet
        '~~> Find the last cell to write to
        If Application.WorksheetFunction.CountA(.Cells) = 0 Then
            '~~> Copy header row
            copySheet.Rows(1).Copy .Rows(1)
            LRow = 2
        Else
            LRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            
            '~~> Find the previous number
            For i = LRow To 1 Step -1
                If .Cells(i, 2).Value2 = varString Then
                    StartNumber = .Cells(i, 6).Value2 + 1
                    Exit For
                End If
            Next i
        End If
        
        '~~> Copy the range
        copySheet.Range("2:17").Copy .Rows(LRow)
        
        '~~> Set the start number
        .Cells(LRow, 6).Value = StartNumber
        '~~> Format the number
        .Cells(LRow, 6).NumberFormat = "000"
    End With
End Sub

IN ACTION

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

15 Comments

I am getting a runtime error 13 type mismatch on this line StartNumber = copySheet.Cells(1, 6).Value2 + 1. Just to Clarify the incrementing is not on each line. It is only on each line that also has the value "variable" in column 2. Also in Empty Sheet first number will be 001, in non empty sheet the first number will increment the previous.
I have updated the above post. You may have to refresh the page to see it.
This works almost perfectly, except the first number is 000 instead of 001. I tried changing the 000 in the code to 001 but then it increments incorrectly to 011.
Sorry i do nto understand. The numbering will start from 001 see the screenshot. Do you want to start with 000?
Works extremely well. Thank you so much for the help and explanations!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.