Solved Paste a range of information from one worksheet to a defined

April 23, 2013 at 12:17:21
Specs: Windows 7
<></PASTE_card()
'
' PASTE Macro
'

'
Range("A1:I31").Select
Sheets("Sheet1").Select
Selection.Copy
Sheets("Sheet9").Select
Cells(1, Range ("A33")).Select
ActiveSheet.PASTE
Selection.PasteSpecial PASTE:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Subpre>

"A33" generates the value to represent the column to which the paste will occurr

The above works very well with this part of the instruction

Sheets("Sheet9").Select

but I require ("Sheet9) to be represented as follows

What I require to do is paste a Range of information to a titled worksheet. Lets say there are 10 titled worksheets. Within the range "A1:I31", "D5" is a drop down list of the titled worksheets, i.e Sheet2 is titled Tony Gibb, Sheet3 is titled Rob Revill, Sheet4 is titled Paul Skinner etc.

If "D5" = Rob Revill, then the Range needs to be pasted to the worksheet titled Rob Revill

Any suggestions


See More: Paste a range of information from one worksheet to a defined

Report •


#1
April 23, 2013 at 13:05:18
First, let's talk about your macro. I assume that you recorded this macro and then edited it to fit your needs. That's a great practice, but you can do a lot more editing to make it more efficient.

Rarely, if ever, do you need to Select an object within VBA in order to perform an operation on it. You can almost always perform the operation directly within VBA.

Your code can be rewritten as follows:

Sub PASTE_card()
'
' PASTE Macro
'
'
 Sheets("Sheet1").Range("A1:I31").Copy
   With Sheets("Sheet9").Cells(1, Range("A33"))
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteColumnWidths
   End With
End Sub

As far as using the dropdown to supply the Sheet name, think about how you chose the value for the Column. You referenced a cell using the Range method, right? You can also reference a cell to pick up the Sheet Name.

However, I'm a little confused as to where the drop down (D5) and the value for the column (A33) are. Are they both on Sheet1?

If so, something like this might work for you. In this case, I used variables to store the value from Sheet1!D5 as the Sheet Name and the value from Sheet1!A33 as the column number. I then used those variables for the paste operation.

Sub PASTE_card()
'
' PASTE Macro
'
'
 Sheets("Sheet1").Range("A1:I31").Copy
  shtName = Sheets("Sheet1").Range("D5")
  colNum = Sheets("Sheet1").Range("A33")
   With Sheets(shtName).Cells(1, colNum)
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteColumnWidths
   End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 23, 2013 at 14:07:18
DerbyDad03
Works very well. One slight error that occurs is that the paste is not an exact mirror image of sheet1.
That is to say that the formatted cells, (colour, text alignment etc) has changed.
Can we get round this at all

Report •

#3
April 23, 2013 at 16:46:46
✔ Best Answer
Did you original paste everything? I couldn't get it to work so I wasn't sure what you were doing with the PasteSpecial command.

Anyway, try this:

Sub PASTE_card()
'
' PASTE Macro
'
'
 Sheets("Sheet1").Range("A1:I31").Copy
  shtName = Sheets("Sheet1").Range("D5")
  colNum = Sheets("Sheet1").Range("A33")
   With Sheets(shtName).Cells(1, colNum)
      .PasteSpecial Paste:=xlPasteAll
      .PasteSpecial Paste:=xlPasteColumnWidths
   End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 24, 2013 at 12:54:37
By changing the instruction to

.PasteSpecial Paste:=xlPasteAll

all the excel formulas paste as well, so I end up with an image of what I would like to see. However all the formula within the range paste as well, and I really just need the values of the formulas as these correspond to other worksheets within the workbook


Report •

#5
April 26, 2013 at 13:58:44
Have been trying various scenarios but cant get this to work the way that the original macro at the top of this question PASTE's

The last response given pasted all the formula, and not the merged cells. ( There are 3 to 5 areas where cells are merged).

My requirement is to paste exactly the range A1:I31 with values only to the worksheet designated by D5

The initial macro is a recording which works exactly as required other than is not pasting to the designated worksheet

Any more thoughts on this. I am baffled


Report •

#6
April 30, 2013 at 12:22:09
Quick message to DerbyDad03,
Could noy have achieved what I know have without your help.
Just amazing and so patient. I have only been using excel formulas and VBA since the new year so again thank you.

As far as my last request above, thought you might like to see the final result that does exactly what I require. Just need to refine the appearance

<>Sub ScoreCard_PASTE()
'
' PASTE Macro
'
'
Sheets("SCARD").Range("A1:I31").Select
Selection.Copy
shtName = Sheets("SCARD").Range("D2")
colNum = Sheets("SCARD").Range("A33")

With Sheets(shtName).Cells(1, colNum)
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteColumnWidths
End With
Application.CutCopyMode = False
End Sub

Report •


Ask Question