Vba copy and pasting

Sayf1

New Member
Joined
Jul 17, 2018
Messages
8
I have column A with 22 rows in sheet 1 but there are spaces between these accounts and 27 rows in column c in sheet 2 als with spaces between them. I need to copy and paste the range in column A to sheet 3 and when the copy paste ends it skips a cells and then pasted the data in column C under it. They are overwriting each other and I can’t seem to identify that last cell . please help! Just want to stack a lot of copy pasta under each other in the same column.

Thank you you in advance! Really appreciate it !
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Code:
Sub CopyAndPaste()
  Dim wksSource1 As Worksheet
  Dim wksSource2 As Worksheet
  Dim wksDestination As Worksheet
  Dim lngLastRow1 As Long
  Dim lngLastRow2 As Long
  Dim lngPasteRow As Long
  Dim rngSource1 As Range
  Dim rngSource2 As Range
  
  Set wksSource1 = ThisWorkbook.Worksheets("Sheet1") ' Set 1st source sheet (to copy from)
  Set wksSource2 = ThisWorkbook.Worksheets("Sheet2") ' Set 2nd source sheet (to copy from)
  Set wksDestination = ThisWorkbook.Worksheets("Sheet3") ' Set destination sheet (to paste into)
  
' Find last row of data in each source sheet
  lngLastRow1 = wksSource1.Cells(wksSource1.Rows.Count, "A").End(xlUp).Row
  lngLastRow2 = wksSource2.Cells(wksSource2.Rows.Count, "C").End(xlUp).Row
  
' Set reference to each source range
  Set rngSource1 = wksSource1.Range("A1:A" & lngLastRow1)
  Set rngSource2 = wksSource2.Range("C1:C" & lngLastRow2)
  
' Copy 1st source range
' Paste into row 1 of destination sheet
  lngPasteRow = 1
  rngSource1.Copy Destination:=wksDestination.Cells(lngPasteRow, "A")
  
' Copy 2nd source range
' Paste into next row of destination sheet
  lngPasteRow = rngSource1.Count + 1
  rngSource2.Copy Destination:=wksDestination.Cells(lngPasteRow, "A")
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top