VBA to copy data from one sheet and paste against a range and repeat using LOOP or any other method-CodePudding

Essentially I tried doing using using Macro and see how it records in VBA. I have this dataset where I want to copy company names from a sheet and paste it next to years ranging from 1994-2014. And then repeat the same process for more roughly 800 companies.

I tried doing one with this code but I believe I need to loop the code. Not really a VBA expert just trying to saving time and error using VBA

Sub CopyPaste()
CopyPaste Macro
Sheets("Name").SelectRange("C3").Select
Selection.Copy
Sheets("Stata").Select
Range("B2:B22").PasteSpecial xlPasteValues
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

CodePudding user response：

This is going to be very crude but the following code will accomplish what you want. Especially if you only need to do this exercise once.

In my example the companies list starts in cell A2 and the output starts in cell D2. I have used 3 years (2019 - 2021)

For the code below, the location (row & col) of the first company are placed in the variables copyRow & copyCol as integers (ie A2 = Row: 2, Col: 1) The first cell of the output location is set in the pasteRow & pasteCol (ie D2 = Row: 2, Col: 4) variables. Also the start year and end year are also set in the startYear and endYear variables.

Basically, from there it is just two nested loops. A while for the companies that allows any number of companies as long as there are no empty cells in the list. The for loop to cycle through each year between startYear and endYear for each company.

Sub copyPaste()

Dim copyWb As Workbook
Dim copySht As Worksheet
Dim pasteRow As Integer
Dim pasteCol As Integer
Dim copyRow As Integer
Dim copyCol As Integer
Dim startYear As Integer
Dim endYear As Integer

Set copyWb = ActiveWorkbook
Set copySht = copyWb.ActiveSheet
copySht.Activate

pasteRow = 2
pasteCol = 4
copyRow = 2
copyCol = 1
startYear = 2019
endYear = 2021

While copySht.Cells(copyRow, copyCol).Value <> ""
    
    For curYear = startYear To endYear
        copySht.Cells(pasteRow, pasteCol).Value = curYear
        copySht.Cells(pasteRow, pasteCol   1).Value = copySht.Cells(copyRow, copyCol).Value
        pasteRow = pasteRow   1
    Next curYear
    copyRow = copyRow   1
    
Wend

End Sub

CodePudding user response：

I am trying to make some changes to the code as I want to copy from one and paste in another sheet. I added the variable pasteSht, but when referencing to the pasteSht.pasteRow it's throwing me an error.

Sub copyPaste()

Dim copyWb As Workbook
Dim copySht As Worksheet
Dim pasteSht As Worksheet
Dim pasteRow As Integer
Dim pasteCol As Integer
Dim copyRow As Integer
Dim copyCol As Integer
Dim startYear As Integer
Dim endYear As Integer

Set copyWb = ActiveWorkbook
Set copySht = copyWb.Worksheets("Sheet1")
Set pasteSht = copyWb.Worksheets("Sheet2")
copySht.Activate

pasteRow = 2
pasteCol = 4
copyRow = 2
copyCol = 1
startYear = 2019
endYear = 2021

While copySht.Cells(copyRow, copyCol).Value <> ""
    
    For curYear = startYear To endYear
        copySht.Cells(pasteRow, pasteCol).Value = curYear
        copySht.Cells(pasteRow, pasteCol   1).Value = copySht.Cells(copyRow, copyCol).Value
        pasteRow = pasteRow   1
    Next curYear
    copyRow = copyRow   1
    
Wend

End Sub