Copy/paste new selection

Foxy Lady

New Member
Joined
Feb 25, 2009
Messages
41
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Similar to your other post:
Code:
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

New Member
Joined
Feb 25, 2009
Messages
41
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,671
Messages
5,654,655
Members
418,146
Latest member
Shnn028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top