C+P macro to dest. based on value

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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)
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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