Copy Paste Ranges - Listed in Column A

[dj44]

Hi Folks,

good monday

I've got stuck on this copy paste problem

I have put the ranges in column A that I want to Select

Select (A) Paste to (B)
A1:A10 ----- > D1
B10 :B20 -------> Q1


Column B Destination it should paste to

 Sub CopyPasteRanges()
 
 Dim owksht     As Worksheet
 Dim i      As Integer
 
 
  Set owksht = Worksheets("Sheet1")
  For i = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 
 
  ws.Cells(ws.Rows.Count, "A").Select
  Selection.Copy
    
  ws.Cells(ws.Rows.Count, "B").Paste

next i 
    
 End Sub

is this the right track?

can a pro advise as im not sure ive set it up correctly

[Paul_Hossler]

Do you mean that on Sheet1 you have

Cell A1 = "A1:A10"
Cell B1 = " D1"

Cell A2 = "B10 :B20"
Cell B2 = "Q1"

and you want to copy on THAT sheet?

Or from another sheet (say Sheet2) what's in A1:A10 to D10?

[dj44]

Hi Paul,

I have a lot of ranges to copy and paste and well it gets confusing so i thought i would list all the ranges to be copied in column A
as im forever selecting the worng cells to copy paste - so i got fed up and thought a macro can help

Cells paste.PNG

in column B where to paste to.

Same sheet should be fine

[Paul_Hossler]

Something like this maybe

Option Explicit
Sub CopyByAddress()
    Dim rCopyList As Range, rCopy As Range
    
    Set rCopyList = ActiveSheet.Cells(1, 1).CurrentRegion

    For Each rCopy In rCopyList.Rows
        With rCopy
            If Len(.Cells(1).Value) > 1 And Len(.Cells(2).Value) > 1 Then
                ActiveSheet.Range(.Cells(1).Value).Copy ActiveSheet.Range(.Cells(2).Value)
            End If
        End With
    Next

End Sub

[dj44]

Hi Paul,


In the interim I've been fiddling about with the .paste function, i thought i was on the right track with

ActiveSheet.Range(ws.Cells("i, "A").Value).Copy
ActiveSheet.Range(ws.Cells("i, "B").Value) .paste

but then the debugger wouldnt move oh dear.


thank you very much - your code does the job perfect awesome!

I really appreciate it.

The other problem was I overwrote some cells and that was really frustrating when i was manually copy and pasting,

thanks to the vba gurus i can have a nice monday now

nice one Paul

Good monday folks

[SamT]

Just to show you how to do it the way you started to

Option Explicit

Sub SamT_CopyPasteRanges()
    Dim i As Long
    Dim ws As Worksheet
     
    Set ws = Worksheets("Sheet1")
    
    For i = 1 To ws.Cells(Rows.Count, "A").End(xlUp).Row
        ws.Range(Cells(i, "A")).Copy Destination:=ws.Range(Cells(i, "B"))
    Next i
    
End Sub

Range(Cells(i, "A")) means "the range address contained in cells(i, "A")"

[dj44]

Hello Sam,

how did you know - you folks are psychic?

- Im a bit like a dog on a bone, i was just as we speak fiddling with the exact same thing - bit puzzled by my first attempt -
you beat me to it!

I missed the destination keyword completely oh well

I've been on the micorsoft vba reference and its like finding a needle in the haystack.


You know sometimes when you try to select cells excel goes off like theres no tomorrow and selects al the wrong cells


thanks for this new bit of code - so i have 2 now to make it into all sorts for my worksheet

thanks my friends Paul and Sam for all the help

good evening alll