VBA code to copy and paste rows twice from one workbook to another

excel vba copy range from another workbook without opening
vba copy columns from one workbook to another
excel vba copy data from another workbook without opening
how to transfer data from one excel workbook to another automatically
macro to copy specific columns from one worksheet to another worksheet
excel vba copy visible rows to another worksheet
excel copy rows from one sheet to another based on criteria
excel macro to copy and paste from one worksheet to another

I´m struggling to create a VBA code that does the following:

Copy and paste values from source data set:

into a new workbook in a format shown here:

VBA below works fine to paste values from columns C & D in alternate rows into the new workbook:

Sub rangeToColumn()

Dim rng As Range
Dim i 'index of cells
Dim f

Set rng = Workbooks("Excel1").Worksheets("SourceSheet").Range("C3:D6")
i = 0
For Each f In rng
    i = i + 1
    Workbooks("Excel2").Worksheets("TargetSheet").Cells(i, 1).Value = f.Value
Next f

End Sub

However I just can´t find out how to proceed with the rest. Tried multiple things but none of them worked. I basically need B3:B6 values from source to have twice in column C in target workbook and same for E3:E6 with only difference that each line below should be the opposite value.

Any help is much appreciated. Thanks.

Thomas.

You could try:

Option Explicit

 Sub test()

    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim wbNew As Workbook
    Dim LastrowS As Long, LastrowT As Long, i As Long
    Dim Desc As String, BS As Long, PL As Long

    Set wsSource = ThisWorkbook.Worksheets("Sheet1")

    Set wbNew = Workbooks.Add
        Application.DisplayAlerts = False
            wbNew.SaveAs Filename:="C:\Users\XXXXXX\Desktop\New_Workbook_Test.xls"
        Application.DisplayAlerts = True

    Set wsTarget = wbNew.Worksheets("Sheet1")

    LastrowS = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row

        For i = 2 To LastrowS

            Desc = wsSource.Range("B" & i).Value
            BS = wsSource.Range("C" & i).Value
            PL = wsSource.Range("D" & i).Value

            LastrowS = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row

            wsTarget.Range("A" & LastrowS + 1).Value = BS
            wsTarget.Range("C" & LastrowS + 1).Value = Desc
            wsTarget.Range("F" & LastrowS + 1).Value = -Abs(BS)

            wsTarget.Range("A" & LastrowS + 2).Value = PL
            wsTarget.Range("C" & LastrowS + 2).Value = Desc
            wsTarget.Range("F" & LastrowS + 2).Value = -Abs(PL)

        Next i

 End Sub

copy single cell and paste twice. - Excel VBA / Macros, Then by looping copy the next single cell and copy twice . The macro only copy the last row from my column( please see below). It is 13 and paste it twice. I wan to start from 1 paste it twice to another workbook and come  Rows & Columns – Paste vs. Insert. When pasting rows and columns you have two options: You can paste over the existing row (or column) or you can insert a new row (or column). Let’s look at the difference… Copy & Paste Over Existing Row / Column. This will copy row 1 and paste it into the existing row 5:

Here is one way. Loop through each row of your starting range and reference each cell that way. You'll need to add in your workbook/sheet references.

Sub x()

Dim r As Range, n As Long, i As Long

Set r = Sheet1.Range("B2", Sheet1.Range("E" & Rows.Count).End(xlUp))

For i = 1 To r.Rows.Count
    n = n + 1
    Sheet2.Cells(n, 1).Value = r.Cells(i, 2).Value
    Sheet2.Cells(n, 3).Value = r.Cells(i, 1).Value
    Sheet2.Cells(n, 6).Value = r.Cells(i, 4).Value
    n = n + 1
    Sheet2.Cells(n, 1).Value = r.Cells(i, 3).Value
    Sheet2.Cells(n, 3).Value = r.Cells(i, 1).Value
    Sheet2.Cells(n, 6).Value = r.Cells(i, 4).Value * -1
Next i

End Sub

Starting data (Sheet1)

Output (Sheet2)

Excel VBA code to paste copied data twice - excel - iOS, I wrote a code to copy data from two sheets: ABC and XYZ, and paste it one after then adding that data to the first empty row of a sheet in another workbook. In this article, you will learn how to copy data from one workbook to another & transpose the data using VBA code. Let us understand with an example: We have 2 workbooks- Book1 & Book2. Book2 contains the data entered in Book1. Refer below snapshot of Book1

try this code which you can customize to fit your needs.

If you have more columns just copy and paste at the end the code bellow "Copy first column"

Sub rangeToColumn()

    ' Define objects
    Dim sourceRange As Range
    Dim sourceCell As Range
    Dim targetCell As Range

    ' Define variables
    Dim sourceWBName As String ' WB = Workbook name
    Dim targetWBName As String

    Dim sourceWSName As String ' WS = Worksheet name
    Dim targetWSName As String

    Dim sourceRangeAddress As String
    Dim targetInitCellAddress As String ' Cell's address

    Dim counter As Integer ' Change for long instead of integer if more than 32000 values

    ' Initialize variables
    sourceWBName = "Book1"
    targetWBName = "Book2"

    sourceWSName = "SourceSheet"
    targetWSName = "TargetSheet"



    ' Copy first column
    sourceRangeAddress = "C2:C5"
    targetInitCellAddress = "A1" ' Address of first cell where to begin copy the values

    Set sourceRange = Workbooks(sourceWBName).Worksheets(sourceWSName).Range(sourceRangeAddress)
    Set targetCell = Workbooks(targetWBName).Worksheets(targetWSName).Range(targetInitCellAddress)

    counter = 0

    For Each sourceCell In sourceRange

        targetCell.Offset(counter, 0).Value = sourceCell.Value

        counter = counter + 1

    Next sourceCell


    ' Copy second column
    sourceRangeAddress = "B2:B5"
    targetInitCellAddress = "C1" ' Address of first cell where to begin copy the values

    Set sourceRange = Workbooks(sourceWBName).Worksheets(sourceWSName).Range(sourceRangeAddress)
    Set targetCell = Workbooks(targetWBName).Worksheets(targetWSName).Range(targetInitCellAddress)

    counter = 0

    For Each sourceCell In sourceRange

        targetCell.Offset(counter, 0).Value = sourceCell.Value

        counter = counter + 1

    Next sourceCell


    ' Copy third column
    sourceRangeAddress = "E2:E5"
    targetInitCellAddress = "F1" ' Address of first cell where to begin copy the values

    Set sourceRange = Workbooks(sourceWBName).Worksheets(sourceWSName).Range(sourceRangeAddress)
    Set targetCell = Workbooks(targetWBName).Worksheets(targetWSName).Range(targetInitCellAddress)

    counter = 0

    For Each sourceCell In sourceRange

        targetCell.Offset(counter, 0).Value = sourceCell.Value

        counter = counter + 1

    Next sourceCell

End Sub

Please mark this answer if it helped you.

VBA to copy an entire row and paste it twice, Excel Facts. Repeat Last Command. Click here to reveal answer. I´m struggling to create a VBA code that does the following: Copy and paste values from source data set: into a new workbook in a format shown here: VBA below works fine to paste values from columns C & D in alternate rows into the new workbook:

Excel VBA : Filtering and copy pasting to new sheet or workbook, Excel VBA : Filtering and copy pasting to new sheet or workbook Suppose you want to filter and paste data to a new workbook instead of adding It also writes ' Total' at last row of column A. It assumes your data starts from column A. But, when i ran twice or more, it gets error, I thinks its because the sheet with the same   Example #2 – Copy to another Worksheet in the Same Workbook. Now if we want to copy-paste the value from the different worksheets using VBA macro then in the Destination argument we need to reference the sheet name by using WORKSHEETS object then mention the range of cells in that WORKSHEET.

How to duplicate a sheet in Excel with VBA, If you'd like to copy several sheets from the active workbook to a new one, Next , double-click the form and paste the below code in the Code  copy and paste only filter cells from one excel to other excel using VBA excel-vba I’m stuck with a problem when I try to copy filtered cells from one excel file and paste it onto another file. I am using a macro call to get data where I try to fix this given code. It is not working when I try to copy visible cells which i filtered.

How to copy and insert row multiple times or duplicate the row x , Here, I will introduce a useful feature, Kutools for Excel's Duplicate Rows Paste to Filtered Range. Super Filter (save and apply filter schemes to other sheets); Combine Workbooks and For the second VBA code  These examples below will show you how to copy a Sheet within the same Workbook. When copying a Sheet within a Worbook, you must specify a location. To specify a location, you will tell VBA to move the Worksheet BEFORE or AFTER another Worksheet. Copy Sheet Before Another Sheet. Here we will specify to copy and paste the Sheet before Sheet2

Comments
  • Guys you´re awesome, thanks a lot for you help! :) it´s doing exactly what I needed