excel 2003 macro copy row to empty row in ano

Microsoft Excel 2003 (full product)
November 30, 2010 at 15:09:42
Specs: Windows XP
I need a macro to copy row 2 from a sheet named "vividexcelexport" in an active workbook (it's workbook name varies but it will be open and active) and paste it into the next blank row of sheet 1 of a closed workbook named c:\excel\log.xls. Please note there is only 1 sheet in both the active workbook and the log.xls

Any help is appreciated and thanks in advance.


See More: excel 2003 macro copy row to empty row in ano

Report •

#1
November 30, 2010 at 18:06:28
You can't write to a closed workbook.

The code would have to open it, copy the row, save it and close it.

The code below will do just that.

If you hide log.xls (Windows...Hide) and then quit Excel, it will ask you if you want to save the changes to log.xls. Say yes. By doing that, you won't see log.xls open when you run the code.

When you want to look at log.xls, open it and then use Windows...Unhide so that you can view it. If you don't make any changes after viewing it, you don't have to hide it again, just close it without saving and it will remain hidden the next time the code runs. If you make changes that you need to save, then you'll need to hide it again and the quit Excel in order to save it.

Of course you could always write a macro to hide it, save it and close it so you don't have to quit Excel in order to save it as hidden.

Anyway, here's the code to copy Row 2.

Option Explicit
Sub WriteToLog()
Dim curName As String
Dim nxtRw As Integer
'Open log.xls (Hidden if saved Hidden)
  Workbooks.Open Filename:="C:\Excel\log.xls"
'Determine next empty row in log.xls
   nxtRw = Workbooks("log.xls").Sheets(1).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Activeworkbook row to to log.xls
    Workbooks(ActiveWorkbook.Name).Sheets(1).Rows("2:2").Copy _
         Destination:=Workbooks("log.xls").Sheets(1).Rows(nxtRw)
       Application.CutCopyMode = False
'Save and close log.xls
       Workbooks("log.xls").Save
       Workbooks("log.xls").Close
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 3, 2010 at 10:56:10
Thank you for the code. Unfortunately it does not do exactly what I was hoping for. Maybe I did not give the best description. It open copies save and closes line 2 on log.xls down to the next available row on log.xls. I need it to copy from line 2 of the open active workbook. This source workbook name varies but the sheet name is always VividExcelExport. I tried changing the sheet name of the active workbook to sheet1 but that did not make any difference.

Again I say thank you.
Jeff


Report •

#3
December 3, 2010 at 11:36:31
I assume that you have not hidden log.xls. If you do, it will do what you asked.

I wrote one version of the code with log.xls not hidden and then modified and tested it after I had hidden log.xls. What I neglected to do was to test the modified version when log.xls is not hidden.

What happens is that when log.xls is hidden, it does not become the ActiveWorkbook when opened, so Row 2 is copied from the workbook from which the code was called from.

However, if log.xls opens unhidden, it become the ActiveWorkbook and the code operates on it - even though it resides in a different workbook.

Therefore it copies from log.xls to log.xls.

The version below will work in either case since it saves the name of the workbook from which the code was run and uses that name as the source for the copy.

Sorry about that!

Option Explicit
Sub WriteToLog_v2()
Dim curName As String
Dim nxtRw As Integer
curName = ActiveWorkbook.Name
'Open log.xls (Hidden if saved Hidden)
  Workbooks.Open Filename:="C:\Documents and Settings\david_marulli\Desktop\log.xls"
'Determine next empty row in log.xls
   nxtRw = Workbooks("log.xls").Sheets(1).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Activeworkbook row to to log.xls
    Workbooks(curName).Sheets(1).Rows("2:2").Copy _
         Destination:=Workbooks("log.xls").Sheets(1).Rows(nxtRw)
       Application.CutCopyMode = False
'Save and close log.xls
       Workbooks("log.xls").Save
       Workbooks("log.xls").Close
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 6, 2010 at 12:28:48
Mr. DerbyDad03 This works perfect. Thanks for your help, I learned a lot from the code.

Jeff


Report •

#5
December 6, 2010 at 13:36:58
Glad I could help.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Ask Question