PDA

View Full Version : [SOLVED] Copy Paste Ranges - Listed in Column A



dj44
09-12-2016, 07:34 AM
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
09-12-2016, 08:14 AM
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 D1:D0?

dj44
09-12-2016, 08:37 AM
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

17068

in column B where to paste to.

Same sheet should be fine

Paul_Hossler
09-12-2016, 10:03 AM
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
09-12-2016, 10:39 AM
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.:dau:


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

Good monday folks

SamT
09-12-2016, 04:15 PM
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
09-12-2016, 04:37 PM
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 :eek:

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

:)