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.

 

[TPortsmouth]

Hi Dave,

Thanks for your prompt reply. Your understanding is correct. I've have some additional points to make.

I've simplified my actual file, the range is different (not just A2:E2, it can be A2:Z2) while some columns contain variable (needs to be removed the value and keep the format), some columns contain formula (should remain unchanged).

Pls let me know if further explanation is needed.

Regards,

TPortsmouth

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]

Hi

So would post 10 have worked in your original data set.

In your new data set, if the formula is to be entered with code, please tell me the formula.

is there any reason why we are using A2:D2 as the copy each time, could it in face always be the last row of data that is copied into the next empty row??

Thanks

Dave

 

[SQUIDD]

it would seem to me, actually, this will work for you on your new data set if you are happy to always use the lasr row of data as the formatted source for the new balnk row.

Sub new_test()
lr = Range("B" & Rows.Count).End(xlUp).Row
    Range("A" & lr & ":K" & lr).AutoFill Destination:=Range("A" & lr & ":K" & lr + 1), Type:=xlFillDefault
    Range("B" & lr + 1 & ":F" & lr + 1).ClearContents
    Range("H" & lr + 1 & ":J" & lr + 1).ClearContents
End Sub

For sake of simplicity

This code, in your example actually copy A4:K4 into A5:K5.

 

[TPortsmouth]

Hi Dave,

Thanks! This logic is exactly want I needed. However, as my actual file is quite complicated and contain quite a lot of formula and variable, can you tell me how to modify the code by myself?

Seems like your code will copy the last row and goes into the bottom row and create a new row, then delete some area's value.

Only column G and K will keep the formula, while column A will have a value fill down (why?) and the remaining value will be deleted.

Can you highlight those code I can manually adjust?

For example below:
Area to copy : Always A2:Z2 (I prefer to copy a specific row so that I can hide this in order to prevent end user accidentally change other format or layout.
Columns contain formula: A2, E2:G2, J2:K2, M2, P2:X2.
Columns to ClearContents: A2:Z2 minus A2, E2:G2, J2:K2, M2, P2:X2

Apart from that, which identifier you used to define the last row? I can see the script "lr = Range("B" & Rows.Count).End(xlUp).Row", does it mean always refer to the last row from column B +1 row?

Thanks so much for your help.

TPortsmouth

it would seem to me, actually, this will work for you on your new data set if you are happy to always use the lasr row of data as the formatted source for the new balnk row.

Sub new_test()
lr = Range("B" & Rows.Count).End(xlUp).Row
    Range("A" & lr & ":K" & lr).AutoFill Destination:=Range("A" & lr & ":K" & lr + 1), Type:=xlFillDefault
    Range("B" & lr + 1 & ":F" & lr + 1).ClearContents
    Range("H" & lr + 1 & ":J" & lr + 1).ClearContents
End Sub

For sake of simplicity

This code, in your example actually copy A4:K4 into A5:K5.

 

[SQUIDD]

hi

Seems like your code will copy the last row and goes into the bottom row and create a new row, then delete some area's value.

this is correct, except it pulls the lastrow down 1 row, same a pulling a formula down.

Only column G and K will keep the formula, while column A will have a value fill down (why?) and the remaining value will be deleted

.

your last row of data for pull down in column A must have been a value rather than formula, even if any above that was formula, as we are oly dragging the lastrow down 1 row.

Apart from that, which identifier you used to define the last row? I
can see the script "lr = Range("B" & Rows.Count).End(xlUp).Row",
does it mean always refer to the last row from column B +1 row?

absolutly correct, last row of B, the + is to make it refer to the next empty and B can be changed to any column you wish.

I will post back the code with comments on it in 10 mins.

i will adjust it slightly and show you how you can amend it.

there would perhaps be a better way to code it, but you would need to learn how to enter formula from vba, sounds like you have a few?

Dave

 

[SQUIDD]

Hi

ok so i will mark in red the variables you can change, depending on your data you may need to apply additional lines of code to clear additional values.

Sub new_test()
lr = Range("[COLOR=#ff0000]B[/COLOR]" & Rows.Count).End(xlUp).Row 'this finds the last row of column B with data in
    Range("[COLOR=#ff0000]A[/COLOR]" & lr & ":[COLOR=#ff0000]K[/COLOR]" & lr).AutoFill Destination:=Range("[COLOR=#ff0000]A[/COLOR]" & lr & ":[COLOR=#ff0000]K[/COLOR]" & lr + 1), Type:=xlFillDefault 'this selects A:K lastrow of data(defined by B above) and fills down 1 row(hence the +)
    Range("[COLOR=#ff0000]B[/COLOR]" & lr + 1 & ":[COLOR=#ff0000]F[/COLOR]" & lr + 1).ClearContents 'this clears cells values since we pulled the previous rows values down in code above(clears B:F lastrow + 1)
    Range("[COLOR=#ff0000]H[/COLOR]" & lr + 1 & ":[COLOR=#ff0000]J[/COLOR]" & lr + 1).ClearContents 'this clears cells values since we pulled the previous rows values down in code above(clears H:J lastrow + 1)
End Sub

Though, i must tell you, this code simple relies on always copying the lastrow down 1 row, so as you use it, on the next cycle, the row you just created becomes the new row to copy down.

I believe you do not want this, so as simple as the code is above, something else will involve more code and understanding.

Dave

 

[SQUIDD]

ok, now i have actually realised what you are doing, i think this code will be easier to understand, be more efficient and certianly allow you to make it into whatever you require, by simply adding what you need/want.

1st part finds the next empty row
2nd part copies your range and pastes formatting only
3rd,4th and 5th part all do the same thing, copy the formula from row 2 in the relevent column and pastes it into the next empty row

i think the code is very self explanitary.

i think this will work perfectly for you. you can add more lines of the formula copy code for as many columns as you want.

variables in red again for you

Sub new_test()

lr = Range("[COLOR=#ff0000]B[/COLOR]" & Rows.Count).End(xlUp).Row + 1

Range("[COLOR=#ff0000]A2:K2[/COLOR]").Copy
Range("[COLOR=#ff0000]A[/COLOR]" & lr).PasteSpecial Paste:=xlPasteFormats

Range("[COLOR=#ff0000]A2[/COLOR]").Copy
Range("[COLOR=#ff0000]A[/COLOR]" & lr).PasteSpecial Paste:=xlPasteFormulas

Range("[COLOR=#ff0000]G2[/COLOR]").Copy
Range("[COLOR=#ff0000]G[/COLOR]" & lr).PasteSpecial Paste:=xlPasteFormulas

Range("[COLOR=#ff0000]K2[/COLOR]").Copy
Range("[COLOR=#ff0000]K[/COLOR]" & lr).PasteSpecial Paste:=xlPasteFormulas

Application.CutCopyMode = False

End Sub

good luck, dave

 

[TPortsmouth]

Hi Dave,

Great, I've almost come to an end to my work, the only missing point is the "Data Validation".

I can see below code can paste the Format

Range("A2:K2").Copy
Range("A" & lr).PasteSpecial Paste:=xlPasteFormats

Can you also tell me know to copy the "Data Validation" as well?

Apart from that, what's the meaning of lr?

Thanks.

TPortsmouth

 

[TPortsmouth]

I just found the solution by navigating the forum. Simple change the code "xlPasteFormats" into "xlPasteValidation" will do.

Code as below:

Range("B2").Copy
Range("B" & lr).PasteSpecial Paste:=xlPasteValidation

Eventually, it works! Thanks for your inspiration, Dave, cheers

BTW, what's the meaning of lr?

Hi Dave,

Great, I've almost come to an end to my work, the only missing point is the "Data Validation".

I can see below code can paste the Format

Range("A2:K2").Copy
Range("A" & lr).PasteSpecial Paste:=xlPasteFormats

Can you also tell me know to copy the "Data Validation" as well?

Apart from that, what's the meaning of lr?

Thanks.

TPortsmouth

 

[SQUIDD]

You are very welcome for the help.

Glad you found the answer to your last question, well done.

The meaning of lr.

Well, to be honest it has no meaning, but its short for LAST ROW.

This is just a srting that we decided to use

lr = Range("B" & Rows.Count).End(xlUp).Row + 1


could have been

TPortsmouth = Range("B" & Rows.Count).End(xlUp).Row + 1

then we would have said

Range("A" & TPortsmouth).PasteSpecial Paste:=xlPasteFormats

or we could have not defined this at all and simply put

Range("A" & Range("B" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteFormats

so all we done was make up a string that would = something else

so as we user Lr in the code several times

we just made lr = Range("B" & Rows.Count).End(xlUp).Row + 1 in the beginning of the code.

hope that makes sense.

Dave