Problem with SendKeys

[qwertytrewq]

I have a small vba code to copy data to another sheet - (data from SampleSheet1 {range(B17:W*) *is obtained from cell AA17} to SampleSheet2 {range(no change from last selected)}

    Dim Cval As Variant
    Dim Rng1 As Range
    Worksheets("SampleSheet1").Activate
    Cval = ActiveSheet.Range("AA17").Value + 16
    Set Rng1 = ActiveSheet.Range("B17:W" & Cval)
    Rng1.Copy
    Worksheets("SampleSheet2").Activate
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats       'point1:warning:
    Application.SendKeys "^{PGDN}", True
    Application.CutCopyMode = False
    Worksheets("SampleSheet1").Activate                  'point2:warning:
End Sub

The problem is command at 'point2 executes before the command at 'point1
i.e. the "Pagedown" key gets pressed at "SampleSheet1" and not on sheet "SampleSheet2"
also the macro results in weird "NumLock" off/on execution. i.e. the macro results in "NumLock" turning off if its on and viceversa.

Thanks in advance for giving this problem your precious time.

 

[RoryA]

Welcome to the forum.

Why do you need the Ctrl+PageDown in the code at all?

 

[daverunt]

Try Application.Wait to put a 1 or 2 second delay after P1 and before P2

Application.Wait (Now() + TimeValue("00:00:01"))

 

[qwertytrewq]

Welcome to the forum.

Why do you need the Ctrl+PageDown in the code at all?

my bad, there is no Ctrl in this formula, just {PGDN}...its a typo

I have data on say SampleSheet2 in page layout which is a filled with formulas and other variables, now i have setup this vba to copy this data without any formula or variable on SampeSheet1, each time i change value is changed on SampleSheet2 i use macro to copy the changed data on the next page, using PageDown lets me automatically select the first cell of next page where the next data gets pasted using macro button.
i have another formula where i use Ctrl+Down which lets me automatically select the last cell of data , below which the next data gets pasted using macro button.
The weird NumLock glitch is always present whenever i use sendkey, it doesn't depend on what key i send.

 

[RoryA]

You really shouldn't be using Sendkeys for that at all. You can do something like this instead:

    Dim Cval As Variant
    Dim Rng1 As Range
    Worksheets("SampleSheet1").Activate
    Cval = ActiveSheet.Range("AA17").Value + 16
    Set Rng1 = ActiveSheet.Range("B17:W" & Cval)
    Rng1.Copy
    With Worksheets("SampleSheet2")
        With .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
             .PasteSpecial Paste:=xlPasteValues
             .PasteSpecial Paste:=xlPasteFormats
        End With
    End WIth 
    Application.CutCopyMode = False
End Sub

Change the "A" part to whatever column you are pasting to.

 

[qwertytrewq]

Try Application.Wait to put a 1 or 2 second delay after P1 and before P2

Application.Wait (Now() + TimeValue("00:00:01"))

I did try this but it only results in freezing of excel for given time and executes "PageDown" command on returning back to SampleSheet1 only, the only way it executes the command on SampleSheet2 if i remove "point2" from code i.e. if i remove activation of SampleSheet1 from vba code.

Dim Cval As Variant
Dim Rng1 As Range
Worksheets("SampleSheet1").Activate
Cval = ActiveSheet.Range("AA17").Value + 16
Set Rng1 = ActiveSheet.Range("B17:W" & Cval)
Rng1.Copy
Worksheets("SampleSheet2").Activate
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats 'point1
Application.SendKeys "^{PGDN}", True
Application.CutCopyMode = False
Application.Wait (Now() + TimeValue("00:00:01")) 'point1 still executes after point 2
Worksheets("SampleSheet1").Activate 'point2

 

[qwertytrewq]

Thanks a lot , it did wonders. Saved me a lota time.
Just one more question ,since i am new to vba i dont know how to combine say two of these codes together and use it using 1 macro button

Sub Code1()
 Dim Cval1 As Variant
    Dim Rng1 As Range
    Worksheets("SampleSheet1").Activate
    Cval1 = ActiveSheet.Range("AA17").Value + 16
    Set Rng1 = ActiveSheet.Range("B17:W" & Cval1)
    Rng1.Copy
    With Worksheets("SampleSheet2")
        With .Cells(.Rows.Count, [COLOR=#0000FF][B]"[/B]B[B]"[/B][/COLOR]).End(xlUp).Offset(1)
             .PasteSpecial Paste:=xlPasteValues
             .PasteSpecial Paste:=xlPasteFormats
        End With
    End WIth 
    Application.CutCopyMode = False
End Sub


Sub Code2()

Dim Cval2 As Variant
Dim Rng2 As Range
Worksheets("SampleSheet3").Activate
Cval = ActiveSheet.Range("A1").Value
Set Rng2 = ActiveSheet.Range("B1:W" & Cval2)
Rng2.Copy
With Worksheets("SampleSheet2")
With .Cells(.Rows.Count, [COLOR=#0000FF]"B"[/COLOR]).End(xlUp).Offset(1)
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With
End WIth 
Application.CutCopyMode = False
End Sub

 

[RoryA]

You can just add another macro that runs them both like this:

Sub Newmacro()
   Code1
   Code2
End Sub