Macro Recorder into VBA, Copy Paste Data Filled Cells

beginvbaanalyst

New Member
Joined
Jan 28, 2020
Messages
49
Office Version
365
Platform
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,825
Office Version
365
Platform
Windows
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,698
Office Version
2007
Platform
Windows
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
 

beginvbaanalyst

New Member
Joined
Jan 28, 2020
Messages
49
Office Version
365
Platform
Windows
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,825
Office Version
365
Platform
Windows
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
 

beginvbaanalyst

New Member
Joined
Jan 28, 2020
Messages
49
Office Version
365
Platform
Windows
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.
 

beginvbaanalyst

New Member
Joined
Jan 28, 2020
Messages
49
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,825
Office Version
365
Platform
Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,698
Office Version
2007
Platform
Windows
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
 

Forum statistics

Threads
1,085,757
Messages
5,385,706
Members
401,967
Latest member
Sullivag2

Some videos you may like

This Week's Hot Topics

Top