Help with a macro again

bpflyr

Board Regular
Joined
Nov 7, 2005
Messages
116
I actually got an answer to this question a while ago, but kept getting a syntax error with the new macro, so I am posting the question again.

I have two workbooks, 'Data Entry' and 'Log Entry' (the one worksheet within them are the same names). The macro listed below will copy 4 rows of data from 'Data Entry' and paste it into 'Log Entry'.
(The macro is in 'Log Entry').

Can anyone please add to my macro so that each time I run it, the macro will look for the next blank row in 'Log Entry'! and do it's paste there? I am erasing all the data in 'Data Entry'! after each use.

Thanks in advance. Here's the macro:

Windows("BDataEntry.xls").Activate
Range("A4:AG8").Select
Selection.Copy
Windows("BLogbook.XLS").Activate
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=True _
, Transpose:=False
End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
I'm new to VBA...but picked things up quickly...learnt in a day at work as I had nothing else to do...but surely you want this line...

" SkipBlanks:=True _ "

to be set to False...as far as I would guess it's skipping all blanks and only pasting in the ones with stuff in.

I could be wrong...as I said I am new to this.
 
Upvote 0
I am assuming that you want to paste the data in columns A:AG in BLogbook. The following will probably do what you want.

Range("A65536").End(xlUp).Offset(1, 0).Select
 
Upvote 0
Sorry, I hit submit to quickly. This shows where the line should be inserted in your existing code.

Windows("BDataEntry.xls").Activate
Range("A4:AG8").Select
Selection.Copy
Windows("BLogbook.XLS").Activate
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=True _
, Transpose:=False
 
Upvote 0
Hi, guys,
avoid to select
for example: Range("A4:AG8").Copy
it would be safer to tell the code which sheet to copy and to paste to

Code:
Windows("BDataEntry.xls").Activate
'you would better specify a sheet in next line
Range("A4:AG8").Copy
Windows("BLogbook.XLS").Activate
'you would better specify a sheet in next line
NR = Cells(Rows.Count).End(xlUp).Row + 1    'NR = next row :-)
Cells(NR, 1).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=True _
, Transpose:=False
End Sub

kind regards,
Erik

EDIT: "hit submit to early" you can edit your post or delete it (when not replied to)
using the "code"button let appear your code +- the way it is in the VBEditor
 
Upvote 0

Forum statistics

Threads
1,217,448
Messages
6,136,685
Members
450,024
Latest member
Beagle263

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
Back
Top