macro Solved/Closed

Question

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/05/2013 by dhale
'
' Keyboard Shortcut: Ctrl+a
'
    Application.Goto Reference:="onecell"
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-27
    Range("E10").Select
    ActiveSheet.Paste

End Sub
This macro is supposed to copy informaiton from the named range to the current active cell but it keeps returning to the original cell where the macro was recorded.
What is missing from the macro?  I take it it is a command that says something like Current Active Cell

sgmpatnaik · Answer

Hi

Try the below code which is copy the range from a particular sheet and paste in the second sheet of last cell

Sub TransferData()
Dim LastRow As Integer

'Where is the last cell with data?
LastRow = Worksheets("Sheet2").Range("A65536").End(xlUp).Row

'Transfer data
Sheets("Sheet1").Range("A1:C7").Copy Worksheets("Sheet2").Cells(LastRow + 1, "A")
End Sub

Hope it will solve your problem otherwise please inform us

Thanks

Patnaik

TrowaD · Answer

Hi Phillip,

You're right.
Change 
Range("E10").Select
into
ActiveCell.Select

Best regards,
Trowa

Phillip Kabala · Answer

MAy be an easier way to do what I am attempting.  I have a box made of 4 cells making a square.

I want to create 5 different macros so that the first shades the Active Cell as follows:
.ColorIndex = 8
        .Pattern = xlSolid
    End With

The second to fill the active cell and one to the R.
The third to fill the active and one to the R and one below
The fourth to fill all 4 cells 
The fifth to fill all four cells but as follows
  .ColorIndex = 6
        .Pattern = xlSolid
    End With

In every case I want the user to just activate the top left cell they want filled in and the macro to do the rest.

Am I stretching the friendship to ask how I would achieve this???

TrowaD · Answer

Not at all Phillip,

I just have some questions.

1. I'm assuming the box of 4 cells have a fixed location. What is it?

2. Users activate the top left cell. You mean of the sheet (=A1) or of the 4 cells box?

3. You want to run the macro when users activate the top left cell. But which of the 5 macro do you run? This could be dependent of the cell entry, so 5 different condition are needed.

Phillip Kabala · Answer

Can I attach a doc to thi.  It would be easier for you to understand if you could see it.

TrowaD · Answer

I totally agree with you.

With this filesharing site you can upload your file and then post back the download link:
www.speedyshare.com

Do share your thoughts on point 2 and 3 from my previous post.

Awaiting your reply.

Macro

6 responses

Similar discussions

Subscribe To Our Newsletter!