Help with macro Please!

AlanPops

New Member
Joined
May 12, 2016
Messages
5
Hi all, Would a kind person write me a macro formula that I can copy and paste into my worksheet. I used to work with excel in my job, but I've been retired for years and have forgotten how to use it.

I have a sheet named 'Data Input', Cells B3-G3 and I want to copy the data from these and paste it into a list of data in a sheet named 'All'. Once this is done, I would like to 'clear' the data from the 'Data Input' cells ready for the next set of data.

I have had several attempts at doing this myself, but I keep getting data into the wrong cells or overwriting previous input.

I'd be so grateful if someone could help out please. I think I've lost it!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Try testing the below. Put the code in a regular module (ALT + F11, insert - module), paste the code in the window that appears. Close editor.

To run ALT + F8, select the sub called testsub and click Run.

Code:
Sub testsub()
Sheets("Data Input").Range("B3:G3").Copy Sheets("All").Cells(Rows.Count, "A").End(xlUp)(2)
Sheets("Data Input").Range("B3:G3").ClearContents
End Sub

I have assumed as you haven't stated it that you want the code copied to column A in sheet All.

write me a macro formula
BTW, just to point out a macro is not a formula. They are different things.
 
Last edited:
Upvote 0
Thanks Mark, Will give it a try. I know what you mean re: macro & formula, I didn't actually mean that ;)

Cheers
 
Upvote 0
Hi Mark, Tried it and it works very well. Thanks VM.

Just one thing. My 'Data Input' Sheet has different Formatting to the 'All' Sheet. Could you show me how to alter the macro so that it pastes 'Values and Number formats'.

I don't want to appear ungrateful, I'm not, I really appreciate what you've done.

Thanks again.

Alan
 
Upvote 0
Could you show me how to alter the macro so that it pastes 'Values and Number formats'.

Code:
Sub testsub2()
    Sheets("Data Input").Range("B3:G3").Copy
    Sheets("All").Cells(Rows.Count, "A").End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormats
    Sheets("Data Input").Range("B3:G3").ClearContents
End Sub
 
Upvote 0
Hi Mark,

Just wanted to say a big thank you for helping out, if it wasn't for people like you I'd have given up.

Thanks also to all those on this forum that help out those of us, who can't!

Alan
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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
Back
Top