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

[TPortsmouth]

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.

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?

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

 

[SQUIDD]

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]

The code below will make your data in your top table look like the data in your second table after you run it.

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

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

 

[SQUIDD]

and just in case

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

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]

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.

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]

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]

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?

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

 

[TPortsmouth]

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?

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

 

[SQUIDD]

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]

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.

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