Member Avatar for Papa_Don

Group,

I've got a protected spreadsheet at work that prevents me from creating links or writing macro's to do some repetitive copying and pasting from one spreadsheet to another. I hope someone can help.

I'd like to use VB (if possible) to copy from a specific range in Spreadsheet1.Sheet1 (let's say D5 to J15). I then want to go to Spreadsheet2.Sheet1 and "Paste Value" starting at cell D2. Is this actually possible? If so, can someone direct me to where I might find the commands/codes that would allow me to do this? Is this done very much like writing a macro?

In advance, thanks for the help. I certainly appreciate any articles or video's that might help me get started with this project.

Don Wilson

  • 3 Contributors
  • 2 Replies
  • 401 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by jared.geli
Member Avatar for Oxiegen

Have a look at Gembox Spreadsheet.
You can do pretty much anything with it concerning excel/xml.

Member Avatar for jared.geli

Try this. It let's you copy the values from a ceratin cell up to the last cell with value. The copied value will be in clipboard.

xlWorkBook = xlApp.Workbooks.Open(location of your spreadsheet)

xlWorkSheet = xlWorkBook.Worksheets(1) '1 indicates the first worksheet, 2 indicates second, and so forth

Dim last As Excel.Range = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing)



xlWorkSheet.Range("B2", last).Copy()

xlWorkBook.Close()

I use this on my projects but haven't tried it on an excel spreadsheet that restrict copying values. I hope it works on your end

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.