Copy Paste offset value Horizontally

[edwincwc]

i am new to VBA, I use following code to copy past offset Down is OK.
"Sub Copyoffsetdown()
Range("A1:A6").Copy
Range("h555").End(xlUp).Offset(0, 0).PasteSpecial Paste:=xlPasteValues
End Sub"

but
How to change the code , so i can copy rang(A1-A6) to H1-H6, then I1-I6, J1-J6 up to Z1 to Z6 horizontally etc ?
Thanks very your help in advance.

 

[Trevor G]

Re: Help Pls!Simple Copy Paste offset value Horizontally

You can add the additional cell ids into this code to paste them in.

Sub mcrpaste()
Range("A1:A6").Copy
Range("H1:H6,I1:I6,J1:J6").Select
Range("H1").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

 

[edwincwc]

Re: Help Pls!Simple Copy Paste offset value Horizontally

thanks, but value from A1-A6 changes, and i want to records of A1-A6 value , first at H1: H6, when A1-A6 changes new value, will be pasted on I1:I6 etc( keeping old value at H1:H6), up to AAA1:AAA6 or more. Offset feature is needed. thanks

 

[MickG]

Re: Help Pls!Simple Copy Paste offset value Horizontally

Try this:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:A6")
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsEmpty(Range("H1")) [COLOR="Navy"]Then[/COLOR]
       Rng.Copy Range("H1")
    [COLOR="Navy"]Else[/COLOR]
        Rng.Copy Cells(1, Lst + 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[edwincwc]

Re: Help Pls!Simple Copy Paste offset value Horizontally

Thanks Mick, sorry i m new to VBA, seems like your code not for Module, i put it under Sheet1, and dont know to do "activate" it without Run Marco. In short, is it possible to do simple Offset Marco Horizontally rather than Offset vertically? THanks

 

[MickG]

Re: Help Pls!Simple Copy Paste offset value Horizontally

No problem !!

To load and run code:-
Right click sheet "Tab", From menu select "View Code", vb window appears.
Pate code into Vb window , Close Vbwindow .

To run code change any value in range "A1:A6"
Each time you change the value a new list will appear, starting column "H"> "I"> "K" etc.

Hope that helps
Mick

 

[edwincwc]

Re: Help Pls!Simple Copy Paste offset value Horizontally

Great! it works! THanks very much! I m new to this forum and people here are Amazing and very helpful! Thanks again for your help!

 

[MickG]

Re: Help Pls!Simple Copy Paste offset value Horizontally

You're welcome

 

[edwincwc]

Re: Help Pls!Simple Copy Paste offset value Horizontally

Sorry, follow up question, yr code works well within Sheet1, but i tried to copy A1-A6 from Sheet1 to Sheet2 H1, not work, pls see my code

 Private Sub Worksheet_Change(ByVal Target As Range)Dim Rng As Range
Set Rng = Range("A1:A6")
Dim Lst As Long
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
If Not Intersect(Target, Rng) Is Nothing Then
   If IsEmpty(Sheets("Sheet2").Range("H1")) Then
   Rng.Copy (Sheets("Sheet2").Range("H1"))
   Else
   Rng.Copy (Sheets("Sheet2").Cells(1, Lst + 1))
    End If
End If
End Sub

How should i Modify this? THanks again

 

[MickG]

Re: Help Pls!Simple Copy Paste offset value Horizontally

Try this:-
Place this code in sheet1 module.
Results in sheet2

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Sheets("Sheet1").Range("A1:A6")
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
Lst = .Cells("1", Columns.Count).End(xlToLeft).Column
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsEmpty(.Range("H1")) [COLOR="Navy"]Then[/COLOR]
       Rng.Copy .Range("H1")
    [COLOR="Navy"]Else[/COLOR]
        Rng.Copy .Cells(1, Lst + 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick