Re: Can I copy data (Values) from one *** to another with macro

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 *** by the name selected in the DV cell.
Note that the code contains the *** name "13th***". That part of the code simply ensures that anything entered into B3 of that *** will not fire the code to copy & paste. Change that name in the code to match the actual name of your 13th ***.
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 ***.
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 moehrbachoextra@xxxxxxxxxxxxxx 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 = "13th***" 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" <Husker87@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:BFBA740C-E940-491D-9F53-14D860D9B48E@xxxxxxxxxxxxxxxx
OK… how about this. 12 sheets, named for each month of the year. I just
used the 13th as a *** to house the list of months for the drop down.
Anyway, each *** is identical below row 10. When the user is ready to
enter data in the range A11:L200 on a particular ***, 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 *** he is currently working on. If they can do
that then they only need to edit the data that has been exported from another
*** rather than type all the data in again. Most of the data will be the
same from one work*** 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 ***. Then they would click a macro
button and the data from the February *** in A11:L200 is copied as values
onto the May *** 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" wrote:

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 *** selected in the B3 DV cell, to be
copied to the active ***. 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" <Husker87@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:264ED490-0F0F-4E67-AE40-33EAA1BB00D4@xxxxxxxxxxxxxxxx
> One more thing... I can post macros to the workbook and worksheets...
>
> "Otto Moehrbach" wrote:
>
>> I would use a Workbook_SheetChange macro in the Workbook module as >> shown
>> below. I assumed your 13th *** is named "13th***". 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 = "13th***" Then Exit Sub
>> If Not Intersect(Target, Sh.Range("B3")) Is Nothing Then
>> 'Note that Target.Value is *** name selected
>> 'Note that Sh is the *** 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" <Husker87@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:1A81F24F-5E28-4824-8FCB-487BE296C00C@xxxxxxxxxxxxxxxx
>> > Hello all,
>> > I’m trying to write a macro. I have 12 identical worksheets, one >> > for
>> > each
>> > month of the year. Each *** has a drop down in cell B3 with the >> > 12
>> > months
>> > in it which comes from another (13th) ***. A lot of data gets
>> > entered
>> > into
>> > each monthly *** 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 *** they are working on and click a
>> > macro
>> > button that would copy the values from that month to the current
>> > work***
>> > they are working on. I can’t seem to get the macro to recognize the
>> > value
>> > in
>> > B3. Help.
>> >
>>
>>



.

Loading