Can I copy data (Values) from one sheet to another with macro

[Husker87]

Hello all,
I’m trying to write a macro. I have 12 identical worksheets, one for each
month of the year. Each sheet has a drop down in cell B3 with the 12 months
in it which comes from another (13th) sheet. A lot of data gets entered into
each monthly sheet and is pretty much the same month to month. When filling
out a new month I’d like the user to be able to select a different month with
the drop down on the current sheet they are working on and click a macro
button that would copy the values from that month to the current worksheet
they are working on. I can’t seem to get the macro to recognize the value in
B3. Help.

 

[Otto Moehrbach]

I would use a Workbook_SheetChange macro in the Workbook module as shown
below. I assumed your 13th sheet is named "13thSheet". Come back if you
need more. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Sh.Name = "13thSheet" Then Exit Sub
If Not Intersect(Target, Sh.Range("B3")) Is Nothing Then
'Note that Target.Value is sheet name selected
'Note that Sh is the sheet that holds the Target cell
'Note that Target.Address is B3
'Place your code here or a call to your macro
End If
End Sub

 

[Husker87]

I’m afraid you are talking a step or two beyond my abilities. The end result
I’m looking for is that by using the dropdown from one of the 12 sheets you
could select from any worksheet a range of values (A11:L200 for example) to
copy to that worksheet which you are working on. That would enable the user
to simply select the data from an earlier month and modify the data rather
than having to start from scratch. Sorry I wasn't able to understand what to
do with your first answer and THANKS very much for your help.

 

[Husker87]

One more thing... I can post macros to the workbook and worksheets...

 

[Otto Moehrbach]

What I gave you was more of an outline of what the macro would look like.
Basically, the macro would be triggered by the act of making a selection
from the B3 DV list. Everything after that would be automatic. You say
that you want a range from the sheet selected in the B3 DV cell, to be
copied to the active sheet. Good. What range? Is the range always the
same? If not, tell me how you would MANUALLY figure out the range to copy.
Pretend that you are explaining this to someone who will be doing this for
you. What would you tell him? Just the part about figuring out the range.
HTH Otto

 

[Husker87]

OK… how about this. 12 sheets, named for each month of the year. I just
used the 13th as a sheet to house the list of months for the drop down.
Anyway, each sheet is identical below row 10. When the user is ready to
enter data in the range A11:L200 on a particular sheet, I would like to give
him the functionality to select one of the 11 other months to copy the range
A11:L200 from it onto the sheet he is currently working on. If they can do
that then they only need to edit the data that has been exported from another
sheet rather than type all the data in again. Most of the data will be the
same from one worksheet to the next. Example: The user has entered data
into the first 4 sheets. January – April. They then select the May tab.
They then could select the month of say February in the drop down in cell B4
on the May tab because the data in February is the closest match to the data
that needs to be entered in the May sheet. Then they would click a macro
button and the data from the February sheet in A11:L200 is copied as values
onto the May sheet in section A11:L200. Now the user only needs to edit
small amounts of the data on the May tab before he goes onto the June tab
instead of typing it all in again. You are VERY understanding and I
appreciate it!

 

[Otto Moehrbach]

Try this macro and see if it does what you want. Be aware that the months
listed in the DV cells MUST match EXACTLY the names of the sheets. Any
extra spaces or misspellings will cause a "Subscript out of range" error
because the code cannot find a sheet by the name selected in the DV cell.
Note that the code contains the sheet name "13thSheet". That part of the
code simply ensures that anything entered into B3 of that sheet will not
fire the code to copy & paste. Change that name in the code to match the
actual name of your 13th sheet.
This macro is a workbook event macro and MUST be placed in the workbook
module of your file. To access that module, go to the VBE (Visual Basic
Editor) by doing Alt - F11. Look on the left part of the screen and find
the Project window. If it is not there, click on View - Project Explorer.
In the Project window, find your file listed. Expand that file listing
until you see "ThisWorkbook". Double-click on that. That brings up the
Workbook module in the large window. Paste this macro into that module.
"X" out of the VBE to return to your sheet.
If you can't find where to place this macro, email me and I'll send you the
small file I used to develop the code. That will have the macro properly
placed. My email address is (e-mail address removed). Remove the
"extra" from this address. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Sh.Name = "13thSheet" Then Exit Sub
If Not Intersect(Target, Sh.Range("B3")) Is Nothing Then
With Sheets(Target.Value)
.Range("A11:L200").Copy
Range("A11").PasteSpecial xlPasteValues
End With
End If
End Sub

 

[Husker87]

PERFECT. Thanks for your help… it works flawlessly.