+ Reply to Thread
Results 1 to 3 of 3

Excel vba copy values from one sheet to another insert row / transpose

  1. #1
    Registered User
    Join Date
    07-21-2023
    Location
    uae
    MS-Off Ver
    2016
    Posts
    8

    Excel vba copy values from one sheet to another insert row / transpose

    excel vba report getting in wrong formats. codes and screenshots attached.

    Sub TransferDataAndInsertRows()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim row1 As Long, row2 As Long
    Dim col As Long
    Dim numNonBlanks As Long

    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")

    row1 = 4 ' Starting row in Sheet1
    row2 = 9 ' Starting row in Sheet2

    Do While ws1.Cells(row1, 2).Value <> ""

    ' Copy particulars and amount to Sheet2
    ws2.Cells(row2, 4).Value = ws1.Cells(row1, 2).Value
    ws2.Cells(row2, 7).Value = ws1.Cells(row1, 3).Value

    ' Count non-blank cells in D4:M4
    numNonBlanks = Application.WorksheetFunction.CountA(ws1.Range("D" & row1 & ":M" & row1))

    ' Insert rows in Sheet2 if needed
    If numNonBlanks > 0 Then
    ws2.Rows(row2 + 1 & ":" & row2 + numNonBlanks).Insert Shift:=xlDown
    End If

    ' Copy non-blank values from D4:M4 to Sheet2
    col = 5
    For i = 4 To 13
    If ws1.Cells(row1, i).Value <> "" Then
    ws2.Cells(row2 + 1, col).Value = Application.Transpose(ws1.Cells(row1, i).Value)
    col = col + 1
    End If
    Next i

    row1 = row1 + 1
    row2 = row2 + numNonBlanks + 1

    Loop

    End Sub

    ====
    screenshots
    sheet1
    sh1.jpg

    sheet2-getting wrong report as per above code.
    sh2wrongformat.jpg

    sheet2 output report should be
    sh2 correct.jpg
    Last edited by norules1; 12-30-2023 at 08:51 AM.

  2. #2
    Forum Contributor
    Join Date
    10-16-2023
    Location
    Poland
    MS-Off Ver
    Excel 2019 32bit WIN10
    Posts
    142

    Re: Excel vba copy values from one sheet to another insert row / transpose

    Try this:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-21-2023
    Location
    uae
    MS-Off Ver
    2016
    Posts
    8

    Re: Excel vba copy values from one sheet to another insert row / transpose

    Tajan,
    Awesome! thank you so much!

+ 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] How Insert Rows, Copy Row, and Transpose to Column
    By VictoriaN in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 01-14-2023, 08:01 AM
  2. VBA to transpose required column and insert few variable by look up with other sheet
    By abraham123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-20-2020, 10:03 PM
  3. [SOLVED] Copy values from other sheet and TRANSPOSE Paste in active sheet
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2019, 02:23 AM
  4. macro to insert, copy and transpose values + loop
    By ddenicola in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2013, 09:31 AM
  5. [SOLVED] Copy transpose insert rows
    By Alprashant in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-22-2013, 07:37 AM
  6. Replies: 1
    Last Post: 05-11-2013, 02:58 AM
  7. [SOLVED] Need Help w/Macro that Insert Rows, Copy Row, and Transpose to Column
    By hiroki3296 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-01-2012, 10:27 AM

Tags for this Thread

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