1

Been learning vba for a while now and have managed to find and/or work out solutions to most hiccups I get into.

However one project I'm working on at the moment raised a question. Essentially a chunk of data is copied from one sheet (before the sheet with the macro is even opened), the sheet I am working on is opened and the macro is run, currently what the macro does is paste the copied data and then re arrange if in the desired form. This is fine apart from the fact that the sheet is very busy and pasting the entire hunk of data has the chance to overwrite existing data in the sheet.

Essentially I'm wondering if there is a way to save the data that has been copied to an array variable? I just don't know the syntax. To paste it you I just use a simple selection.Pastespecial line, but when setting a variable you can't use that can you?

Dim pasteAr() as Variable
    pasreAr = .....??? 'currently copied data in other workbook

Once I've worked that out it should be simple to just paste the desired information into the sheet by referencing this array right?

Thank for the help (in advance :P)

Community
  • 1
  • 1
matt
  • 15
  • 1
  • 4

1 Answers1

0

Copy/Paste is generally not an effective way to do things, you can use a variant array to store the information. If you are going to store it to use between macros you will want to declare it as Public/Private.

Public MyArray() As Variant

Sub StoreArray()
     'Store the data you want to keep:
     MyArray = Sheets("Sheet1").Range("A1:B5").Value
End Sub

The array wont work the same as paste however so doing:

Sheets("Sheet2").Range("A1").Value = MyArray

Will only fill in A1 with the value in A1, see this page for more detailed information.

D_Zab
  • 710
  • 1
  • 5
  • 14
  • I'm familiar with the use of arrays and how to assign values using them (Range("A1").Value=myArrray(1,5), etc.) What I need to know is, is there a way to assign whatever is on the clipboard to an array variable? – matt Jan 09 '15 at 02:51
  • That would be more difficult, you would have to store the data in an array first, write all the data from that array into the clipboard as individual strings with identifiers and then pull the data out of the clipboard by the identifiers. Chip Pearson has a custom module for that [here](http://www.cpearson.com/excel/Clipboard.aspx) – D_Zab Jan 09 '15 at 14:07