如何将数据复制并粘贴到特定行 [英] how to copy and paste data to a specific row

问题描述

如何从自定义开始行和最后一列(Sheet1)复制数据并将其粘贴到自定义行和自定义列?

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

我所做的是:

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

返回列名的代码

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

我一直在复制时出错，并将数据粘贴到"B10"范围内的"Sheet2"中.

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

推荐答案

尝试一下，代码是很容易解释的，但是如果您需要了解任何内容，请告诉我.

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

此外，您不需要知道列字母，可以使用Cells(Row，Column)，它的索引编号为1 = A，2 = B，并且行等于该行的编号.

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.

这篇关于如何将数据复制并粘贴到特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！