I need vba to copy and paste the values from the dynamic column in sheet 1 and paste them to the 8x12 table in sheet 2 upon pressing the command button. The names should end up being in order going down and to the right. I’m getting caught up because the two tables have different dimensions also the number of rows in column 1 can change. I have shown what it should end up looking like in the attachment. Let me know if any clarification is needed!
This is what I have so far but the loop takes much too long to execute. Is there a way I could simplify this to make the run time shorter?
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim sws As Worksheet: Set sws = ThisWorkbook.Sheets("Sheet1")
Dim dws As Worksheet: Set dws = ThisWorkbook.Sheets("Sheet2")
LastRow = 14
LastCol = 13
a = 2
DoEvents
For i = 2 To LastCol
For j = 7 To LastRow
sws.Cells(a, 1).Copy
dws.Cells(j, i).PasteSpecial Paste:=xlPasteValues
a = a + 1
Next j
Next i
End Sub
Bookmarks