Excel/vba

Advertisement


Question
QUESTION: Hi Tom

Is it possible to write a piece of code that does the following:

Copy the range: A1:D400 from:

Sheet33 to Sheet3
Sheet34 to Sheet4
Sheet35 to Sheet5
Sheet36 to Sheet6
Sheet37 to Sheet7

Then go to worksheet "charts" cell "d1"

Is this something that you could help me with?

Thanks in advance

Chris

ANSWER: Christopher Mitchell,

I would see it this way:

Sub copyAD()
Dim i as long, r as Range
for i = 3 to 7
 set r = worksheets("Sheet3" & i).Range("A1:D400")
 r.copy worksheets("Sheet" & i).Range("A1:D400")
Next
worksheets("Charts").Activate
Range("D1").Select
End sub

if you want to paste special values (no formula, no formats)


Sub copyADSpecial()
Dim i as long
for i = 3 to 7
worksheets("Sheet" & i).Range("A1:D400").Value = _
worksheets("Sheet3" & i).Range("A1:D400").Value
Next
worksheets("Charts").Activate
Range("D1").Select
End sub

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: Hi Tom

Please accept my apologies.  I did not give very clear instructions.

I actually wanted the code to copy the range "A1:D400" on Sheet33
and then paste it into Sheet3.  So that the contents of "A1:D400" would be copied from:

Sheet33 and pasted to Sheet3
Sheet34 and pasted to Sheet4
Sheet35 and pasted to Sheet5
Sheet36 and pasted to Sheet6
Sheet37 and pasted to Sheet7

Then go to worksheet "charts" cell "d1"

Could you adjust the code please?

regards

Chris Mitchell

ANSWER: Christopher Mitchell,

You said I want A1:D400 copied from the sheet shown on the left and pasted to the sheet shown on the right:

Sheet33 and pasted to Sheet3
Sheet34 and pasted to Sheet4
Sheet35 and pasted to Sheet5
Sheet36 and pasted to Sheet6
Sheet37 and pasted to Sheet7
Could you adjust the code please?

No Problem.   No adjustment required.  I tested the code and it worked exactly as you describe.

copied A1:D400 from Sheet33 and pasted to A1:D400 in Sheet3
Copied A1:D400 from Sheet34 and pasted to A1:D400 in Sheet4
copied A1:D400 from Sheet35 and pasted to A1:D400 in Sheet5
Copied A1:D400 from Sheet36 and pasted to A1:D400 in Sheet6
copied A1:D400 from Sheet37 and pasted to A1:D400 in Sheet7

both versions of the code I provided did that.  The second version did a paste special values
The first did a normal copy.

Since it isn't broken, I can't fix it.  Sorry.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom

Again, my fault entirely.  I could not figure out the section of the code that pasted the copied data.  Could you show which section of the code does this?

Thanks once again

Chris

Answer

r.copy worksheets("Sheet" & i).Range("A1:D400")

r is established in the previous line:

set r = worksheets("Sheet3" & i).Range("A1:D400")

so when i = 3 then you have


set r = worksheets("Sheet33).Range("A1:D400")
r.copy worksheets("Sheet3").Range("A1:D400")

when i = 4

set r = worksheets("Sheet34).Range("A1:D400")
r.copy worksheets("Sheet4").Range("A1:D400")


and so forth

the copy command anchors on the source range being copied.  In this case, the range reference "r" which will always refer to a sheet name beginning with   Sheet3  with the value of i appended.

r.copy  Destination range

so the destination range will always refer to a sheet with the Name beginning with "Sheet" and the value of i appended.   

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.