Excel/Copy/paste

Advertisement


Question
QUESTION: Hi Tom

Wanted to do two things, first thank you for the assistance last week with the issue i was having with multiple books, it helped a lot.  Again, many thanks.....

second, i wanted to see if you could guide me on two issues.....

First i am copying different cells (or trying to) from one sheet to another sheet in their exact respective cells.  I cant link them cause they always need to change, i tried selecting each one individually and test by copying them but it wont let me select any cells that arent connected.  I want to be able to do in VBA as well.....here are the following cells,


K4
H12
N12:n14
N16:n20
P6:p7
P9:p14
R5:r6
R8:R10
R11:R14
Q17
Q19:Q20
U5:W14
Z5:z11
Z13:Z14
Z16:Z19
to the same cells in another sheet in the same workbook.....

second

I have a macro the has a short cut key to performs as follows:
Ctrl+c opens a sheet, and their hidden cells, then
Crtl +d from that sheet back to the original sheet after closing and hiding those cells it opened.

it works fine as a macro, but cant get it work when i put on the sheet in vba.....

ii am trying to get totally away from using any macros and this is one of two things i have left that have macros....

Please again thanks for you assistance....

tony

ANSWER: tony,

I understand you to say you want to manually copy discontiguous cells and have them pasted to the same cell location in another sheet.

To the best of my knowledge, you will have to copy and paste each individual contigous block of cells (commonly referred to as an area).  

>it works fine as a macro, but cant get it work when i put on the sheet in vba.....

that statement means nothing to me.  A macro and VBA are the same thing and neither goes on a sheet unless you are talking about the old Excel 4 macros.

--
Regards,
Tom Ogilvy


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

QUESTION: Thanks for your response....i truly appreciated everything you have suggested, and i apologize if i am unable to convert what my needs are through this process, I will try and do better.  This is the code that i did and it works as a macro, i actually didnt realize that VBA and Macros are actually one in the same, i just figured that vba was a bit smoother.

Nevertheless, when i run the macro with all those copy/paste it does the job but looks lousy doing it, is there something in the code i could do better to make it at least show a bit better or faster?

Basically what its doing it taking the information from a worksheet, in different cells, copying those cells to a sheet in order to add the information to a internal database allow the client to keep their clients information at a click of a key....



Sub customerindex()
'
' customerindex Macro
'

'
   Range("Z5:Z11").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("Z5").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   ActiveWindow.SmallScroll ToRight:=-78
   Sheets("Customer Index").Select
   Range("Z13:Z14").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("Z13").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("Z16:Z19").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("Z16").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("U5:W14").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("U5").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("Q19:R23").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("Q19:R19").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("Q16:R16").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("Q16:R16").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("R12:R14").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("R12").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("R9:R10").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("R9:R10").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("R8:S8").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("R8:S8").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("R5:R6").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("R5").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("P9:P14").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("P9").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("P6:P7").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("P6").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("N16:O20").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("N16:O16").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("N13:N14").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("N13").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("H12:J13").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("H12:J12").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer").Select
   Range("K4").Select
   Application.CutCopyMode = False
   ActiveCell.FormulaR1C1 = "0"
   Range("N12").Select
   ActiveCell.FormulaR1C1 = "0"
   Range("H12:J12").Select
   ActiveWindow.SmallScroll ToRight:=-82
End Sub


i can send you the program if you can tell me a way to do it....

Thanks again, Tom

Answer
Anthony,

Here are two ways:

1)  Use you existing code but turn off screenupdating at the top

Sub customerindex()
'
' customerindex Macro
'

'
 Application.ScreenUpdating = False  '<== turns off screen updating

   Range("Z5:Z11").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("Z5").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   ActiveWindow.SmallScroll ToRight:=-78
   Sheets("Customer Index").Select
   Range("Z13:Z14").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("Z13").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("Z16:Z19").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("Z16").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("U5:W14").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("U5").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("Q19:R23").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("Q19:R19").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("Q16:R16").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("Q16:R16").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("R12:R14").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("R12").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("R9:R10").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("R9:R10").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("R8:S8").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("R8:S8").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("R5:R6").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("R5").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("P9:P14").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("P9").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("P6:P7").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("P6").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("N16:O20").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("N16:O16").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("N13:N14").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("N13").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer Index").Select
   Range("H12:J13").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Customer").Select
   Range("H12:J12").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Sheets("Customer").Select
   Range("K4").Select
   Application.CutCopyMode = False
   ActiveCell.FormulaR1C1 = "0"
   Range("N12").Select
   ActiveCell.FormulaR1C1 = "0"
   Range("H12:J12").Select
   ActiveWindow.SmallScroll ToRight:=-82

 Application.ScreenUpdating = True  '<==  turns screen updating back on
End Sub


2)
You can address cells without activating the sheet and selecting the cell.  That is how you do it manually - so that is how the code is recorded.  This code produced the same output as your original code:

Sub customerindex1()
'
' customerindex Macro
'
Application.ScreenUpdating = False
Dim r1 As Range, ar As Range, r2 As Range
Dim s1 As String, s2 As String, s As String
s1 = "Z5:Z11,Z13:Z14,Z16:Z19,U5:W14,Q19:R23,Q16:R16,R12:R14,R9:R10,"
s2 = "R8:S8,R5:R6,P9:P14,P6:P7,N16:O20,N13:N14,H12:J13"
s = s1 & s2
Set r1 = Worksheets("Customer Index").Range(s)
For Each ar In r1.Areas
 Set r2 = Worksheets("Customer").Range(ar(1).Address)
 ar.Copy
 r2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False

Next
With Worksheets("Customer")
 .Range("K4,N12").Value = 0
 .Activate
 .Range("H12:J12").Select
End With
Application.ScreenUpdating = True
End Sub

--
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.