Previous Topic Next Topic

C+P macro to dest. based on value

Closed
Matrixfan - Dec 6, 2011 at 08:37 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Dec 7, 2011 at 08:36 AM
Hello,

I'm still learning Macros and the like, and have had some luck learning the functions for what I have need in the past.

What I am trying to figure out how to do is this:
a) Select a range of cells based on activecell on Main Page tab of the workbook
b) copy this range (a:h of the activecell row)
c) paste this range into a different tab within this workbook by inserting the range above the previous information on the destination tab (the tab name reflects the same name in Main Page Column A)
d) return to main page tab;

What I have so far:

Sub one_touch()
i = ActiveCell.Row
Cells(ActiveCell.Row, 1).Range("a1:h1").Select
Selection.Copy

(((I have nothing to bring between these points)))

(((This needs to reference the destination tab, based on the value of Main Page tab Column A)))
Range("A3").Select
Selection.Insert Shift:=xlDown
Sheets("Main Page").Select
Application.CutCopyMode = False
End Sub


if someone would be able to assist in bridging the gap, it would be appreciated.
If I'm completely out to lunch on my attempt, I'm open to suggestions.
Thank you in advance to anyone who can provide assistance.

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 6, 2011 at 08:53 AM
Try this 

Sub one_touch()

   Dim lActiveRow          As Long

   lActiveRow = ActiveCell.Row
   Range(Cells(lActiveRow, "A"), Cells(lActiveRow, "H")).Copy
   Sheets("Sheet2").Range("A3").Insert Shift:=xlDown
   Application.CutCopyMode = False
End Sub
0
Matrixfan
Dec 7, 2011 at 05:43 AM
Thanks for the quick response.

I'm still getting an error - "Runtime error 9: Subscript out of range" on Sheets("Sheet2").Range("A3").Insert Shift:=xlDown

Just to elaborate on what I'm looking for:
the paste destination tab is variable based on the value in the first column of the row (which is someones name). The position it goes into is "A3" and then pastes into there by pushing everything else down.

The whole seems to work, except for the one line.

Thanks for the help so far - you're a lifesaver (plus, I get to learn more about Macros, etc)
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 7, 2011 at 08:36 AM
Oh so you are saying that on active row, look at cell in column A. That will give the destination sheet name ? If that is the case 

Sub one_touch()

   Dim lActiveRow          As Long

   lActiveRow = ActiveCell.Row
   Range(Cells(lActiveRow, "A"), Cells(lActiveRow, "H")).Copy
   Sheets(Cells(lActiveRow, "A")).Range("A3").Insert Shift:=xlDown
   Application.CutCopyMode = False
End Sub
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses
How do I change a base location on Instagram?
rebelkids -
38nancy -

8 responses
Code for macro that conditionally inserts rows
bfleming -
bfleming -

4 responses
Facebook says that my email doesn't match my facebook email
@@@@@@@@@@@ -
Ambucias -

1 response