Macro-Copy/add

[puiuluipui]

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!

 

[Per Jessen]

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

 

[FloMM2]

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]

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: