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, Mar 12 '19 at 11:41
@SJR already updated. is there a link to it ? because some code are copy and paste — chopperfield, Mar 12 '19 at 11:50
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, Mar 12 '19 at 11:56
Read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba Check the value of those variables in the copy line and I guess one is not what you are expecting. — SJR, Mar 12 '19 at 12:03
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, Mar 12 '19 at 12:07

score 1 · Accepted Answer · answered Mar 12 '19 at 12:05

1

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.

answered Mar 12 '19 at 12:05

Damian

5,152
1
10
21

i have tried your code and i got an error `run time error '1004': Application-defined or Object-defined error`. the code error on the copy function. because i have tried display msgbox before `.Range("A1"..` – chopperfield Mar 13 '19 at 08:15
is it possible to change `Set wb = ThisWorkbook ` as not in thisworkbook but another excel that is not open. i mean copy and paste from excel to excel which they are not open yet.. and what does `option explicit used` for – chopperfield Mar 13 '19 at 08:47
1

Hello @chopperfield as for your first comment, the code is all right, it copies from the data workbook to the workbook containing the code on the cell A1. As for the second, `Set wb = ThisWorkbook` is the workbook you have the code on, and where you will paste. `Set wbSource = ...` is for the workbook which will be openned and will contain your data. `Option Explicit` forces you to declare all your variables, which is important. – Damian Mar 13 '19 at 16:09
oh yeah, I'm mistype it. thanks for the insight. but i need to set `A1` as not static, because it can change. that's why i still need to use `GetColumnLetter` function and either copy/paste with custom range cell – chopperfield Mar 14 '19 at 06:04
Where do you need to paste the data? – Damian Mar 14 '19 at 07:02
last column with custom range where iam already declare the starting point as static and for the end point is last row of the column. (e.g .range(k10:k34)). where `K` is last column letter; 10 is static starting point and 34 is last row of the column). but i already got that – chopperfield Mar 14 '19 at 07:50
Ok, glad you could! Check out for more examples on where to do your code without using `.select` it will spare you a lot of troubles and headaches – Damian Mar 14 '19 at 08:02
@Damin i got another scenario. how do i select entire column by ignoring the merge cell ?. so before i try selecting the entire column by `range("B:B").select` where the workbook got no merge cell. problem comes when from `A1 till C4` was a merge cell. when i try to select it with the same code. it automatically select the whole cell from `A till C` instead only whole column of `B` – chopperfield Mar 15 '19 at 02:41
@chopperfield like you've been told, you should stop selecting cells and just use methods like `.value = .value` or `.copy destination` that way no merged cells would be a problem. Always thinking that a group of merged cells, the value is on the top left cell. – Damian Mar 15 '19 at 07:35
ahh iam sorry for not being clear. i use `select` just to see which column got selected. the case is almost same as this `https://excel.tips.net/T003093_Selecting_Columns_in_VBA_when_Cells_are_Merged.html` . in the end, I'm duplicating the code if there's is a merged row and no merged row. whereas the merged row using range starting point – chopperfield Mar 15 '19 at 08:43
`Range("A1").MergeArea.Rows.Count` or `Range("A1").MergeArea.Columns.Count` can give you the number of columns or rows that are merged. – Damian Mar 15 '19 at 08:46

how to copy and paste data to a specific row

1 Answers1

Linked