#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 468
Default Macro-Copy/add

Hi, i need a macro to copy and add (i dont want to replace) the content of
sheet 1 to sheet october. But i have in sheet 1, M1, a cell with october. I
need the macro to look in M1 and to add content of sheet 1 columns A:J to the
corresponding sheet.
If in M1 i have October, then the macro to add to the october sheet. If i
have november in M1, then to copy content to november sheet.
Can this be done?
Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,533
Default Macro-Copy/add

Hi

This should do it:

Sub AddFromSheet()
Dim shName As String
Dim DestSh As Worksheet
Dim TargetSh As Worksheet

Set TargetSh = Worksheets("Sheet1")
shName = TargetSh.Range("M1").Value
On Error Resume Next
Set DestSh = Worksheets(shName)
If DestSh Is Nothing Then
Err.Clear
msg = MsgBox("The sheet: " & shName & " does not exists", vbExclamation
+ vbOKOnly, "Error")
Exit Sub
End If
TargetSh.Columns("A:J").Copy
DestSh.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
Application.CutCopyMode = False
End Sub

Regards,
Per

"puiuluipui" skrev i meddelelsen
...
Hi, i need a macro to copy and add (i dont want to replace) the content of
sheet 1 to sheet october. But i have in sheet 1, M1, a cell with october.
I
need the macro to look in M1 and to add content of sheet 1 columns A:J to
the
corresponding sheet.
If in M1 i have October, then the macro to add to the october sheet. If i
have november in M1, then to copy content to november sheet.
Can this be done?
Thanks!


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 207
Default Macro-Copy/add

puiulipui,
This is what I came up with to solve your problem:
Open a new Workbook, double right-click on "Sheet2" and rename it "Blank".
Double right-click "Sheet3" and rename it "Blank2".
Add a Command button and use the following code:
Private Sub CommandButton1_Click()
Dim sName As String
sName = Range("M1")

Sheets("Sheet1").Activate
Range("A:J").Select
Selection.Copy
Sheets("Blank").Select
Sheets("Blank").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Blank").Name = sName
Sheets.Add.Name = "Sheet2"
Sheets("Sheet2").Name = "Blank"
Sheets("Sheet1").Select
Sheets("Sheet1").Activate
Range("A1").Select

End Sub


Hope this Helps and good luck.
"puiuluipui" wrote:

Hi, i need a macro to copy and add (i dont want to replace) the content of
sheet 1 to sheet october. But i have in sheet 1, M1, a cell with october. I
need the macro to look in M1 and to add content of sheet 1 columns A:J to the
corresponding sheet.
If in M1 i have October, then the macro to add to the october sheet. If i
have november in M1, then to copy content to november sheet.
Can this be done?
Thanks!

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 468
Default Macro-Copy/add

Hy Flo, i did everything like you said, but when i want to assign a macro, i
can't see ut. I removed Private from Macro name:
from this:
Private Sub CommandButton1_Click()
into this:
Sub CommandButton1_Click()

Now i can see it, but it gives me an error "run time error 1004".
i hit debug and it highlighting "Sheets.Add.Name = "Sheet2""

What am i doing wrong?
Thanks!






"FloMM2" a scris:

puiulipui,
This is what I came up with to solve your problem:
Open a new Workbook, double right-click on "Sheet2" and rename it "Blank".
Double right-click "Sheet3" and rename it "Blank2".
Add a Command button and use the following code:
Private Sub CommandButton1_Click()
Dim sName As String
sName = Range("M1")

Sheets("Sheet1").Activate
Range("A:J").Select
Selection.Copy
Sheets("Blank").Select
Sheets("Blank").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Blank").Name = sName
Sheets.Add.Name = "Sheet2"
Sheets("Sheet2").Name = "Blank"
Sheets("Sheet1").Select
Sheets("Sheet1").Activate
Range("A1").Select

End Sub


Hope this Helps and good luck.
"puiuluipui" wrote:

Hi, i need a macro to copy and add (i dont want to replace) the content of
sheet 1 to sheet october. But i have in sheet 1, M1, a cell with october. I
need the macro to look in M1 and to add content of sheet 1 columns A:J to the
corresponding sheet.
If in M1 i have October, then the macro to add to the october sheet. If i
have november in M1, then to copy content to november sheet.
Can this be done?
Thanks!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro-copy puiuluipui Excel Discussion (Misc queries) 0 September 17th 09 02:19 PM
Copy workbook, don't copy macro CongroGrey Excel Discussion (Misc queries) 1 June 13th 08 04:56 PM
macro copy Pammy Excel Discussion (Misc queries) 1 April 28th 07 03:48 PM
Copy a macro? Melissa Excel Discussion (Misc queries) 2 July 26th 06 08:32 PM


All times are GMT +1. The time now is 07:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"