0

I need to copy data from one sheet to another with a limited selection of columns using VBA, not continuous and transpose the copied data in a column while pasting to another sheet. Also, I want to skip the empty cells while doing so.

I want to apply a loop but I am not able to declare the ranges of cells exactly as they should be. I am very new to VBA and below is the code which I am using trying to achieve the goal.

    Option Explicit
Sub CopyPasteLoop()

    Dim X As Long
    Dim Y As Long
    Dim Col As Long
    Dim row1 As Long
    'Dim A As Long 

    Col = 1
    Sheets("Copy").Activate

    'For A = 1 To 10000    

    row1 = Sheets("Copy").Range(.Cells(.Rows.Count, Col)).End(xlUp).row

    Sheets("Key Entry Data").Activate
    X = Sheet2.Range("A" & Rows.Count).End(xlUp).row
    'Y = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column

    Sheets("Copy").Activate
    Sheet1.Range("Col" & 2, "Col" & row1).Select
    Selection.Copy

    'X = X + 1

    Sheets("Key Entry Data").Activate
    Sheet2.Cells(X).Select
    Sheet2.Range("A" & X).PasteSpecial xlPasteValues

    Col = ActiveCell.Next.EntireColumn.Cells(1).Select

    'Next X

End Sub
riskypenguin
  • 2,139
  • 1
  • 10
  • 22
Abhishek
  • 1
  • 1

2 Answers2

0

In general you should avoid using .Acitvate and .Select as described here. In your code you can completely leave out those parts. This and the unqualified ranges (as mentioned in the comments) are most likely the cause of your problems. Here is your corrected code:

Option Explicit

Sub CopyPasteLoop()

Dim X As Long
Dim Y As Long
Dim Col As Long
Dim row1 As Long
'Dim A As Long

Col = 1
'For A = 1 To 10000
row1 = Sheets("Copy").Cells(Rows.Count, Col).End(xlUp).Row

X = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
'Y = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column

Sheet1.Range(Sheet1.Cells(2, Col), Sheet1.Cells(row1, Col)).Copy Sheet2.Range("A" & X)
'X = X + 1
'Next X
End Sub

Please note that the For loop you commented out will not work. As it's unclear from your question what that loop is supposed to achieve I'm not able to correct it to what exactly you're trying to do. In general, you don't need X = X + 1 inside a For loop (it will skip every second integer this way), as the For ... To statement takes care of that.

riskypenguin
  • 2,139
  • 1
  • 10
  • 22
0

Thank you for sharing this M.Schalk, I am using the below code to copy the data to another sheet. Can you look at the code and share an effective one with me?

Option Explicit Sub EmailIDCopy()

Dim X As Long 'X is the value of Row
Dim Y As Long
Dim Col As Long 'Col is used to column of Sheet2
Dim row1 As Long 'row1 is currently being used for defining the last row of column
Dim M As Long

    Col = 1
    Sheets("Copy").Activate

    For M = 1 To 5

    row1 = Sheets("Copy").Cells(Rows.Count, Col).End(xlUp).row
        Sheets("Key Entry Data").Activate
            X = Sheet2.Range("A" & Rows.Count).End(xlUp).row

    Sheets("Copy").Activate
        Sheet1.Range(Cells(2, Col), Cells(row1, Col)).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Copy


    X = X + 1
    Sheets("Key Entry Data").Activate
        Sheet2.Cells(X).Select
            Sheet2.Range("A" & X).PasteSpecial xlPasteValues

    Col = Col + 2
    Next M

End Sub

thank you.

Abhishek
  • 1
  • 1