Macro to copy specific section at the bottom of existing data set

TPortsmouth

New Member
Joined
Apr 6, 2017
Messages
41
Hi mates,

I would like to seek help on building some Macro.

I have a table for end user data input, let's say monthly record of sales figure.

For example, row 2 is the first record, while B2, C2 & D2 are for end user's data input, and E2 is a formula.

doroyb.jpg


I would like to create a Macro fro below purpose:
  1. Copy area A2:E2.
  2. Go to the last row of the existing data set (for this case is A5).
  3. Paste A2:E2 into A5:E5.

If I need to build such Macro, what should be the data source for copy? As I need a blank result data (where A5:D5 should contain null value, only the column E5 with formula should be copy). Do I need to create a blank dummy record for copy purpose?

3460uux.jpg


Any idea? Your help is highly appreciated, thank you.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
hi

i dont quite follow what you are asking.

Are you saying that you simply want the value from E2 to go into the last row of column E??

Dave
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
The code below will make your data in your top table look like the data in your second table after you run it.

Code:
Sub test()
lr = Range("E" & Rows.Count).End(xlUp).Row + 1
Range("E" & lr) = Range("e2").Value
End Sub

and this code will copy the whole lot including what you have crossed out

Code:
Sub test()
LR = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & LR & ":E" & LR) = Range("A2:E2").Value
End Sub
 
Last edited:

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
and just in case

this code will sum the figures in the lastrow from B:D and put the total in E.

Code:
Sub test()
LR = Range("E" & Rows.Count).End(xlUp).Row + 1
Range("E" & LR) = Range("B" & LR) + Range("C" & LR) + Range("D" & LR)
End Sub

Dave
 

TPortsmouth

New Member
Joined
Apr 6, 2017
Messages
41

ADVERTISEMENT

Hi Dave,

Thanks for your help. But actually, the general idea of this Macro is to help user to create a new blank row for data entry, and in which A1:D1 contain some format which needs to be copy (not copy the data), while the column E2 contain the formula (needs to copy both format and data).

Please let me know if further elaboration is needed, thanks.


and just in case

this code will sum the figures in the lastrow from B:D and put the total in E.

Code:
Sub test()
LR = Range("E" & Rows.Count).End(xlUp).Row + 1
Range("E" & LR) = Range("B" & LR) + Range("C" & LR) + Range("D" & LR)
End Sub

Dave
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
Hi

so the code posted above is ok to sum your data in column E but you want A:D & lastrow to be formatted as per A2:D2.

correct?

Dave
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690

ADVERTISEMENT

Hi

ok, if the above is correct then this code will copy and paste the cell formats from A2:D2 and put it into the last row without data in column A.

But i must say, i am still confused about if you will be using a formula in column E or you wish the code to sum your values?

Code:
Sub test()
lr = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A2:D2").Copy
Range("A" & lr).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Dave
 
Last edited:

TPortsmouth

New Member
Joined
Apr 6, 2017
Messages
41
Hi Dave,

Thanks for your help. I am not trying to sum the range. I have an Excel file with a long list of information (I just simplified the content and took some data set for example).

What I need end user to do is to copy the row (contain placeholder for data entry, as well as some formula) and paste in at the bottom of the existing data and do the data entry.

To make it simple. The logic is below:
1. Copy the data range A2:E2
2. Go to the end of the existing data (that's E1) range and paste the data.
3. Remove the data value A5:D5 (As these are supposed to be entry by end user's new data), keep the E5 formula, as it is used for calculation.

I am trying to write the Macro by myself, but I am a newbie to VBA, thus I need some expert's help, later on, I still need to fine tone the code to suit the actual file.

Hope this is clear, and thanks for your help.

Hi

ok, if the above is correct then this code will copy and paste the cell formats from A2:D2 and put it into the last row without data in column A.

But i must say, i am still confused about if you will be using a formula in column E or you wish the code to sum your values?

Code:
Sub test()
lr = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A2:D2").Copy
Range("A" & lr).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Dave
 
Last edited:

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
Hi

so
point 1 you are copying data and formatting From A2:E2 and E2 copying the formula.
point 2 you want A2:D2 values and formatting pasted into your A:D lastrow, and also the formula that sums A:D lastrow data.
point 3 you want to remove the values form A:D lastrow keeping formatting and also leaving the formula In E lastrow.

It the above correct??

Dave
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
The code below will copy formatting of b2:d2 and paste the formatting only into your B:D lastrow, it will the apply the formula into column E lastrow.

Code:
Sub test()
LR = Range("B" & Rows.Count).End(xlUp).Row + 1
Range("A2:D2").Copy
Range("A" & LR).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("E" & LR).Formula = "=SUM(B" & LR & ":D" & LR & ")"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,129
Messages
5,599,898
Members
414,343
Latest member
JennyGarcia

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