Macro Recorder into VBA, Copy Paste Data Filled Cells

beginvbaanalyst

Board Regular
Joined
Jan 28, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
How about
VBA Code:
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.
 
Upvote 0
The following macro runs through all the columns, assuming you have headings in row 1 on LIST sheet in all columns.

VBA Code:
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
 
Upvote 0
How about
VBA Code:
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!
 
Upvote 0
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
 
Upvote 0
The following macro runs through all the columns, assuming you have headings in row 1 on LIST sheet in all columns.

VBA Code:
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.
 
Upvote 0
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?
 
Upvote 0
How about
VBA Code:
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
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

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