+ Reply to Thread
Results 1 to 3 of 3

Modify code to also transpose data into table

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    452

    Modify code to also transpose data into table

    I have the following code which will copy and paste various things from the Calculator sheet into the Journal sheet. In addition to that, I need Caclulator!R2:243 copied and pasted as values and transposed into the Data sheet, specifically into the table that starts at A1.

    Thank you much!

    Sub ExportExerciseStats()
    Dim sws As Worksheet, dws As Worksheet
    Dim lrow As Long
    Dim i
    '*************
    Dim response As VbMsgBoxResult
    response = MsgBox("Are you sure you want to export data as " & Range("AJ2").Text & " ?", vbCritical + vbYesNo + vbDefaultButton2, "Run Macro")
    If response = vbNo Then
    Exit Sub
    End If
    '*****************
    Set sws = ThisWorkbook.Worksheets("Calculator")
    Set dws = ThisWorkbook.Worksheets("Journal")
    Application.ScreenUpdating = False
    With dws.Range("A" & Rows.Count).End(xlUp)
        .Offset(2).Value = sws.Range("K8").Value
        .Offset(3).Value = sws.Range("K9").Value
        .Offset(4).Value = sws.Range("K10").Value
    
    lrow = sws.Range("X" & Rows.Count).End(xlUp).Row
    sws.Range("X2").Resize(lrow - 1, 5).Copy
    .Offset(5).PasteSpecial xlPasteValues
    .Offset(5).Resize(lrow - 1, 5).Borders.LineStyle = xlContinuous
    Application.CutCopyMode = False
    End With
    With sws.Range("AD10")
    For i = 0 To 3
    If .Offset(i) <> "" Then
        dws.Range("A" & Rows.Count).End(xlUp).Offset(1) = .Offset(i) & "-" & .Offset(i, 1)
    End If
    Next
    End With
    sws.Range("X3").Resize(lrow - 2, 4) = ""
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by Butcher1; 11-29-2020 at 06:57 PM.

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Modify code to also transpose data into table

    just copy the range, paste it on the other sheet and transpose it by writing the function in the top level cell only, I think. you need these:

    https://docs.microsoft.com/en-us/off...l.range.select

    https://docs.microsoft.com/en-us/off...cel.range.copy

    https://docs.microsoft.com/en-us/off...e.pastespecial

    https://support.microsoft.com/en-us/...9-4b6dfac76027

  3. #3
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,515

    Re: Modify code to also transpose data into table

    Caclulator!R2:243 copied and pasted as values and transposed into the Data sheet, specifically into the table that starts at A1.
    Sub sintekJ3v16()
    Dim NxtRow As Range
    With Sheets("Data").ListObjects("Ex_Data")
        If Not .InsertRowRange Is Nothing Then
            Set NxtRow = .InsertRowRange.Cells(1)
        Else
            Set NxtRow = .HeaderRowRange.Cells(1).Offset(.ListRows.Count + 1)
        End If
    End With
    Sheets("Calculator").Range("R2:R243").Copy
    NxtRow.PasteSpecial xlPasteValues, Transpose:=True
    Application.CutCopyMode = False
    End Sub
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

+ 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. [SOLVED] VBA Form search and modify buttons code don't work for a table not starting in A1
    By Gagahoučte in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-26-2020, 12:17 AM
  2. Replies: 5
    Last Post: 03-19-2018, 02:42 PM
  3. Replies: 8
    Last Post: 04-04-2016, 02:19 PM
  4. transpose column to a range calculate and transpose to new table loop
    By moshro1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-02-2015, 10:52 AM
  5. [SOLVED] Please help me to modify the code copy/paste/transpose
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-30-2013, 09:44 AM
  6. Modify existing macro to transpose results
    By Cody1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2013, 09:29 AM
  7. [SOLVED] Modify code to transpose a stack of data to rows on to delimit on text hone number field
    By coachtim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-26-2012, 02:52 PM

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