View Full Version : [SOLVED:] Copy Paste Ranges - Listed in Column A
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?
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
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
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")"
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
:)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.