Need help with a macro

[bpflyr]

I have a macro that someone gave to me and it almost does what I need. I have two workbooks, Data Entry and Log Entry. I am trying to get the macro to copy from Data Entry A4:AG20 and paste into Log Entry on the next available blank row BELOW any existing rows that have data. All the column headings are the same in the two workbooks.

This macro will just add a row to the top or overwrite any data I have already. Can you please help?

Thank you in advance. Here's the existing macr (it is in Log Entry):

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

 

[Banker1]

Here is what I came up with. The macro sits in the Data workbook, I have named the Log workbook "Log Entry.xls"

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()
<SPAN style="color:#00007F">Dim</SPAN> DataWB <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> LogWB <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

DataWB = ThisWorkbook.Name
LogWB = "Log Entry.xls"

Range("A4:AG8").Copy

Windows(LogWB).Activate

<SPAN style="color:#00007F">If</SPAN> Range("A65536").End(xlUp).Value = "" <SPAN style="color:#00007F">Then</SPAN>
    Range("A65536").End(xlUp).PasteSpecial Paste:=xlAll, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, _
        Transpose:=False
<SPAN style="color:#00007F">Else</SPAN>
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlAll, Operation:=xlPasteSpecialOperationNone, _
        SkipBlanks:=<SPAN style="color:#00007F">False</SPAN>, Transpose:=False
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

Windows(DataWB).Activate

Application.CutCopyMode = False

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

If you want to put a space in between each paste operation change this:

<font face=Courier New><SPAN style="color:#00007F">Else</SPAN>
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlAll, Operation:=xlPasteSpecialOperationNone, _
        SkipBlanks:=False, Transpose:=False
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
</FONT>

to this (Offset is now 2 instead of 1):

<font face=Courier New><SPAN style="color:#00007F">Else</SPAN>
    Range("A65536").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlAll, Operation:=xlPasteSpecialOperationNone, _
        SkipBlanks:=False, Transpose:=False
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
</FONT>

 

[HalfAce]

Yes, I too had an issue with the workbook names in the code as opposed to their names in your text description. I named them "Data Entry" and "Log Entry". I also assume the copy and paste operations are being done on (& to) sheet1 of each workbook. (Change any of those you need to suit.)
Here's how the code you provided can be made to work.

Sub Demo()
Application.ScreenUpdating = False
Windows("Data Entry.xls").Activate
Sheets("Sheet1").Select
Range("A4:AG8").Copy
Windows("Log Entry.xls").Activate
Sheets("Sheet1").Select
Cells(Rows.Count, 1).End(xlUp)(2, 1).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=True _
, Transpose:=False
With Application
    .ScreenUpdating = True
    .CutCopyMode = False
End With
End Sub

Hope it helps.

 

[bpflyr]

My apologies. The workbook to copy from is BDataEntry, sheet Data Entry. The workbook to copy TO is BLogbook, sheet Log Entry.

The Macro needs to be in BLogbook.

HalfAce, your macro looks like it will work, but I keep getting a subscript error. The Debugger says it is in the third line of code. Any help?
Thanks.

 

[HalfAce]

Yep, error'ed out because it couldn't find a sheet named "Sheet1"
Try this instead.

Sub Demo()
Application.ScreenUpdating = False
Windows("BDataEntry.xls").Activate
Sheets("Data Entry").Select
Range("A4:AG8").Copy
Windows("BLogbook.xls").Activate
Sheets("Log Entry").Select
Cells(Rows.Count, 1).End(xlUp)(2, 1).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=True _
, Transpose:=False
With Application
    .ScreenUpdating = True
    .CutCopyMode = False
End With
End Sub

That work any better?

 

[bpflyr]

I am still getting a subscript error with the code line:

Windows("BDataEntry.xls").Activate

Any reason? or solutions? Thanks for the quick response by the way!!

 

[bpflyr]

never mind, I just checked it again and it WORKED!! Thank You!!!!

 

[HalfAce]

You're most welcome.
The only reason I can think of for it to error out (provided everything is named properly - which it seems to be if it worked once) would be if your BDataEntry wb is not already open when you run the code. If this is ever going to be a problem we could have the code open the workbook, copy & paste and then close the workbook down again if you'd like.