Copy then Paste more than once

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

Sub Test()
Selection.Copy
Range("A15").Select
ActiveSheet.Paste
End Sub

The above will copy to 1 Cell in this case A15

Can it be That I can copy then paste to several cells
All the cells I copy to are in Col A:A, but over more than
1 Workbook. So really I am trying to achieve the macro to
copy A Value then paste the Value 12 times over several
Workbooks.
Thankyou.
 
Steve,

Sub TryNow()
Selection.Copy
'Copy to the same workbook, same sheet
Range("A5").PasteSpecial xlPasteAll
Range("A10").PasteSpecial xlPasteAll
Range("A15").PasteSpecial xlPasteAll
Range("A20").PasteSpecial xlPasteAll
'Copy to the same workbook, different sheet
With Worksheets("Sheet2")
.Range("A1").PasteSpecial xlPasteAll
.Range("A3").PasteSpecial xlPasteAll
.Range("A6").PasteSpecial xlPasteAll
.Range("A9").PasteSpecial xlPasteAll
End With
'Copy to a different workbook
With Workbooks("Book2.xls").Worksheets("Sheet1")
.Range("A4").PasteSpecial xlPasteAll
.Range("A8").PasteSpecial xlPasteAll
.Range("A12").PasteSpecial xlPasteAll
End With
End Sub

HTH,
Bernie
MS Excel MVP
 
Hello Bernie from Steved

Thankyou

You have nominated the cells, for your macro
to help me I need to know what cells in a particular work
to copy to. Your macro is What I require please but is it
possible to develop it without a nominated range.

I copied your Macro and Ran it
Range("A5").PasteSpecial xlPasteAll
Is it possible to use the above formula in a loop
ie Range("any").PasteSpecial xlPasteAll
The reason is that I would have to look for the cells,
I need to paste, then using your formula change the
"A5" as an example each time hoping to avoid this.

If it is possible "copy then paste as many time as I need
to do then say push esc to end the macro.

Thankyou for taking timeout.

Cheers.
 
Steved,

Anything is possible. What is your logic or criteria for selecting cells to
paste?

HTH,
Bernie
MS Excel MVP
 
Hello From Steved

I am the keeper of Working Timetables
In Column A:A is a Driver number ie "4537"
This represents the work the person does
I have A Monday to Friday Timetable

I have 55 diferent timetables

the number 4537 might be on 8 of them
On one timetable would be for an example 7:30am Route 567 I
on the above the time table is associated by Route "567"
Simply I would like to replace 4537 with 4409.

" I copy 4409 then paste it to one of the several
Timetables.

Ok What I require please is for The macro to
A copy a Value
B paste to more than 1 cell over more than one workbook,
using the value I have just copied.
What determines where I paste is by time on a timeable,
as an example "4409 will do 7:30am" I will look for the
Timetable 567 then 7:30am trip which in this case is in
Cell B20 and paste 4409, then 4409 will do 8:25am trip on
Timetable 475 and I then find the Cell 8:25am is in then
paste 4409, I could do this step up to 12 more Different
Timetables.

Another explantion is Copy the Value paste several times
and push the Escape key to stop the Macro. As I could do
many changes ie I start with 4409 then finish, Copy
another value 5530 then paste to several workbooks, and
keep do this process until i have finished the exercise.

Sorry about the long explanation but hopefully you follow.
Just think in this term you have type in Cell A100 for
example now you need the macro to hold this information
so that you can paste as many Cells that need to be done
then have the macro end the proicess.

Thankyou for taking timeout so far.
 
Steved,

I'm still not sure of your logic, but we'll try.

Insert a sheet into your workbook, then name it "Replace Values", and put
4537 in cell A1, and 4409 in cell B1. Then run the macro below.

The macro will look through all your sheets, and replace every cell that has
just 4537 with 4409 - that is, where the comnplete cell value is 4537. If
you would like to replace any occurence, even within strings, then change

xlWhole
to
xlPart

HTH,
Bernie
MS Excel MVP

Sub ChangeAllValues()
Dim mySht As Worksheet

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name <> "Replace Values" Then
mySht.Cells.Replace _
Worksheets("Replace Values").Range("A1").Value, _
Worksheets("Replace Values").Range("B1").Value, _
xlWhole
End If
Next mySht
End Sub
 
Hello Bernie from Steved

Yes your macro is designed to do exactly as I require,
I thankyou.

Can your macro be put in personal.xls which I will then
put this in xlStart.

My understanding is your macro works on A workbook
with many worksheets. ("which is fine")

What I would like to do is open more than one workbook
then run your macro, is this possible please.

By doing this I do not have to do 1 Workbook at a Time.

Thankyou.
 
Steved,

Would your replacement be keyed by the values in cells in each workbook, or
by a master list?

HTH,
Bernie
MS Excel MVP
 
Hello from Steved

Keyed by the values in cells please.

My understanding of your question is
1 I change data in a Cell ie 4527 to 2627

2 I would have your formula copy 2627

3 I would select the cells that have 4527,
your formula will put in 2627

I would like to thankyou for taking timeout
as although I am happy with your original
formula, If you can improve on it it will
save quiet a lot of hours as sometimes when
we do a major timetable change I can spend
3 to 4 days updating.
Thankyou
 
Steved,

You need to answer my question:

That is: - will each workbook have differing replacement values, or will all
the workbooks require the same replacements?

And what cells do you want the orginal and replacement values to be read
from, or would you prefer that the macro prompt you for both?

HTH,
Bernie
MS Excel MVP
 
Hello from Steved
Sorry Bernie I will give you an answer to your questions
below.

That is: - will each workbook have differing replacement
values, or will all the workbooks require the same
replacements?

The workbooks require the same replacements

And what cells do you want the orginal and replacement
values to be read from, or would you prefer that the
macro prompt you for both?

macro prompt you for both please.

Thankyou.



-----Original Message-----
Steved,

You need to answer my question:
Click to expand...
That is: - will each workbook have differing replacement
values, or will all the workbooks require the same
replacements?And what cells do you want the orginal and replacement
values to be read from, or would you prefer that the
macro prompt you for both?
 
Steved,

Open all the workbooks that need to have changes made, then run the sub
below.

HTH,
Bernie
MS Excel MVP

Sub ChangeAllValues2()
Dim mySht As Worksheet
Dim myBook As Workbook
Dim ReplaceWith As String
Dim ToReplace As String

ToReplace = Application.InputBox("What value to replace?")
ReplaceWith = Application.InputBox("Replace '" & _
ToReplace & "' with what other value?")

For Each myBook In Application.Workbooks
For Each mySht In myBook.Worksheets
mySht.Cells.Replace _
ToReplace, ReplaceWith, _
xlWhole
Next mySht
Next myBook

End Sub
 
Hello Bernie from Steved

Thankyou, I will let you know how I got on As I am
about to use it.

I would like to say thankyou for spending time on my issue.

Cheers.
 
Steved,

We have a saying in the newsgroups:

"Satisfaction guaranteed within the week, or your money back!" ;-)

Bernie
 
Bernie

Yes This is what I require and I thankyou.

ps you have a problem that is this guy is a bit slow on
the uptake ie "money what's this you say money".

Cheers
 
Back
Top