how to copy and paste data to a specific row

Question

How do I copy data from custom start row and last column (Sheet1) and paste it on custom row and custom column?

What I have done is:

Dim sourceFileName As String
sourceFileName = "asal-gc.xlsx"
Dim sourceFileURL As String
sourceFileURL = "C:\Users\xxx\Desktop\NewFolder\" & sourceFileName
Dim sourceFileSheet As String
sourceFileSheet = "Sheet1"

Dim defaultRowCell As Integer
defaultSourceRow = 6


Workbooks.Open Filename:=sourceFileURL
Workbooks(sourceFileName).Worksheets(sourceFileSheet).Activate
Workbooks(sourceFileName).Worksheets(sourceFileSheet).Select

//return value = 2
Dim LastColumn As Long
'Find the last used column in a Row
With ActiveSheet.UsedRange
    LastColumn = .Cells(defaultSourceRow, .Columns.Count).End(xlToLeft).Column
End With

//return string = B
Dim myCol As String
myCol = GetColumnLetter(LastColumn)
MsgBox myCol

//return value 13
Dim LastRow As Long
With ActiveSheet
    LastRow = .Cells(.Rows.Count, myCol).End(xlUp).Row
End With
MsgBox LastRow

Dim rangeCopy As String
str3 = myCol & defaultSourceRow & ":" & myCol & LastRow   

Workbooks(sourceFileName).Worksheets(sourceFileSheet).Range(str3).Copy Destination:= Workbooks(sourceFileName).Worksheets("Sheet1").Range("c6")        
End Sub

code for returning column name

Function GetColumnLetter(colNum As Long) As String
    Dim vArr
    vArr = Split(Cells(1, colNum).Address(True, False), "$")
    GetColumnLetter = vArr(0)
End Function

I keep getting an error on copy and paste the data into "Sheet2" in range "B10".

I can't see any copying and pasting in your code? You should read up on how to avoid Select/Activate. — SJR
@SJR already updated. is there a link to it ? because some code are copy and paste — chopperfield
what is the error you are getting? Also you had an extra double-quote on the line sourceFileSheet = "Sheet1"" which I have corrected. I assume this isn't the problem in your actual code or it wouldn't have compiled. — aucuparia
Read this stackoverflow.com/questions/10714251/… Check the value of those variables in the copy line and I guess one is not what you are expecting. — SJR
Yes there are several things in your code which wouldn't even compile. It might be enough just to remove .usedrange from this line With ActiveSheet.UsedRange. — SJR

Damian Damian · Accepted Answer · 2019-03-12T12:05:06

Try this, the code is pretty self explanatory, but if you need to understand anything let me know.

Option Explicit
Sub CopyPaste()

    Dim wb As Workbook, wbSource As Workbook, ws As Worksheet, wsSource As Worksheet 'variables for workbooks and sheets
    Dim LastRow As Long, LastCol As Integer 'variables for number rows and columns

    'To avoid selecting you must reference all the workbooks and sheets you are working on, and this is how:
    Set wb = ThisWorkbook 'this way you reference the workbook with the code
    Set ws = wb.Sheets("ChangeThis") 'name of the worksheet where you are going to pase
    Set wbSource = Workbooks.Open("C:\Users\xxx\Desktop\NewFolder\asal-gc.xlsx", UpdateLinks:=False, ReadOnly:=True) 'the source data workbook
    Set wsSource = wbSource.Sheets("Sheet1") 'the source data worksheet

    'Finding the range you want to copy
    With wsSource
        LastCol = .Cells(6, .Columns.Count).End(xlToLeft).Column 'this will get the last column on row 6, change that number if you need to
        LastRow = .Cells(.Rows.Count, LastCol).End(xlUp).Row 'this will get the last row on the last column, change the number of the col if there is more data on another column
        'this is taking the whole range from A1 to last col and row
        .Range("A1", .Cells(LastRow, LastCol)).Copy _
            Destination:=ws.Range("A1") 'this is where it will paste, if not range A1, change it wherever you need
    End With

    wbSource.Close Savechanges:=False 'this will close the source data workbook without saving

End Sub

Also, you don't need to know the column letter, you can work with Cells(Row, Column) it works with their index number: 1 = A, 2 = B and for rows equals the number to the row.

how to copy and paste data to a specific row

1 Answers