Append Excel Data from a named Range to an Exceldatabase

[Janetzky]

I wish to add a code to a workbook in order to append information of a
named range "data" to an external Excelfile, the path to file is in a
cell of the same workbook named "location". The sheet in the external
file is called "database". The data of the named range (7 columns x
7rows) is structured exactly as in the "database".The twist is it
needs to append the data below existing data in that file. I figure
there need to be some sort of a loop which counts the last row and
data needs to be copied beneath that data in the closed file.
Maybe some one can help me out there. Thanks in advance, Sven!

 

[Excel_Expert]

I wish to add a code to a workbook in order to append information of a
named range "data" to an external Excelfile, the path to file is in a
cell of the same workbook named "location". The sheet in the external
file is called "database". The data of the named range (7 columns x
7rows) is structured exactly as in the "database".The twist is it
needs to append the data below existing data in that file. I figure
there need to be some sort of a loop which counts the last row and
data needs to be copied beneath that data in the closed file.
Maybe some one can help me out there. Thanks in advance, Sven!

Sub CopyDataRange()
Dim rngCopy as Range
Dim wbDest as Workbook

set rngCopy = Range("Data")
set wbDest = Workbooks.Open(Range("location")

'Assuming Data on Database Sheet starts from Column A
wbDest.Sheets("Database").Range("A65536").offset(1,0).PasteSpecial
xlPasteAll
Application.CutCopyMode = False
wbDest.Save

msgbox "Completed"
End Sub

 

[Janetzky]

Thanks for the post.

The code is not working even after modification. The missing part,
however, is the appending of the data in the "database" file after the
last line of existing data.How can i count the rows of data? After
that i assume that i will use the second coordinate of the offset
formula to trigger the row for the paste.
Please help again.

Sven

 

[Excel_Expert]

Hi

Can you tell me what line doesn't seem to be working? Is there an
error you face?

Oh... I now see the error. I think I didn't write this line of code
rngCopy.Copy

Just re-copy the code and try running it.

Sub CopyDataRange()
Dim rngCopy as Range
Dim wbDest as Workbook

set rngCopy = Range("Data")
set wbDest = Workbooks.Open(Range("location")


rngCopy.Copy

'Assuming Data on Database Sheet starts from Column A
wbDest.Sheets("Database").Range("A65536").offset(1,0).PasteSpecial
xlPasteAll
Application.CutCopyMode = False
wbDest.Save


msgbox "Completed"
End Sub