Feb 04 2021 01:52 PM
Hey friends. Im New in VBA. Could you help me at this?
So, we have 2 columns: AB with data, in Sheet1
I want to COPY until Last Row in AB Column from Sheet1
and paste it to Sheet2, column CD in FIRST EMPTY ROW (i mean by adding more data, cause CD is supposed to have previous data)
Can anyone help? It would be a huge favour for me! THANKS !!
Feb 04 2021 02:06 PM
Try this:
Sub CopyData()
Dim m As Long
Dim r As Long
m = Worksheets("Sheet1").Range("AB" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row
r = Worksheets("Sheet2").Range("CD" & Worksheets("Sheet2").Rows.Count).End(xlUp).Row + 1
Worksheets("Sheet1").Range("AB1:AB" & m).Copy Destination:=Worksheets("Sheet2").Range("CD" & r)
Application.CutCopyMode = False
End Sub
Feb 04 2021 02:36 PM
My friend, actually it does nothing, it runs without error, but the data are not shown in CD in Sheet2. They arent pasted anywhere. I don't know where the problem is...
Feb 04 2021 02:54 PM
By AB, did you mean the 28th column, or did you mean columns A and B?
Feb 04 2021 03:06 PM
@Hans Vogelaar i meant column A and B. Sorry if was not very clear. And Column C and D as well
Feb 04 2021 03:16 PM
Change the macro to
Sub CopyData()
Dim m As Long
Dim r As Long
m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
r = Worksheets("Sheet2").Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("C" & r)
Application.CutCopyMode = False
End Sub
Feb 04 2021 03:23 PM
@Hans Vogelaar it shows an error my friend at this line:
r = Worksheets("Sheet2").Range("C:D"). . .
I dont know, can you fix it?
Feb 04 2021 03:28 PM
Feb 04 2021 03:30 PM
SolutionThat happens if columns C and D are empty. See if this is better:
Sub CopyData()
Dim m As Long
Dim r As Long
m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error Resume Next
r = Worksheets("Sheet2").Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
If Err Then
r = 1
End If
On Error GoTo 0
Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("C" & r)
Application.CutCopyMode = False
End Sub
Feb 04 2021 03:37 PM
@Hans Vogelaar You are simply the BEST my friend ! You just saved my life. 1 billion thanks. Much love from Albania <3
Feb 05 2021 06:08 AM
@Hans Vogelaar Hey friend, one more question, how can i modify that code, so if column A & B are empty, not to show an error?
Feb 05 2021 07:05 AM
Like this:
Sub CopyData()
Dim m As Long
Dim r As Long
On Error Resume Next
m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If Err Then
MsgBox "There are no data to copy!", vbExclamation
Err.Clear
Exit Sub
End If
r = Worksheets("Sheet2").Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
If Err Then
r = 1
End If
On Error GoTo 0
Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("C" & r)
Application.CutCopyMode = False
End Sub
Feb 05 2021 02:33 PM - edited Feb 06 2021 05:37 AM
removed reply
Feb 05 2021 03:08 PM
That's hard to tell.
I'd single-step through the code and inspect the values of m10, r10, m11, r11 etc. to see if one of those has an unexpectedly large value.
Feb 05 2021 03:19 PM
Feb 05 2021 11:47 PM
When you single-step through the code by pressing F8 repeatedly, you can view the value of variables such as m11 by hovering the mouse pointer over them.
Perhaps you suddenly see a very large value. That would provide a clue...
Feb 06 2021 05:14 AM - edited Feb 06 2021 05:54 AM
@Hans Vogelaar So we have our code here. How can we modify this that: we have a text in Sheet1 cell C1, to be pasted in Sheet2 Column E, that times how long is column C and D that we pasted with data. like pasting drop-down.
Sub CopyData()
Dim m As Long
Dim r As Long
m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error Resume Next
r = Worksheets("Sheet2").Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
If Err Then
r = 1
End If
On Error GoTo 0
Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("C" & r)
Application.CutCopyMode = False
End Sub
visually:
column __A__B__C___ ...Sheet1
row1_____w__p__k__
row2_____w__p_____
row3_____w__p_____
..............................................
column__A__B__C__D__E Sheet2
row1____________w__p__k
row2____________w__p__k
row3____________w__p__k
(single step helped. i found it. and i have to do this, in order to remove that large data. Can you help please?)
Feb 06 2021 06:07 AM
I'm sorry, I don't understand this question.
Feb 06 2021 07:18 AM - edited Feb 06 2021 07:24 AM
@Hans Vogelaar Hmm ok, think of this:
We are in Sheet1 and we have data: in Column A and Column B and data in C1
what i need:
Count, Copy, Paste Column A and Column B from Sheet1 - to Sheet2 to Last Row in Column A and Column B
and
C1 from Sheet1 to be pasted to Column C Sheet2, and to be multiplied/drop-down-cell/that long, that column B has data.
Here is a photo:
Feb 06 2021 07:40 AM
You changed the question slightly: instead of pasting to columns C and D on Sheet2, you now want to paste to columns A and B.
Sub CopyData()
Dim m As Long
Dim r As Long
On Error Resume Next
m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If Err Then
MsgBox "No data to copy!", vbExclamation
Exit Sub
End If
r = Worksheets("Sheet2").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
If Err Then
r = 1
End If
On Error GoTo 0
Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("A" & r)
Worksheets("Sheet1").Range("C1").Copy Destination:=Worksheets("Sheet2").Range("C" & r).Resize(m)
Application.CutCopyMode = False
End Sub
Feb 04 2021 03:30 PM
SolutionThat happens if columns C and D are empty. See if this is better:
Sub CopyData()
Dim m As Long
Dim r As Long
m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error Resume Next
r = Worksheets("Sheet2").Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
If Err Then
r = 1
End If
On Error GoTo 0
Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("C" & r)
Application.CutCopyMode = False
End Sub