• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

find row and then copy a range

In Sheet "B", I have a table. In the Sheet "A" I have a summary sheet. I would like a macro that selects a defined row in Sheet "B" and then copies(transposes) a range in that row to a defined range in Sheet "A".
The row is defined by inserting the row number in cell "A1" in Sheet "A". Then I will run the macro manually.
Examples:
Sheet "A" cell A1 :  insert the number 5.
Then in Sheet "B", range A5:G5, Select
Sheet "A", range C1:C8. paste/Transpose

Sheet "A" cell A1 :  insert the number 10.
Then in Sheet "B", range A10:G10, Select
Sheet "A", range C1:C8. paste/Transpose

Sheet "A" cell A1 :  insert the number 22.
Then in Sheet "B", range A22:G22, Select
Sheet "A", range C1:C8. paste/Transpose


Thanks
CC
0
CC10
Asked:
CC10
  • 2
1 Solution
 
psmittyCommented:
Sub Macro x
Dim i
 i = ActiveSheet.Cells(1,1).Value
 Range("A" & i ":G" & i).Select
 Selection.Copy
 Sheets("Sheet B").Select
 Range("C1").Select
 Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False,  Transpose:=True
End Sub

Open in new window

0
 
CC10Author Commented:
This does not seem to work. Is the separator correct in the Range statement?

Also, the range to be copied is in Sheet B and then that should be pasted back into Sheet A in Range(C1)

The single cell to determine the row number in Sheet "B",  is Sheet"A". Cell(1,1)
The range is Sheet B, row i,A:G
It is pasted back into Sheet A, cell C1

Thanks
0
 
CC10Author Commented:
Have solved it. The range statement was incorrect.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now