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".
Try this, the code is pretty self explanatory, but if you need to understand anything let me know.
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.