Whats wrong with this code?

[Phoenix_Turn]

Hi all,

This is my code so far, but it is not working:

Dim strE As String
Dim rngE As Range
Dim intE As Integer
Dim strA As String
Dim rngCell As Range, rngA As Range
Dim wksheet As Worksheet, i As Integer

'this becomes the active cell
Application.ActiveWorkbook.Worksheets("Sheet2").Activate
Application.ActiveWorkbook.Worksheets("Sheet2").Range("a1").Select

Set rngCell = Application.ActiveWorkbook.Worksheets("Sheet1").Range("a1")
Set wksheet = Application.ActiveWorkbook.Worksheets("Sheet1")

Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

'For Each rngA In rngCell.Columns(1).Cells
If IsEmpty(ActiveCell) Then
ActiveCell.Value = rngCell
End If
'Next rngA

Now my main purpose is like this:

-Sheet 1, column A has a column of values
-Transfer those values to the first empty cell in row A in sheet 2, it below existing data in sheet2

-sheet 1 column B has a column of values
-Transfer those values to the first empty cell in row B in sheet 2, that is it is below the existing data in sheet2 column b

etc...

this can be done using a loop structure and an activecell, currentregion, resize format offsett function or anything that is simple.

Thanks guys! I appreciate your help

 

[VoG]

No need to loop

Sub test()
With Sheets("Sheet1")
    .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Range(.Range("B1"), .Range("B" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

 

[kpark91]

    Dim LRA As Long, LRB As Long, countA As Long, countB As Long, count As Long
    
    'MAKE sure you're dealing with sheet1 and sheet2 in VBA, which can be found on the left pane of the VBA. It is not in the parenthesis
    LRA = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    LRB = Sheet2.Range("B" & Rows.Count).End(xlUp).Row
    countA = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    countB = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
    count = 1
 
    For i = LRA + 1 To countA + LRA + 1
        Sheet2.Range("A" & i).Value = Sheet1.Range("A" & count).Value
        count = count + 1
    Next i
    
    count = 1
    
    For i = LRB + 1 To countB + LRB + 1
         Sheet2.Range("B" & i).Value = Sheet1.Range("B" & count).Value
        count = count + 1
    Next i

 

[Phoenix_Turn]

No need to loop

Sub test()
With Sheets("Sheet1")
    .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Range(.Range("B1"), .Range("B" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

Thanks VoG!

However im a beginner at this so im not too sure wat is being written here.

But if a loop is possible could u specify it for me or make changes to my one above?

Thanks!

 

[Phoenix_Turn]

    Dim LRA As Long, LRB As Long, countA As Long, countB As Long, count As Long
    
    'MAKE sure you're dealing with sheet1 and sheet2 in VBA, which can be found on the left pane of the VBA. It is not in the parenthesis
    LRA = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    LRB = Sheet2.Range("B" & Rows.Count).End(xlUp).Row
    countA = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    countB = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
    count = 1
 
    For i = LRA + 1 To countA + LRA + 1
        Sheet2.Range("A" & i).Value = Sheet1.Range("A" & count).Value
        count = count + 1
    Next i
    
    count = 1
    
    For i = LRB + 1 To countB + LRB + 1
         Sheet2.Range("B" & i).Value = Sheet1.Range("B" & count).Value
        count = count + 1
    Next i

thanks kpark, im looking for something like variables which consists of strings, integers, ranges and stuff. Not really pointing at "long" and etc.

Thanks for the reply though!

 

[VoG]

There really is no need to loop.

That copies from Sheet1 A1 to the last filled row in column A and pastes to the first free row in Sheet2 column A. Similarly for column B.

 

[Phoenix_Turn]

There really is no need to loop.

That copies from Sheet1 A1 to the last filled row in column A and pastes to the first free row in Sheet2 column A. Similarly for column B.

Yep i know, but i wanna get the basics of "loops" first.

Sorry for the trouble, but thanks for your reply!

And a heads up, the code was written in a way that the procedure is executed once a button is clicked.

 

[VoG]

Try

Sub test2()
Dim LR1 As Long, LR2 As Long, i As Long, j As Long
With Sheets("Sheet1")
    LR1 = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR1
        .Range("A" & i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
    LR2 = .Range("B" & Rows.Count).End(xlUp).Row
    For j = 1 To LR2
        .Range("B" & j).Copy Destination:=Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)
    Next j
End With
End Sub

 

[Phoenix_Turn]

Try

Sub test2()
Dim LR1 As Long, LR2 As Long, i As Long, j As Long
With Sheets("Sheet1")
    LR1 = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR1
        .Range("A" & i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
    LR2 = .Range("B" & Rows.Count).End(xlUp).Row
    For j = 1 To LR2
        .Range("B" & j).Copy Destination:=Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)
    Next j
End With
End Sub

thanks!

I forgot to mention that i need to build my procedure from the ground up. So thats why i dont understand what is being written above.

If you could give me the written up procedure rather than the built in procedure i appreciate it!

Thanks again Vog!

 

[VoG]

Does this help?

Sub test2()
Dim LR1 As Long, LR2 As Long, i As Long, j As Long
With Sheets("Sheet1")
    LR1 = .Range("A" & Rows.Count).End(xlUp).Row 'last filled row in col A
    For i = 1 To LR1 'loop from A1 to last filled cell in column A
        .Range("A" & i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) ' copy to end of column A
    Next i
    LR2 = .Range("B" & Rows.Count).End(xlUp).Row
    For j = 1 To LR2
        .Range("B" & j).Copy Destination:=Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)
    Next j
End With
End Sub