Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro-copy | Excel Discussion (Misc queries) | |||
Copy workbook, don't copy macro | Excel Discussion (Misc queries) | |||
macro copy | Excel Discussion (Misc queries) | |||
Copy a macro? | Excel Discussion (Misc queries) |