Copy and Paste to specific worksheet

shoyu

New Member
Joined
May 26, 2016
Messages
10
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.

Code:
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...
 
Upvote 0
Code:
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?
 
Upvote 0
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
 
Upvote 0
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:

Code:
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
 
Upvote 0
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

A BC
18SummaryFull Name Wire Amount
19ABNABN
20JPMJPMorgan Chase 5,000,000.00
21PNCPNC Bank
22GSGoldman 60,000.00
23CSCredit Suisse 120,000.00
24YAHYahoo Inc 1,000,000.00

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top