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

Hello all,
Im 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 Id 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 cant seem to get the macro to recognize the value in
B3. Help.

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

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" wrote in message
...
Hello all,
Im 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 Id 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 cant seem to get the macro to recognize the value
in
B3. Help.

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

Im afraid you are talking a step or two beyond my abilities. The end result
Im 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.


"Otto Moehrbach" wrote:

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" wrote in message
...
Hello all,
Im 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 Id 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 cant seem to get the macro to recognize the value
in
B3. Help.

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

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 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" wrote in message
...
Hello all,
Im 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 Id 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 cant seem to get the macro to recognize the value
in
B3. Help.

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

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" wrote in message
...
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 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" wrote in message
...
Hello all,
Im 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 Id 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 cant seem to get the macro to recognize the
value
in
B3. Help.

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

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" 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 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" wrote in message
...
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 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" wrote in message
...
Hello all,
Im 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 Id 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 cant seem to get the macro to recognize the
value
in
B3. Help.

Can I copy data (Values) from one sheet 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 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 . 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" wrote in message
...
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" 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 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" wrote in message
...
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 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" wrote in message
...
Hello all,
Im 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 Id 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 cant seem to get the macro to recognize the
value
in
B3. Help.

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

PERFECT. Thanks for your help€¦ it works flawlessly.

"Otto Moehrbach" wrote:

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 . 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" wrote in message
...
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" 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 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" wrote in message
...
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 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" wrote in message
...
Hello all,
Im 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 Id 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 cant seem to get the macro to recognize the
value
in
B3. Help.