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
 
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

Everything looks it was selecting the cells but didn't want to paste anything into the PV LISTING sheet
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

It looks like it's running through but nothing is pasting into the my PV LIST sheet.
 
Upvote 0
Oops, it should be
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)).Copy ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      Next i
   End With
End Sub
I just had the Select for testing & forgot to remove it.
 
Upvote 0
Oops, it should be
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)).Copy ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      Next i
   End With
End Sub
I just had the Select for testing & forgot to remove it.

Now it's not selecting or copying anything. What could be the issue?
Thank you for the help!
 
Upvote 0
Do you have any data in row 2?
 
Upvote 0
It looks like it's running through but nothing is pasting into the my PV LIST sheet.
You can put two images, one of each sheet, where we can see the rows and columns of the sheet and the data you have on the sheets.
 
Upvote 0
Do you have any data in row 2?
Here is a screenshot of my script:
1581436523557.png


Here is what my PV LIST looks like:
1581436558419.png
 
Upvote 0
If you select A2 on the PV List sheet & do Ctrl & down arrow, where do you endup?
 
Upvote 0
If you select A2 on the PV List sheet & do Ctrl & down arrow, where do you endup?
oh wow,
It looks like it's pasting at line 127.
Is that because information has been filled previously on the above lines?
I'd have to hard delete and not clear contents to remove?

(EDIT) Looks like that was the issue! Thank you so much for the help!
Are you able to explain to me what each line of code does?
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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