#1. The code you posted doesn't copy formats. It just assigns values. These
two lines do the work:

destRange.Offset(k,destColA).Value = format(entryA,"00000000")
destRange.Offset(k,destColB).Value = entryB

#2. When you type 0000003 into a cell that's formatted as General, excel sees
what you're typing and knows it's just 3. Same thing when you do the "typing"
in code.

You could change the numberformat of that cell:

with destRange.Offset(k,destColA)
.numberformat = "00000000"
.Value = entryA
end with

Or you could force the cell to be text:

with destRange.Offset(k,destColA)
.numberformat = "@"
.Value = format(entryA,"00000000")
end with

or more simply:
destRange.Offset(k,destColA).Value = "'" & format(entryA,"00000000")

#3. Maybe you can exclude the rows you don't want to copy by applying
data|Filter|autofilter and hiding those rows that should be ignored. The
copy|paste the visible cells????



rockhammer wrote:

Hello, I'm trying to copy data from one file to another. The data is copied
almost "as is" except the columns are essentially rearranged. I'm doing it
using the following code (as opposed to copying & pasting whole columns one
at a time) because I want to be able to skip some rows that don't actually
contain data. I have 3 questions based on the code excerpt below ("..." =
code not shown). Please keep in mind the source file was opened using a macro
from the destination file and the source file is presently active:

...
for j = 1 to sourceLastCell.Row ' the last row in the source file
...
entryA = ActiveWorkbook.Worksheets(sourceTab).Cells(j,sourc eColA)
' A is a number formatted as "00000000", i.e. leading zeros if not 8
digits long
entryB = ActiveWorkbook.Worksheets(sourceTab).Cells(j,sourc eColB)
' B is alphanumeric
...
' the reason I pull the values into entryA & entryB first is that I need to
' determine if I need to skip copying the row of data
' destRange points to a single cell in the destination file
...
destRange.Offset(k,destColA).Value = format(entryA,"00000000")
destRange.Offset(k,destColB).Value = entryB
...
k = k + 1 ' row index in the destination file
next j
...

This code works insofar as the data I want copied are all copied to the
right cells. However my 3 questions a

1) All the data copied are effectively "copied with formats" but I want
effectively to "copy & paste values". Is there a way to modify this code so
the cell formats are not copied to the destination?

2) the Format() code does not do what I expect it to do. In other words, a
number with fewer than 8 digits are copied without the leading zeros I'm
trying to force onto it. Is there a way to make sure the leading zeros are
there?

3) Compared copying whole columns one at a time, this method because it goes
to the files for each individual cell takes considerably longer time to
execute. I am in debug mode doing just ~6,000 rows at one time with ~13
columns/cells to copy for each row. My code is completing the ~6,000 rows in
1min compared to the straight whole column copy/paste which takes <5sec (all
times rough estimates). I need to scale this eventually to ~15,000 rows. Is
there a smarter/faster way to do this?

Thanks a lot.

Rockhammer

--

Dave Peterson