Need to insert 2 columns from one sheet to another using vba

This is my code for copy-paste 2 columns from one sheet to another.. In which it should be pasted somewhere inside the existing table. Pls help me


For j = 2 To (4 + ((Sheets.Count – SheetCount) * 2))
MsgBox Sheets.Count

If Cells(3, j).Value = “Total” Then
Selection.Insert Shift:=xlRight

ActiveSheet.Cells(3, j) = WKSheetName
Exit For
End If
Next j

Top Contributor Asked on December 12, 2017 in VBA: Macros.
Add Comment
1 Answer(s)

Sheet1 column “A”  must have at least 2 rows (Cells A1 and A2) of data for macro to work. Less than that will msgbox an error.

Sheet 2 contains a Table1 located in ” A1″  with a header of  4 columns and 2 rows including the header row.

Data on sheet1 columns “A” and “B” will be moved to Sheet2 Table1 columns “C” and “D”.

Sheet1 will also be cleaned of data in columns “A” and “B”.

Good Luck!

Sub copycols2table()
Dim x As Integer
Dim c As Integer
Dim n As Integer
Dim r As Integer
Dim rc As Integer
Dim ws2 As Worksheet

x = 1
c = 3
n = 2
r = 1
rc = 1
Set ws2 = Worksheets(“Sheet2”)

For i = 1 To 2
If IsEmpty(Cells(r, rc)) Then
MsgBox “Please enter minimum of 2 rows of data on Sheet1 columns A.”
Exit Sub

End If
r = r + 1
Next i
r = 1
rc = 2
If IsEmpty(Cells(r, rc)) Then
n = 1
End If

For i = 1 To n
With ws2
Range(Cells(1, x), Cells(1, x).End(xlDown)).Copy _
Destination:=ws2.Cells(1, c).End(xlDown).Offset(1)
End With
x = 2
c = 4
Next i

End Sub

Expert Answered on December 30, 2017.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.