score:0
are you performing any special operation post loading the data into an array? if not then i'd suggest using simple copy and paste routine through vba which should work reliably.
public sub copypastedata()
dim wksh as worksheet, wksh2 as worksheet
set wksh = thisworkbook.sheets(1)
set wksh2 = thisworkbook.sheets(2)
wksh.range("a2:" & lastrow).copy wksh2.range("a2")
end sub
score:2
first note that "a2:" & lastrow
is no valid address as the column letter for the second part is missing. it needs to be something like "a2:a" & lastrow
first option
one option is to loop through the array and test each element if it begins with =
and replace it with '=
(the apostroph is not shown but ensures that it is handled as text and not as formula). note that this will kill all formulas in that range.
dim arrc as variant
arrc = wksh.range("a2:a" & lastrow).value
dim irow as long
for irow = 1 to ubound(arrc, 1)
dim icol as long
for icol = 1 to ubound(arrc, 2)
if left$(arrc(irow, icol), 1) = "=" then
arrc(irow, icol) = "'" & arrc(irow, icol)
end if
next icol
next irow
wksh2.range("a2").resize(ubound(arrc), 1).value = arrc
second option
the second option is to set the number format of the destination to text: .numberformat = "@"
then paste the values and turn it back to general.
dim arrc as variant
arrc = wksh.range("a2:a" & lastrow).value
with wksh2.range("a2").resize(ubound(arrc), 1)
.numberformat = "@"
.value = arrc
.numberformat = "general"
end with
note that .numberformat = "@"
will turn also numbers into text so you need to turn it back to general
to ensure that if there were numbers they are turned back to numbers again and you can calculate with them.
this workaround might have some odd effects on dates or other number formats. so this might not be a reliable solution depending on what data you have.
third option
last option is .copy
and .pastespecial
wksh.range("a2:a" & lastrow).copy
wksh2.range("a2").pastespecial xlpastevaluesandnumberformats
'or without number formats
wksh2.range("a2").pastespecial xlpastevalues
Source: stackoverflow.com
Related Query
- Copy Paste Cell beginning with equal sign (=====)
- Copy rows based on cell value and paste on a new sheet with same cell value name
- VBA cant copy and paste cell with single value
- With Copy Paste over Data Validation cell , its not working
- Copy and paste row in new sheet with changes in cell value (month) based on other cell value
- Find unknown name and surname in opened Word document, copy it and paste into the cell A12 in excel .activesheet with excel VBA
- Copy and Paste in next empty row beginning with B59
- Vba Worksheet_Change event does not trigger when copy and paste data into column but works with a manual click into cell
- copy and paste cell with a certain clause
- Copy Cell With Line Breaks - Paste Values Into Separate Cells
- copy and paste to the next cell using a loop with the datas in different columns
- Macro to copy and paste rows with correct first cell
- Copy paste entire row with blank intermediate cell
- Comparing last column of first row in sheet2 with F2 cell of sheet1 if it matches then show msgbox or else copy F2 range paste to sheet2
- How to copy a row (variable) and paste it into a specific cell (also row as variable) with VBA in Excel
- Copy paste only part of rows, with one cell containing a picture, on another sheet when condition is met
- How to copy the entire row of the active cell and paste it underneath with the formulas and data validation in shared workbook
- If cell = value then copy and paste cell below with addition
- Excel VBA How to match WorkSheet name with Cell value, then Copy and Paste a Range of Cells
- How can I copy & paste entire rows with distinct values to a new sheet on varying cell ranges?
- Search for a value in a column which is equal to cell value in another sheet, then copy paste to new location
- How can I copy Excel cells with rich text formatting but not the global cell format?
- Copy and paste an excel chart with a VBA macro in word
- How can I copy a row of data, and paste it with an offset
- What does mean the colon with equal sign ":="
- Protected worksheet allows editing cell format by copy and paste
- Copy a cell and paste into first blank row in a range of cells
- Copy cell background color and paste it to corresponding cell of another sheet
- Excel VBA - Copy from a range of cells and paste in one cell
- Microsoft Excel Copy and Paste with Format of Date
More Query from same tag
- Allowing Wildcards in Access Query
- Script to Insert a Checkbox into every cell and assign it to that cell in Excel and returns true false in a specific selected cell
- delete chart picture without deleting chart
- How to add a column in between an array in vba
- VBA copy non-blank cells to a master workbook
- Excel VBA Comparison with Two Attributes
- Find the number of days passed since January 1 of a particular year in vba
- speed up array formula
- Send CDO email using Outlook through excel VBA code
- Run Time Error '13': Type mismatch
- Multiple textbox value to one cell
- Importing Excel File to Access Form
- Validate IBAN in Excel
- Run time error when clicking cancel on Input box asking for Date
- Replacing value in different worksheet
- How to simulate a Group Header in an Access Continuous Form?
- Issue with RunSavedImportExport
- Using RegEx and Replace to update address fields with USPS abbreviations in MS-Access
- How to obtain last-known occurrence of closest match to a value after passing the maximum value?
- Set Cell Value on Clicking Other Cells
- VBA Loop until cell not blank
- Excel VBA is Finding Every Other Cell not Every Cell From Method
- VBA Selenium: How to click on href link to download excel file
- Pushing data from one workbook to another
- How to link many excel files to one master file, so that if I update the single master file, all linked files also update?
- Copy multiple times of the same data according to the date
- Macro on auto fill down
- How do I properly use if/then for a bolean value in order to make an object visible?
- Access VBA - Editing table data isn't actioned immediately
- Sorting operation using VBA?