Macro to copy specific cells from one tab to another

[MacroLearner]

Hello,
I am attempting to create an Excel worksheet to approve and track communications. I have created an input tab that people could fill out and email to me with all the details of a communication need. When I receive it, I want to approve it with a button that runs a macro that selects specific cells (not all), copies those cells (not adjacent so I will need to select, copy, and paste for each piece of info individually) into another tab where I will have a directory of all communications.

I have recorded a macro but when it copies, it copies the text (for example, the send date is 1/24/12) instead of a cell reference. So when I go change the information on the input tab, (for example, the send date for another communication is 2/10/12) and run the macro, the original info all pastes (send date that pastes is still 1/24/12).

Also, I want the input tab to look nice and merged cells but was struggling to paste the content rather than getting a merged cell error. Do I need to avoid all merged cells.

Lastly, I had an idea to create a cell where I could type in text that would be the name of the tab to look at to copy the information from to get references if the approve button was on my directory tab?

Would love help on how to accomplish this!!
Cheers,
Caroline

[Don]

Hi and welcome to the forum!

Merged cells can be a pain, but you can generally work with them when you have to do so. Can you include a sample of the file and the macro that you are using? If you can provide us with more information, we can give you specific fixes and or code for the spreadsheet issues.

[Sisyphus]

Hi,
The fastest way to get where you want to go is to take one step at a time. Put each step into its own thread and don't let go until you got what you wanted. Then turn to the next problem. (You can run several threads side by side, though, for as many as you can keep track of.)

1. The Macro recorder automatically guides you toward Copy / Paste:

Code: Select all

Range("A1").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste


Actually, this isn't the way Excel thinks at all. Instead, it deals with what Excel does as well as what you see. When you write code you can neglect the second part and you end up with much shorter code, like this:

Code: Select all

Range("A1") = Range("A2")


2. The default property of a range is its Value. Because it is the default it doesn't need to be written, though most teachers recommend that you do. Anyway, in the above code the Value of A2 is written to the Value property of A1. It seems, you wanted the cell reference to be written instead. The property required is the Address. This code will write the cell reference to A1:

Code: Select all

Range("A1").Formula = "=" & Range("A2").Address


As you see, you didn't write to the default property (Value) of A1. Instead, you wrote to another property, the Formula property. And, since the Address of A2 is "$A$2" isn't a valid formula without the "=" before it, you added that and concatenated the two strings with an ampersand. If you don't need the (default) absolute referencing you can modify the address you write any way you want, but that is another thread unless you find it in the Help.

3. When you copy and paste merged cells the two ranges must be of identical size. If you refer to merged cells by their range address you should remember that only their top-left cell has the value or formula. So, to get the Value of range A1:B2 you ask the Value of A1. B2 has no value and may cause an error. Similarly, you can write the value of A1:B2 by writing to A1. The important thing is to avoid Copy / Paste, as the macro recorder would have you do, and use Read / Write instead, as demonstrated in the above code.

4. Your final question seems unclear, because what could possibly be difficult about having a cell into which you write the name of a tab, unless it is the abundance of cells to choose from? I presume that what you had in mind was a little more sophisticated. Perhaps this fragment of code can help you on your way:

Code: Select all

Activesheet.Cells(1, 1).Value = ActiveSheet.Name


I trust that you can actually understand this code even though it uses language you won't get from the Recorder. To manipulate this code you need to know the following:

a) You can replace Activesheet with Worksheets("Sheet name") or Worksheets(1)
b) The Cell address is defined by Row and Column, in that sequence, where Column 1 = A, 2 = B etc.

See what I mean? You got a lot of well-meant and very interesting advice. As such, you are now better equipped to deal with your problems than before. But has any one of them gone away as a result of your post?

[MacroLearner]

These comments were very helpful! I am still learning and am going to continue moving forward with what I've learned and wait until I have a few questions to post to keep me moving.

I am so excited!!

Side comment - it would be very powerful if on this website, there was a one page "getting started" tips.

Such as:
' denotes a line of comments to keep code clean
Worksheets("Name") is the reference for a worksheet name ("quotations are needed")
Application.CutCopyMode = False just deselects what was copied, like keying esc
Range("D12").Select selects the value and format ("quotations are needed) - althought I'm not sure if that is true about the format??

Basic actions and coding basics

[Sisyphus]

Hello MacrLearner,
Your excitement is contagious. Thank you for your input. I'm sure Don will take note of it and consider having a 'Tip of the day' as a feature as he continues to evolve his forum.
Meanwhile, the scope of VBA is very wide and you will get most enjoyment from it if you can bend it to your own designs. As you pointed out, the frontier of your capability expands continually. Regardless, how far you might reach, there will still be unexplored territory beyond the next hill.