Copy/paste new selection

[Foxy Lady]

Hi all,

. I need to create a button where when pressed it will copy range; A31-A45 to next availible row in Sheet A and a message box appears asking to enter month. When month has been entered it should appear in A32. I don't want the data to be auto formated/adjusted only code needs to be transfered
2. then when the above selection is pasted into Sheet A, Range A32-A33 should be copied to the next availible row in Sheet 2

Can this be done?

Hope you can help me.

Regards

 

[jbeaucaire]

Similar to your other post:

Option Explicit

Sub Copytest()
Dim NR As Long, MyStr As String
NR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1   'change row used if A is unreliable for finding last row

MyStr = Application.InputBox("Enter Month", "Info needed")
Sheets("Sheet1").Range("A32") = MyStr

Sheets("Sheet1").Range("A32:A45").Copy
Sheets("Sheet2").Range("A" & NR).PasteSpecial xlPasteValues    'or xlPasteAll

End Sub

 

[Foxy Lady]

Hi there,

This is how I created the macro

Sub New_Month()
'
' New_Month Macro
'

'

Rows("31:44").Select
Selection.Copy
Range("A47").Select
ActiveSheet.Paste
Rows("31:33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ProRisk").Select
Range("A1").Select
ActiveSheet.Paste
Range("O3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Commision Paid"
Range("N3").Select
Selection.Copy
Range("O3").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("O:O").ColumnWidth = 11.71
Sheets("Master").Select
End Sub

It works fine for the first time, however when i copy and paste it again it should go to the next open line of each sheet.

Also the formula's must stay the same eg. in column J the following formula appears
=SUMIF(M34:M37,C3,J34:J37)= C3 must alway stay and the others adjusted accordingly.

Hope you can help me with this

Regards