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 inspiration, now I am getting more confident in building simple VBA. And this VBA had been embedded into an xlsm file for data input.

However, end user just had another additional request. This Macro run well, however, user sometimes needs to create a lot of records, let say, if they need to create 100 records, they need to click the Macro button 100 times.

So I am thinking the below solution:

1. Create another button named "Add multiple record".
2. After clicking, a msg box "How many records you want to create?"
3. The number of new record will based on the figure entered by end user.

I know how to build a msg box, but I don't know how to based on user's input and use that to create multiple record set.

Can you also provide suggestion on this? Thank you.

TPortsmouth

 

[SQUIDD]

Hi

I would say, and this is untested.

But

at the start of the code have a message box.

and for your understanding i will use a randow word again here.

dog = msgbox("Please enter how many records you wish to add")

user selects the amount of records to add, we can then add this into a loop, like below.

for car = 1 to to dog

'all other code goes here

next car

so dog = user input lets say 12

the for loop then means, for car = 1 to 12(as dog = 12 from msgbox)

finally next car means do add the next record

so the above example will add 12 records.

make sense??

will post full example of loop in next post.

dave

 

[SQUIDD]

here you go

sub test()

dog = msgbox("Please enter how many records you wish to add")

for car = 1 to dog
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

next car

Application.CutCopyMode = False

End Sub

where you may have a problem is where the code is looking for the last data input as range B is user input, so you may have to play around with that.

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

Maybe change this to column A, its your data dependent.

Dave

 

[TPortsmouth]

I am trying on this, but what's the code to show the UI for user to input figure?

After the row dog = msgbox("Please enter how many records you wish to add")?

here you go

sub test()

dog = msgbox("Please enter how many records you wish to add")

for car = 1 to dog
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

next car

Application.CutCopyMode = False

End Sub

where you may have a problem is where the code is looking for the last data input as range B is user input, so you may have to play around with that.

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

Maybe change this to column A, its your data dependent.

Dave

 

[SQUIDD]

yes, my bad, sorry mate.

dog = InputBox("Please enter how many records you wish to add", "records to add", "input number")

Sub test()


dog = InputBox("Please enter how many records you wish to add", "records to add", "input number")

For car = 1 To dog
lr = Range("A" & Rows.Count).End(xlUp).Row + 1

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

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

Range("G2").Copy
Range("G" & lr).PasteSpecial Paste:=xlPasteFormulas

Range("K2").Copy
Range("K" & lr).PasteSpecial Paste:=xlPasteFormulas

Next car

Application.CutCopyMode = False

End Sub

like i said, change column B to somewhere that will get a value to find the lastrow.

If you dont you will get undesired results.

Dave

 

[SQUIDD]

this is the same, but you will see its faster and no screen flicker.

Sub test()

Application.ScreenUpdating = False
dog = InputBox("Please enter how many records you wish to add", "records to add", "input number")

For car = 1 To dog
lr = Range("A" & Rows.Count).End(xlUp).Row + 1

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

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

Range("G2").Copy
Range("G" & lr).PasteSpecial Paste:=xlPasteFormulas

Range("K2").Copy
Range("K" & lr).PasteSpecial Paste:=xlPasteFormulas

Next car

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Dave

 

[TPortsmouth]

Hi Dave,

Thanks a lot, I especially like the code Application.ScreenUpdating = True, extremely useful!

I am thinking of putting a default value (1) as copy row so I changed the code a little bid as:

dog = InputBox("Please enter how many records you wish to add", "records to add", 1)

I am also interested to know the quote "records to add". What's the usage? As I don't see these word appear anywhere.

Apart from that, I still have some queries, as user might click Cancel and decide not to copy, and it will bring out Run-time error '13':

Can this screen also be remove? That is, if user click Cancel, it would not bring out the VB editor.

Finally, as I still have more code by this Macro button, can I assume this code will only loop before the line "Next car"? That means, the code after this line would not be repeat?

this is the same, but you will see its faster and no screen flicker.

Sub test()

Application.ScreenUpdating = False
dog = InputBox("Please enter how many records you wish to add", "records to add", "input number")

For car = 1 To dog
lr = Range("A" & Rows.Count).End(xlUp).Row + 1

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

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

Range("G2").Copy
Range("G" & lr).PasteSpecial Paste:=xlPasteFormulas

Range("K2").Copy
Range("K" & lr).PasteSpecial Paste:=xlPasteFormulas

Next car

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Dave

 

[SQUIDD]

hi

glad to help

the default of 1 is a great idea, you found how to change that. well done.

as for "records to add", look in the top right of the input box, the caption at the top, you dont have to add any of this to be honest, but i done it so you could see it.

simply
dog = InputBox("Please enter how many records you wish to add")

would work

new code below with the cancel feature for example

loop info included in the code with notes.

Sub test()

Application.ScreenUpdating = False

dog = InputBox("Please enter how many records you wish to add", "records to add", 1)

If dog = "" Or dog = 0 Then MsgBox ("cancelled by user"): Exit Sub ' this checks for cancel or 0
'all code above here will not loop
For car = 1 To dog
lr = Range("A" & Rows.Count).End(xlUp).Row + 1

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

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

Range("G2").Copy
Range("G" & lr).PasteSpecial Paste:=xlPasteFormulas

Range("K2").Copy
Range("K" & lr).PasteSpecial Paste:=xlPasteFormulas

Next car
'all code under here will not loop

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[SQUIDD]

you could also use the addition of

If Not IsNumeric(dog) Then MsgBox "This is not a number, please enter a number": GoTo 0

added to the code below to check the input is numeric. try to put your name in for example, it will loop back to 0:

Sub test()
Application.ScreenUpdating = False
0:
dog = InputBox("Please enter how many records you wish to add", "records to add", 1)

If dog = "" Or dog = 0 Then MsgBox ("cancelled by user"): Exit Sub ' this checks for cancel or 0
If Not IsNumeric(dog) Then MsgBox "This is not a number, please enter a number": GoTo 0 ' makes sure input is numeric

'all code above here will not loop
For car = 1 To dog
lr = Range("A" & Rows.Count).End(xlUp).Row + 1

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

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

Range("G2").Copy
Range("G" & lr).PasteSpecial Paste:=xlPasteFormulas

Range("K2").Copy
Range("K" & lr).PasteSpecial Paste:=xlPasteFormulas

Next car
'all code under here will not loop

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Dave

 

[TPortsmouth]

Hi Dave,

Excellent!

Can I further limit user from entering text? That means apart from cancel or 0, if user enter any text or symbol, it would also brings the second dialog box.

hi

glad to help

the default of 1 is a great idea, you found how to change that. well done.

as for "records to add", look in the top right of the input box, the caption at the top, you dont have to add any of this to be honest, but i done it so you could see it.

simply
dog = InputBox("Please enter how many records you wish to add")

would work

new code below with the cancel feature for example

loop info included in the code with notes.

Sub test()

Application.ScreenUpdating = False

dog = InputBox("Please enter how many records you wish to add", "records to add", 1)

If dog = "" Or dog = 0 Then MsgBox ("cancelled by user"): Exit Sub ' this checks for cancel or 0
'all code above here will not loop
For car = 1 To dog
lr = Range("A" & Rows.Count).End(xlUp).Row + 1

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

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

Range("G2").Copy
Range("G" & lr).PasteSpecial Paste:=xlPasteFormulas

Range("K2").Copy
Range("K" & lr).PasteSpecial Paste:=xlPasteFormulas

Next car
'all code under here will not loop

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub