+ Reply to Thread
Results 1 to 4 of 4

Copy and pasting dynamic column from one sheet to 8x12 table in another sheet using VBA

  1. #1
    Registered User
    Join Date
    04-13-2020
    Location
    MD
    MS-Off Ver
    2018
    Posts
    19

    Copy and pasting dynamic column from one sheet to 8x12 table in another sheet using VBA

    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
    Attached Files Attached Files
    Last edited by skhan5427; 09-11-2020 at 01:15 PM.

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Copy and pasting dynamic column from one sheet to 8x12 table in another sheet using VB

    Possibly...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-13-2020
    Location
    MD
    MS-Off Ver
    2018
    Posts
    19

    Re: Copy and pasting dynamic column from one sheet to 8x12 table in another sheet using VB

    This works perfect! Thanks

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Copy and pasting dynamic column from one sheet to 8x12 table in another sheet using VB

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 02-19-2016, 06:17 PM
  2. Copy and pasting a dynamic range from one sheet to another
    By drpurple in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2014, 09:44 AM
  3. Simply Copy and pasting data from one sheet to another sheet but certain cells....
    By cory0789 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-24-2014, 09:36 AM
  4. [SOLVED] How to skip an entire column when copy pasting from another sheet
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-21-2013, 01:25 PM
  5. Copy and Pasting dynamic data into blank rows and of another sheet
    By Lisa4legin in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-18-2013, 07:49 PM
  6. Dynamic copying and pasting of rows from one sheet into another sheet
    By princesspeach in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2013, 01:50 PM
  7. Excel 2007: Copy dynamic table from one sheet to another
    By lilbird1981 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-12-2012, 10:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1