CODEWITHSUNDEEP
vbaexcel
MasterBlaster
MasterBlaster

Reputation: 11

copy paste one single value multiple times to another sheet

I have a Column "ID" starting from Column C3 in sheet1 where all the id's are listed with an interval of 4 blank cells between each unique id's, as shown below

enter image description here

I want to copy the id's and paste it to Sheet2 five times as shown below

enter image description here

Can anyone sugggest how can i get this automated with the help of VBA

Upvotes: 0

Views: 1023

Answers (2)

R.Katnaan
R.Katnaan

Reputation: 2526

Try this one:

Sub copyCell()

    Dim idColumn As String
    Dim startRow, lastRow, row As Integer

    Dim sht1, sht2 As Worksheet

    Set sht1 = Worksheets("Sheet1") 'Setting sheet1
    Set sht2 = Worksheets("Sheet2") 'Setting sheet1

    idColumn = "C" 'Setting column id value column

    startRow = 1 'Setting the start row from sheet 1

    'Getting the last used row from sheet 1
    lastRow = sht1.Cells(sht1.Rows.Count, rowColumn).End(xlUp).row

    For row = startRow To lastRow Step 1

        'Check Id Value is empty or not
        If sht1.Range(idColumn & row) <> "" Then
            'If not empty, set value
            sht2.Range(idColumn & row) = sht1.Range(idColumn & row)
        Else
            'If empty, set value from previous row of sheet 2
            sht2.Range(idColumn & row) = sht2.Range(idColumn & (row - 1))
        End If

    Next row

End Sub

I already tested my code with your sample data. It works well for me.

I believe that will be helpful for you.

Upvotes: 0

user4039065
user4039065

Reputation:

Put this formula in Sheet2!B2,

=vlookup("zzzz", sheet1!C$3:C3, 1)

Fill down as necessary.

Upvotes: 1

Related Questions