VBA - Copy and Paste Multiple Times Between Excel Sheets

newbie Published at Dev

Newbie

I have a set of x names (in row 4) with corresponding dates (row 3) (the combination of name and date is unique).

I would like to copy the unique name and date, and then paste it x times (where x is the total number of names) in a different sheet.

I would like the code to loop through all names and dates and paste them within column A,B in a new sheet. Where column A has heading name and column B has heading date.

Initial data:

Initial data

After Code:

After Code

What I have attempted so far - i can't seem to get the paste correct

Sub Test()
    
    Dim o           As Variant
    Dim CountC_Range As Range
    Dim cel_3       As Range
    Dim MyRange     As Range
    
    'count the number of different engagement areas
    Worksheets("Sheet8").Activate
    Range("B4").Select
    Set CountC_Range = Range("B4", Selection.End(xlToRight))
    
    'Set the letter k as number of engagements as we'll use this later
    o = WorksheetFunction.CountA(CountC_Range) - "1"
    
    Worksheets("sheet9").Activate
    Range("A1").Select
    MyRange = Range("Selection.End(xlDown) + 1", "Selection.End(xlDown) + o + 1")
    
    For Each cel_3 In Worksheets("Sheet8").Range("4:4")
        If cel_3.Value <> "" Then                
            MyRange = cel_3.Value                
        End If            
    Next cel_3
    
End Sub

Vityata

There are plenty of ways to do it, but having this input:

The code below will provide this:

Sub TestMe()
            
    With Worksheets("Source")
        Dim k As Long
        k = .Range("A4").End(xlToRight).Column
    End With
    
    With Worksheets("Target")
        Dim i As Long, ii As Long
        Dim currentRow As Long
        
        For i = 1 To k
            For ii = 1 To k
                currentRow = currentRow + 1
                .Cells(currentRow, "A") = Worksheets("Source").Cells(3, i)
                .Cells(currentRow, "B") = Worksheets("Source").Cells(4, i)
            Next
        Next
    End With
  
End Sub

Dependencies:

Name the input worksheet "Source"
Name the output worksheet "Target"

A must read - How to avoid using Select in Excel VBA

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-11-11

Comments

0 comments

VBA - Copy and Paste Multiple Times Between Excel Sheets

VBA - Copy and Paste Multiple Times Between Excel Sheets

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Double spacing in rmarkdown pdf

SQL Server : need add a dot before two last character

C++ 16 bit grayscale gradient image from 2D array

JMeter: Why get error when try to save test plan

JWT gives JsonWebTokenError "invalid token"

How to make thrown errors visible outside of a Promise?

How to tell if iOS Today Widget is being updated in the background?

Calling Doctrine clear() with an argument is deprecated

Capybara Selenium Chrome opens About Google Chrome

How to update azerothcore-wotlk docker container

Adding Ripple Effect to RecyclerView item

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

Error while applying filter on dataframe - PySpark

Unable to add slack to bluemix project

MyPy fails dataclass argument with optional list of objects type

How can I validate and parse phone numbers to extract their country calling code and area code?

Single Sign-On in Spring by using SAML Extension and Shibboleth

python how to create many-to-many of lists inside one list