Selected Answer
I think your code is not very well written and unsuitable to be placed in the hands of a layman. Therefore it's no fun to modify it and I have chosen to re-write instead.
Option Explicit
Private Enum Nws ' set your source parameters here
NwsFirstDataRow = 2
NwsFirstDataColumn = 1 ' 1 = column A, 2 = B etc
NwsLongestRow = 1
NwsLongestColumn = 1
End Enum
Sub TransferData()
' Variatus @TeachExcel
' 17 Jan 2020
Const Destination As String = "C4" ' first destination cell in Target sheet
' change as desired
Dim WsS As Worksheet, WsT As Worksheet
Dim Arr As Variant
Dim RngS As Range, RngT As Range
Dim Rl As Long, Cl As Long ' Last row, last column
Set WsS = Worksheets("Sheet1") ' Source: modify name as desired
Set WsT = Worksheets("Sheet2") ' Target: modify name as desired
' Never touch the code below. Do all modification above this line.
With WsS
Rl = .Cells(.Rows.Count, NwsLongestColumn).End(xlUp).Row
Cl = .Cells(NwsLongestRow, .Columns.Count).End(xlToLeft).Column
' determine the range to be copied from
Set RngS = .Range(.Cells(NwsFirstDataRow, NwsFirstDataColumn), _
.Cells(Rl, Cl))
End With
With WsT
Rl = .Cells(.Rows.Count, .Range(Destination).Column).End(xlUp).Row + 1
If Rl < .Range(Destination).Row Then Rl = .Range(Destination).Row
Set RngT = .Cells(Rl, .Range(Destination).Column)
End With
With RngS
Arr = .Value
RngT.Resize(.Rows.Count, .Columns.Count).Value = Arr
End With
End Sub
In fact, your code is a perfect demonstration of how NOT to write code.
- Variables are named as x, I, J, K.
Best practise is to give meaningful names.
- Parameters are written directly into the code.
Best practise is to separate parameters from the code so that the user doesn't need to modify the code when he wants to modify the parameters.
- Modification of the data is required ("Done" to be found in column "K" - which isn't the worksheet column K).
Best practise is to never modify the data.
Not that your code might not function. Rather, a combination of the above faults has rendered it useless to you and forced you to seek help. I hope this won't happen with the code I wrote.
Before you start, please set the parameters. There are seven of them.
- There are 4 enumerations at the top.
- Set the first row of data you wish to copy and the first column.
- Then identify the longest column and the longest row.
In the example in the attached workbook columns A and B are of different lengths. Try the result if your set NwsLongstColumn to 1 or 2. Same for the rows. In most cases column A and row 1 will be the longest. This depends upon your data.
The effect is that data in the longest column and columns to the left of it will be copied, and data starting from the FirstDataRow to the last row in the longest column.
The Kutools code uses the UsedRange to determine the range to copy. That has other drawbacks.
- Set the constant Destination. This should be the top left cell of where you want the data to be pasted. In my example this cell is defined as C4. In your code the UsedRange is used to find a row in column A. UsedRange.Row may give the wrong result and you need to look in the code to modify the column. With my code you can change the target column but that column is used to determine the last used row too. If you are pasting data of different number of rows in some of the columns my arrangement will lead to data being over-written if the Destination column isn't the longest column. This is a conflict between determining the source and the target ranges. Some modification of the code would be required if this is relevant to you.
- Insert the names of the two worksheets, Source and Target. These names must match actual names in your workbook.
I suggest you play around with the parameters a little to familiarise yourself with the functionality. After that, set the parameters for what you need and forget them. Chances are, once set you will never need them again.
One more thing. This code works very fast because it copies only data, no formatting. If you need the data in the target sheet to be formatted the best way is to designate the first data row (the one indicated by the Destination constant) in the target sheet as a sample and add code to the above procedure to copy formats from there to the rest of the sheet. In that way your target sheet would be independent from the formats of imported data and changing the formats in that row would automatically be trasnsferred to all the sheet or only the new additions, according to a rule you would yet have to decide.