Macro Recorder into VBA, Copy Paste Data Filled Cells

[beginvbaanalyst]

Hi Everyone,

I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2")
The issue is my range will always differ for my data that I copy.
I have data sets in every 4 columns ("A:D"), ("E:H") and so on but I just want the data from ("A:B"), ("E:F") to copy into my new sheet ("A2:B2") without overwriting data.
My Macro recorder code is provided below:
Sub Testcopypaste()
'
' Testcopypaste Macro
'

'
Range("A2:B27").Select 'Selection of range ("A2:B27")
Selection.Copy 'Copy Selection Above ^
Sheets("PV LIST").Select 'Make "PV LIST" Active
Range("A2").Select 'Select Cell A2 on Tab PV LIST
ActiveSheet.Paste 'Paste selection ("A2:B27") into ("A2")
Sheets("LIST").Select 'Make Sheet "LIST" active
Range("E2:F24").Select 'Select Range ("E2:F24")
Selection.Copy 'Copy Selected Range ^
Sheets("PV LIST").Select 'Make Sheet "PV LIST" Active
Range("A28").Select 'Select Cell ("A28")
ActiveSheet.Paste 'Paste Range ("E2:F24") into cell ("A28")
Sheets("LIST").Select 'Make Sheet "LIST" active
Range("I2:J30").Select 'Select Range ("I2:J30")
Selection.Copy 'Copy Selected Range ^
Sheets("PV LIST").Select 'Make Sheet "PV LIST" active
Range("A51").Select 'Select Cell ("A51")
ActiveSheet.Paste 'Paste selected Range ("I2:J30")
Sheets("LIST").Select 'Make Sheet "LIST" Active
Range("M2:N21").Select 'Select Range[Cells] ("M2:N21")
Selection.Copy 'Copy Selected Range[Cells] above ^
Sheets("PV LIST").Select 'Make Sheet "PV LIST" Active
Range("A80").Select 'Select Cell ("A80")
ActiveSheet.Paste 'Paste Selected Range ("M2:N21")
End Sub

 

[Fluff]

How about

Sub Beginvba()
   Dim Ws As Worksheet
   
   Set Ws = Sheets("PV list")
   With Sheets("List")
      .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Range("E2:F" & .Range("E" & Rows.Count).End(xlUp).Row).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Range("I2:J" & .Range("I" & Rows.Count).End(xlUp).Row).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Range("M2:N" & .Range("M" & Rows.Count).End(xlUp).Row).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
   End With
End Sub

Also when posting code, please use code tags the <vba/> icon in the reply window.

 

[DanteAmor]

The following macro runs through all the columns, assuming you have headings in row 1 on LIST sheet in all columns.

Sub Testcopypaste()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long, lr1 As Long, lr2 As Long
  
  Set sh1 = Sheets("List")
  Set sh2 = Sheets("PV LIST")
  For i = 1 To sh1.Cells(1, Columns.Count).End(xlToLeft).Column Step 4
    lr1 = sh1.Range(sh1.Cells(2, i), sh1.Cells(Rows.Count, i + 1)).Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lr2 = sh2.UsedRange.Rows(sh2.UsedRange.Rows.Count).Row + 1
    sh1.Range(sh1.Cells(2, i), sh1.Cells(lr1, i + 1)).Copy sh2.Range("A" & lr2)
  Next
End Sub

 

[beginvbaanalyst]

How about

Sub Beginvba()
   Dim Ws As Worksheet
  
   Set Ws = Sheets("PV list")
   With Sheets("List")
      .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Range("E2:F" & .Range("E" & Rows.Count).End(xlUp).Row).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Range("I2:J" & .Range("I" & Rows.Count).End(xlUp).Row).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Range("M2:N" & .Range("M" & Rows.Count).End(xlUp).Row).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
   End With
End Sub

Also when posting code, please use code tags the <vba/> icon in the reply window.

Thank you SO much! This works perfectly.
Are you able to explain to me which each line means? I would love to understand better.
.Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
^What does this mean? Populated data in cells "A2:B"?
^What does the second set of range do and why isn't it Column B instead of A?
^ I understand the copy range but what does the offset(1) do?

I would love to be able to set the code to where it reads everything first and second column of every populated data(4 columns total, would read the first two) but stop when there is nothing filled. The range would always be different.
Thank you so much for your help!

 

[Fluff]

This .Range("A" & Rows.Count).End(xlUp).Row returns the row number for the last used cell in col A
Which means this .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Copy becomes (for instance) .Range("A2:B" & 27).Copy or just .Range("A2:B27").copy

The final Offset(1) simply comes down 1 cell

 

[DanteAmor]

I would love to be able to set the code to where it reads everything first and second column of every populated data

Did you try the macro of post #3?

 

[beginvbaanalyst]

The following macro runs through all the columns, assuming you have headings in row 1 on LIST sheet in all columns.

Sub Testcopypaste()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long, lr1 As Long, lr2 As Long
 
  Set sh1 = Sheets("List")
  Set sh2 = Sheets("PV LIST")
  For i = 1 To sh1.Cells(1, Columns.Count).End(xlToLeft).Column Step 4
    lr1 = sh1.Range(sh1.Cells(2, i), sh1.Cells(Rows.Count, i + 1)).Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lr2 = sh2.UsedRange.Rows(sh2.UsedRange.Rows.Count).Row + 1
    sh1.Range(sh1.Cells(2, i), sh1.Cells(lr1, i + 1)).Copy sh2.Range("A" & lr2)
  Next
End Sub

The code is running but it's not copying and pasting anything.
I don't fully understand what's going on.

 

[beginvbaanalyst]

This .Range("A" & Rows.Count).End(xlUp).Row returns the row number for the last used cell in col A
Which means this .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Copy becomes (for instance) .Range("A2:B" & 27).Copy or just .Range("A2:B27").copy

The final Offset(1) simply comes down 1 cell

Thank you for explaining to me the information provided.
Is there a way to continue this code until populated data is no longer there?

 

[Fluff]

How about

Sub Beginvba()
   Dim ws As Worksheet
   Dim i As Long
   
   Set ws = Sheets("PV list")
   With Sheets("List")
      For i = 1 To .Cells(2, Columns.Count).End(xlToLeft).Column Step 4
         .Range(.Cells(2, i), .Cells(Rows.Count, i).End(xlUp).Offset(, 1)).Select '.Copy ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      Next i
   End With
End Sub

 

[DanteAmor]

The code is running but it's not copying and pasting anything.
I don't fully understand what's going on.

You have no headings in row 1?
Then try the following:

Sub Testcopypaste()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long, lr1 As Long, lr2 As Long
  
  Set sh1 = Sheets("List")
  Set sh2 = Sheets("PV LIST")
  For i = 1 To sh1.Cells(2, Columns.Count).End(xlToLeft).Column Step 4
    lr1 = sh1.Range(sh1.Cells(2, i), sh1.Cells(Rows.Count, i + 1)).Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lr2 = sh2.UsedRange.Rows(sh2.UsedRange.Rows.Count).Row + 1
    sh1.Range(sh1.Cells(2, i), sh1.Cells(lr1, i + 1)).Copy sh2.Range("A" & lr2)
  Next
End Sub