Selecting the active cell (VBA question) (1 Viewer)

[laxster]

Hi all,

I'm trying to paste information from one sheet to another via macros, then have the first empty cell below the data insertion point to be selected. C7 is where the data set I'm working with starts, but currently the code just copies over what I've already got instead of adding it to the end.

Any idea on what I'm missing in my code?

    Sheets("Entry").Select
    Range("c7").Select
        SendKeys ("{End}")
        SendKeys ("{Down}")
        SendKeys ("{Down}")
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[Brianwarnock]

I'm surprised that you didn't get an run time error message, don't you need a Copy statement?

Brian

 

[laxster]

I only posted a portion of the code. There is indeed a copy statement and that part works quite well. If it's more helpful, here's the entire code instead of just the portion I was seeking assistance with:

    Sheets("Manipulations").Select
    Range("A3:B67").Select
    Selection.Copy
    Sheets("Manipulations2").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("Entry").Select
    Range("c7").Select
        SendKeys ("{End}")
        SendKeys ("{Down}")
        SendKeys ("{Down}")
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[Brianwarnock]

Yes that helps, you say that it starts in C7 so presumably there can be many more rows, therefore you need to find the last row eg
Dim lastrow as integer

lastrow = Sheets("sheetname").Range("A65536").End(xlUp).Row

the A of Range should be a column where you know that there will be data.


Brian

 

[laxster]

Well, it now brings it to that spot which is perfect! But when I execute the code again, it should paste it in that bottom activated cell -- so the list would grow larger as more is copied. It just seems to copy directly over instead of the appropriate activated cell. I suspect I mangled something a bit..

    Sheets("Manipulations").Select
    Range("A3:B67").Select
    Selection.Copy
    Sheets("Manipulations2").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Sheets("Entry").Select
    Range("c7").Select
        SendKeys ("{End}")
        SendKeys ("{Down}")
        SendKeys ("{Down}")
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Dim lastrow As Integer
    lastrow = Sheets("Entry").Range("C65536").End(xlUp).Row

 

[Brianwarnock]

OOPs sorry if I didn't, which I didn't, complete the explanation, you need to select the cell in that row +1

Sheets("sheetname").Cells(lastrow + 1, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Brian

 

[laxster]

I'm pretty close, but the next time I execute the code it doesn't paste to the newly selected Cell, but rather up to C7 again.

    Sheets("Entry").Select
    Range("c7").Select
        SendKeys ("{End}")
        SendKeys ("{Down}")
        SendKeys ("{Down}")
    ActiveCell.Select
    Dim lastrow As Integer
    lastrow = Sheets("Entry").Range("C65536").End(xlUp).Row
    Sheets("Entry").Cells(lastrow + 1, 3).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[Brianwarnock]

But this

Sheets("Entry").Cells(lastrow + 1, 3).Select

should select column C in the lastrow+1

Can you post the spreadsheet? But I must warn you that I am unavailable all day tomorrow - walking then watching England's match - the pleasure then the pain

Brian

 

[Brianwarnock]

Having looked at this code again

Sheets("Manipulations").Select
    Range("A3:B67").Select
    Selection.Copy
    Sheets("Manipulations2").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Sheets("Entry").Select
    Range("c7").Select
        SendKeys ("{End}")
        SendKeys ("{Down}")
        SendKeys ("{Down}")
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Dim lastrow As Integer
    lastrow = Sheets("Entry").Range("C65536").End(xlUp).Row

It looks as though you were tryng to copy range ("A3:B67") from sheet manipulations to sheet Entry starting in C10.

If the code you were working with was from a recorded macro please state what it is you are really trying to do. Recording macros can be a useful way to start but they always use static references I doubt that that is what is required.

Brian

 

[laxster]

No, this one isn't a recorded macro. B67 is simply what the dataset runs through for the number of bins at this particular plant location. Below that are various other calculations and cells, and I don't want to copy/analyze anything below that point.

The

    Range("c7").Select
        SendKeys ("{End}")
        SendKeys ("{Down}")
        SendKeys ("{Down}")

was my way of telling the sheet to start at the first empty cell for copying, since the Entry sheet will act as a log of sorts each time this is run so we can keep track of when bins go empty.

Any thoughts on simply being able to add the new data to the end of the set? I'm not quite in understanding why this doesn't work, since the proper cell does appear to be selected when the code is all run.

 

[Brianwarnock]

Any thoughts on simply being able to add the new data to the end of the set? .

That is what the code using Lastrow is for.

I've never used Sendkeys but a test shows that it does not change the activecell from C7.
Why C7?

This with Sheetnames and the range smaller to save typing works for what I thought you wanted.

Sub laxster()
Dim lastrow As Integer

Application.ScreenUpdating = False

Sheets("sheet1").Select
    Range("A3:B7").Select
    Selection.Copy
    Sheets("sheet2").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Sheets("sheet3").Select
    
     lastrow = Sheets("sheet3").Range("C65536").End(xlUp).Row
     Sheets("sheet3").Cells(lastrow + 2, 3).Select  ' start in col C
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub