Macro

Solved/Closed
Report
Posts
3
Registration date
Monday May 13, 2013
Status
Member
Last seen
May 14, 2013
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
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

6 replies

Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
44
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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Phillip,

You're right.
Change
Range("E10").Select
into
ActiveCell.Select

Best regards,
Trowa
Posts
3
Registration date
Monday May 13, 2013
Status
Member
Last seen
May 14, 2013

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???
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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.
Posts
3
Registration date
Monday May 13, 2013
Status
Member
Last seen
May 14, 2013

Can I attach a doc to thi. It would be easier for you to understand if you could see it.
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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.