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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

AlanPops

New Member
Joined
May 12, 2016
Messages
5
Thanks Mark, Will give it a try. I know what you mean re: macro & formula, I didn't actually mean that ;)

Cheers
 

AlanPops

New Member
Joined
May 12, 2016
Messages
5
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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
 

AlanPops

New Member
Joined
May 12, 2016
Messages
5
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,033
Members
414,356
Latest member
death20

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