Help with copy macro

[Crownman]

Hi

I am trying to use the macro recorder in Excel 2003 to create a macro
that will copy selected data from one spreadsheet into another
spreadsheet. The copy to spreadsheet is an Excel database and I need
the macro to find the first blank row in the database and then do the
copy. My code so far is as follows:

Sub Copy_Feed_data()
'
' ' Keyboard Shortcut: Ctrl+m
'
Sheets("FEED").Select
Range("A2:M3").Select
Selection.Copy
Windows("ASCI PRICE DATABASE.xls").Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.SmallScroll Down:=-12
Range("A6").Select
Selection.End(xlDown).Select
Range("A28").Select
ActiveSheet.Paste
ActiveWindow.WindowState = xlMinimized
End Sub

When I recorded the macro I used the end down keystrokes and then
keyed down one more row which was the first blank row when I recorded
the macro. The marco interprets ths as selecting row 28 instead of
the first blank row.

Can someone help me with the correct command to get to the first blank
row instead of the specific row that was done when the macro was
recorded?

Thanks for any advice.

Crownman

 

[Crownman]

Hi

I am trying to use the macro recorder in Excel 2003 to create a macro
that will copy selected data from one spreadsheet into another
spreadsheet. The copy to spreadsheet is an Excel database and I need
the macro to find the first blank row in the database and then do the
copy. My code so far is as follows:

Sub Copy_Feed_data()
'
' ' Keyboard Shortcut: Ctrl+m
'
Sheets("FEED").Select
Range("A2:M3").Select
Selection.Copy
Windows("ASCI PRICE DATABASE.xls").Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.SmallScroll Down:=-12
Range("A6").Select
Selection.End(xlDown).Select
Range("A28").Select
ActiveSheet.Paste
ActiveWindow.WindowState = xlMinimized
End Sub

When I recorded the macro I used the end down keystrokes and then
keyed down one more row which was the first blank row when I recorded
the macro. The marco interprets ths as selecting row 28 instead of
the first blank row.

Can someone help me with the correct command to get to the first blank
row instead of the specific row that was done when the macro was
recorded?

Thanks for any advice.

Crownman

I did a Google search and found the answer.

Selection.End(xlDown).Offset(1,0).Select

Crownman

 

[Don Guillett]

assumes both are open and you are in the source sheet.

Sub CopyToOpenSheet()
With Workbooks("destination.xls").Sheets("sheet1")
dlr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
Sheets("source").Range("a2:m3").Copy .Cells(dlr, 1)
End With
End Sub

 

[Crownman]

assumes both are open and you are in the source sheet.

Sub CopyToOpenSheet()
With Workbooks("destination.xls").Sheets("sheet1")
dlr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
Sheets("source").Range("a2:m3").Copy .Cells(dlr, 1)
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software











- Show quoted text -

Thank you for your advice.

I intend to have the destination file open along with several source
files. I then run the macro on each source file in sequence. After I
added the offset reference and took out the line for A28, everything
worked perfectly.

Crownman

 

[Don Guillett]

If all source files are pretty much the same you could do this from the
desitination file without any selecting of files using the same theory I
gave you changed to stay in the destination file and copy from each source
file.