Copy and Paste to specific worksheet

[shoyu]

Hi All, I currently have one workbook with over 40 wire transfer form worksheets. Yes, really outdated.
I have Summary tab with list of all worksheets from A18, full name in B18, and wire amount in C18 (all with headers).
I would like to copy any wire amounts inputed under C18 and paste to the specific tab name from column A18 and paste to B5 of the specific worksheet.

Sub copyprint()


Dim SName As Worksheet
Dim TabName As String


TabName = Range("C18").Offset(1, -2)


Sheets("Summary").Range("A18").AutoFilter Field:=3, Criteria1:="<>"


Range("C18").Offset(1, 0).Copy
Sheets(TabName).Range("B5").PasteSpecial Paste:=xlPasteValues
Do Until ActiveCell.EntireRow.Hidden = True
Loop


End Sub

 

[Rhodie72]

Hi All, I currently have one workbook with over 40 wire transfer form worksheets. Yes, really outdated.
I have Summary tab with list of all worksheets from A18, full name in B18, and wire amount in C18 (all with headers).
I would like to copy any wire amounts inputed under C18 and paste to the specific tab name from column A18 and paste to B5 of the specific worksheet.

Sub copyprint()


Dim SName As Worksheet
Dim TabName As String


TabName = Range("C18").Offset(1, -2)


Sheets("Summary").Range("A18").AutoFilter Field:=3, Criteria1:="<>"


Range("C18").Offset(1, 0).Copy
Sheets(TabName).Range("B5").PasteSpecial Paste:=xlPasteValues
Do Until ActiveCell.EntireRow.Hidden = True
Loop


End Sub

Let's have a look at this puppy...

 

[Rhodie72]

Option Explicit

Sub copyprint()

Dim SName As Worksheet, TabName As String

    TabName = Range("C18").Offset(1, -2)

    Sheets("Summary").Range("A18").AutoFilter Field:=3, Criteria1:="<>"

    Range("C18").Offset(1, 0).Copy Sheets(TabName).[B5]

Rem Something to be done here but not sure what
    Do

    Loop Until ActiveCell.EntireRow.Hidden = True

End Sub

From what I am able to deduce, you are trying to run a loop. You want this loop to cycle trough each VISIBLE sheet and perform an operation but you don't tell what you want it to do. So here's how I think this should go down...

1) you want the routine to cycle through each sheet.
2) In C18 you have trhe name of a sheet there that is different on every sheet.
3) you want to copy the data from that sheet to B5 on another named sheet that is named in C18 of each active sheet.
4) I have got this wrong and you need to explain more?

 

[shoyu]

Hi Rhodie, thank you for the quick response. I probably wasn't clear on what I wanted. Apologies.
The Summary tab is the initial tab where someone would input amounts in Column C18
Column C18 has the amount to be pasted into the specific tab names in Column A18.
I ran below, but only stopped at C19. Any ideas?

Sub copyprint()


Dim SName As Worksheet
Dim TabName As String


TabName = Range("C18").Offset(1, -2)


Sheets("Summary").Range("A18").AutoFilter Field:=3, Criteria1:="<>"


Range("C18").Offset(1, 0).Copy


Do
Sheets(TabName).Range("B5").PasteSpecial Paste:=xlPasteValues


Loop Until ActiveCell.EntireRow.Hidden = True


End Sub

 

[Rhodie72]

Hi Rhodie, thank you for the quick response. I probably wasn't clear on what I wanted. Apologies.
The Summary tab is the initial tab where someone would input amounts in Column C18
Column C18 has the amount to be pasted into the specific tab names in Column A18.
I ran below, but only stopped at C19. Any ideas?

Sub copyprint()


Dim SName As Worksheet
Dim TabName As String


TabName = Range("C18").Offset(1, -2)


Sheets("Summary").Range("A18").AutoFilter Field:=3, Criteria1:="<>"


Range("C18").Offset(1, 0).Copy


Do
Sheets(TabName).Range("B5").PasteSpecial Paste:=xlPasteValues


Loop Until ActiveCell.EntireRow.Hidden = True


End Sub

You may have 46 cells named A18 in the workbook. THe specifics of each sheet are what matter. I can't see your sheet so you are going to have to explain properly the specofoc cells rather than the relative cells. This means using this annotation:

Sheets("Suspense").[A18].copy Sheets("Silly Squaw").[A5]

Now remember to use the format

[CODE]Sheets("Suspense").[A18].copy Sheets("Silly Squaw").[A5][/CODE]

to display your code in its own window.

I cannot fathom whether you have names in a single cell on 1 sheet or if each sheet has the same cell reference for another sheet name. Worse still, what if there is a column of sheet names and I can't tell if it's horizontal or vertical, where it begins and where it ends.##

It's all very nebulous

 

[shoyu]

Hi, I have name of each tab listed vertically in Column A. If there is a value in Column C copy, go 2 columns to left, and paste to Cell B5 in specific sheet.
So for first instance, 5mm would be pasted into Sheet("JPM") in cell B5. Hope that clarifies things. Thanks

[TABLE="width: 387"]
<tbody>[TR]
[TD][/TD]
[TD]A [/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]Summary[/TD]
[TD]Full Name[/TD]
[TD] Wire Amount[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]ABN[/TD]
[TD]ABN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]JPM[/TD]
[TD]JPMorgan Chase[/TD]
[TD="align: center"] 5,000,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]PNC[/TD]
[TD]PNC Bank[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]GS[/TD]
[TD]Goldman[/TD]
[TD="align: center"] 60,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]CS[/TD]
[TD]Credit Suisse[/TD]
[TD="align: center"] 120,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]YAH[/TD]
[TD]Yahoo Inc[/TD]
[TD="align: center"] 1,000,000.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rhodie72]

I am away until 17 January 2017