VBA Copy-paste values with changing paste location

**booost** · 02-14-2012, 09:15 AM

Hi,

I have a range from A2:T20 that needs to be copied, then pasted according to value in A1 (and then multiplied by 20).
So if value in A1 is 2, then 2*20 = 40. Add an A before it, to get A40. That is where it should be pasted. But in a different sheet!

I need to run this script multiple times (every time A1 changes values). Or just manually. I need to run the script about 2,000 times, so its not too bad manually activating it
by some keystroke. But if someone can make it activate every time A1 changes, it also needs to have some delay to make sure the data has changed, so its not copying the same thing twice.

Any help would be greatly appreciated.

edit: also before anyone says anything, I realize I'm just asking for the script. I should really check the rules and see if that is allowed. Maybe I should have asked a specific question instead. Its just that I know so little about VBA that I have nothing specific to ask without doing a lot of research first. Which I really should do since I always feel bad about asking in these forums.

**Pichingualas** · 02-14-2012, 09:45 AM

Ok, two questions first.
If the value of A1 is changed but lets say the A40 cell of your example is already occupied, do you want it to paste the info anyway, or should it ask for confirmation or something?
Second, is the name of the sheet where the data should be copied to always the same, meaning the same sheet?

**booost** · 02-14-2012, 09:49 AM

Originally Posted by Pichingualas

Ok, two questions first.
If the value of A1 is changed but lets say the A40 cell of your example is already occupied, do you want it to paste the info anyway, or should it ask for confirmation or something?
Second, is the name of the sheet where the data should be copied to always the same, meaning the same sheet?

Hi and thanks for the reply!

1. Paste it no matter what, since it should always be empty. So just shorter/cleaner code

2. Always the same sheet. Always copying from sheet1 and pasting every time to sheet2.

**booost** · 02-14-2012, 10:24 AM

Uhm Pichingualas is now offline, it may have just auto signed him off but I doubt it would have taken so long to help me with what he was planning to help with. And I fear he may have stopped others from helping since he kinda said he would answer. Can anyone else help?

**booost** · 02-14-2012, 11:14 AM

So far I have:

Range("A2:T20").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste

Just need to get the worksheet names in there and change A1 into some kind of formula

edit: A1 formula doesn't need to be in the VBA macro. In B1 I have multiplied it times 20 and in C1 I have added an A in front. (probably could have done it all in one formula but whatever). Now I just need to use an indirect function in the VBA macro so it takes the text (like: "A300") and applies it to a cell location.

Anyone know how I can replace Range("A1") to indirect C1 ?
I have no clue how to set this up.

**Pichingualas** · 02-16-2012, 10:39 AM

Sorry for the delay.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim val As Integer
    
    Application.ScreenUpdating = False
    
    If Target.Column = 1 And Target.Row = 1 Then
        On Error Resume Next
        val = Target.Value
        On Error GoTo 0
        If val > 0 Then
            Sheets("SourceDataSheetName").Range("A2:T20").Copy
            Sheets("SheetToCopyToName").Range("A" & CStr(val * 20)).PasteSpecial (xlPasteAll)
            Application.CutCopyMode = False
        End If
    Else
        Exit Sub
    End If
End Sub

I hope that helps.

You have to paste this code on the corresponding sheet. Check the Example with the working macro.

Example.xlsm