CODEWITHSUNDEEP
excelvba
CodeOut
CodeOut

Reputation: 117

VBA Paste Column starting at row

I'm trying to copy all columns from one workbook sheet and paste to another workbook sheet but pasting all columns starting at Row A8

I've got the code working to copy and paste from one workbook sheet to another, I just can't figure out the code to add to paste the columns starting at row A8.

This is what I have so far:

Set Data = Workbooks.Open(ThisWorkbook.Path & "\Technical Audit.xlsm", UpdateLinks:=xlUpdateLinksAlways)
Worksheets("Raw Data").Select

Set SourceImageColumn = Workbooks("Audit Dashboard.xlsm").Worksheets("Raw Image Data").Columns("A:D")
Set TargetImageColumn = Workbooks("Technical Audit.xlsm").Worksheets("Images").Columns("A:D")

SourceImageColumn.Copy Destination:=TargetImageColumn

Data.Save

Thanks in advance for any help

Upvotes: 0

Views: 1812

Answers (2)

Niclas
Niclas

Reputation: 1252

** Updated the code ** This method will work if you have both workbooks open. I couldn't figure out if you have this or not. 

Sub macro3()
Dim iRow        As Long
Dim lastRow     As Long
Dim nextRow     As Long
Dim wksSource   As Worksheet
Dim wksTarget   As Worksheet

Set wksSource = Workbooks("Audit Dashboard.xlsm").Worksheets("Raw Image Data")
Set wksTarget = Workbooks("Technical Audit.xlsm").Worksheets("Images")

lastRow = wksSource.Range("A" & wksSource.Rows.Count).End(xlUp).Row

nextRow = 8

With wksSource
    For iRow = 1 To lastRow
        .Range(.Cells(iRow, 1), .Cells(iRow, 4)).Copy wksTarget.Cells(nextRow, 1)
        nextRow = nextRow + 1
    Next iRow
End With
End Sub

Upvotes: 1

K Scandrett
K Scandrett

Reputation: 16541

Set wsCore = Workbooks("Audit Dashboard.xlsm").Worksheets("Raw Image Data")
Set wsData = Workbooks("Technical Audit.xlsm").Worksheets("Images")

With Intersect(wsCore.Columns("A:D"), wsCore.UsedRange)
    .Columns("A").Copy Destination:=wsData.Range("A8")
    .Columns("B").Copy Destination:=wsData.Range("B8")
    .Columns("C").Copy Destination:=wsData.Range("C8")
    .Columns("D").Copy Destination:=wsData.Range("D8")
End With

A modified answer of https://stackoverflow.com/a/29359212/1544886

Will copy from wsCore columns A:D, and paste into wsData starting at A8:D8

Upvotes: 1

Related Questions