I need to copy data from one sheet to another in a macro
the destination sheet is formatted with borders and the like
and the data is coming from another sheet that gets created
but when I copy cell date from one page to another sheet
my cell formats get screwed up

Not sure what the best way to do this is without having to re-format
each cell after the paste.

The cvs file I read in varies in length but is always 6 columns wide
some cells contain string data and some numeric values

The number of rows always varies.

any suggestions?

Regards,

Jeff W.

Hi Jeff -

How about something like these 3 statements (replace range addresses and
sheet names to suit):

Worksheets("Sheet1").Range("A1").CurrentRegion.Cop y
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

---
Jay


"Jeff W." wrote:

I need to copy data from one sheet to another in a macro
the destination sheet is formatted with borders and the like
and the data is coming from another sheet that gets created
but when I copy cell date from one page to another sheet
my cell formats get screwed up

Not sure what the best way to do this is without having to re-format
each cell after the paste.

The cvs file I read in varies in length but is always 6 columns wide
some cells contain string data and some numeric values

The number of rows always varies.

any suggestions?

Regards,

Jeff W.

The cell I am coming from is a single cell and the one's I'm going to
some are merged and this errors out, with a message about merged cells
and the debugger comes up


<Jeff

"Jay" wrote in message
...
Hi Jeff -

How about something like these 3 statements (replace range addresses and
sheet names to suit):

Worksheets("Sheet1").Range("A1").CurrentRegion.Cop y
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

---
Jay


"Jeff W." wrote:

I need to copy data from one sheet to another in a macro
the destination sheet is formatted with borders and the like
and the data is coming from another sheet that gets created
but when I copy cell date from one page to another sheet
my cell formats get screwed up

Not sure what the best way to do this is without having to re-format
each cell after the paste.

The cvs file I read in varies in length but is always 6 columns wide
some cells contain string data and some numeric values

The number of rows always varies.

any suggestions?

Regards,

Jeff W.

Hi Jeff -

In the case of merged cells at the destination, we'll need to decide how you
want the source cells to "go into" the merged destination cells. In other
words, if you have a 6 column by 5 row source (from the .csv) and you're
copying those 30 cells into a 23 cell destination, we'll need more specific
information about which source cells to disregard prior to pasting.

For example, row 2 of a 6 column by 5 row source contains 6 cells. If row 2
of the destination has cells A2-C2 merged, then there are only 4 destination
cells; 6 source cells won't fit. The operation cannot occur unless we
programmatically turn the 6 cells into 4 prior to the paste. We need more
specific rules from you on how to handle this case.

---
Jay

"Jeff W." wrote:

The cell I am coming from is a single cell and the one's I'm going to
some are merged and this errors out, with a message about merged cells
and the debugger comes up


<Jeff

"Jay" wrote in message
...
Hi Jeff -

How about something like these 3 statements (replace range addresses and
sheet names to suit):

Worksheets("Sheet1").Range("A1").CurrentRegion.Cop y
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

---
Jay


"Jeff W." wrote:

I need to copy data from one sheet to another in a macro
the destination sheet is formatted with borders and the like
and the data is coming from another sheet that gets created
but when I copy cell date from one page to another sheet
my cell formats get screwed up

Not sure what the best way to do this is without having to re-format
each cell after the paste.

The cvs file I read in varies in length but is always 6 columns wide
some cells contain string data and some numeric values

The number of rows always varies.

any suggestions?

Regards,

Jeff W.

Jay, I'm copying 6 columns wide and on my temporary sheet
its all a single cell entry from the cvs file

This is what I need to happen, this would copy data from sheet1
to sheet2 transferring the columns 6 wide

Sheet1 A1 copy to Sheet2 A11 single cell destination
Sheet1 B1 copy to Sheet2 B11 single cell destination
Sheet1 C1 copy to Sheet2 C11 "three merged cell destination
Sheet1 D1 copy to Sheet2 F11 single cell destination
Sheet1 E1 copy to Sheet2 G11 "two merged cell" destination
Sheet1 F1 copy to Sheet2 I11 "four merged cell" destination

and go to the next row and as long as it has data in it, loop and do
the same thing until we are out of data in the 1st cell of the next row

It sounds like what you are telling me is that my temporary sheet, needs
to be formatted the same as my destination sheet, before I can copy the
data across from one to the other is this correct?

I have tried this and when I read in the cvs file, it doesnt follow the cell
layout, it sort of does what it wants


<Jeff


"Jay" wrote in message
...
Hi Jeff -

In the case of merged cells at the destination, we'll need to decide how
you
want the source cells to "go into" the merged destination cells. In other
words, if you have a 6 column by 5 row source (from the .csv) and you're
copying those 30 cells into a 23 cell destination, we'll need more
specific
information about which source cells to disregard prior to pasting.

For example, row 2 of a 6 column by 5 row source contains 6 cells. If row
2
of the destination has cells A2-C2 merged, then there are only 4
destination
cells; 6 source cells won't fit. The operation cannot occur unless we
programmatically turn the 6 cells into 4 prior to the paste. We need more
specific rules from you on how to handle this case.

---
Jay

"Jeff W." wrote:

The cell I am coming from is a single cell and the one's I'm going to
some are merged and this errors out, with a message about merged cells
and the debugger comes up


<Jeff

"Jay" wrote in message
...
Hi Jeff -

How about something like these 3 statements (replace range addresses
and
sheet names to suit):

Worksheets("Sheet1").Range("A1").CurrentRegion.Cop y
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

---
Jay


"Jeff W." wrote:

I need to copy data from one sheet to another in a macro
the destination sheet is formatted with borders and the like
and the data is coming from another sheet that gets created
but when I copy cell date from one page to another sheet
my cell formats get screwed up

Not sure what the best way to do this is without having to re-format
each cell after the paste.

The cvs file I read in varies in length but is always 6 columns wide
some cells contain string data and some numeric values

The number of rows always varies.

any suggestions?

Regards,

Jeff W.

Hi Jeff - The procedure below uses the original pastespecial method if the
destination is not merged and if it is merged, the procedure loops assigning
values cell-by-cell until it runs out of data as you suggested.

Sub JeffW_02()
For Each col In ActiveSheet.UsedRange.Columns
dC = Choose(col.Column, 1, 2, 3, 6, 7, 9) 'dC = destination Column
(number)
col.Copy
If dC < 3 And dC < 7 And dC < 9 Then 'if destination isn't merged,
paste
Worksheets("Sheet2").Columns(dC).PasteSpecial Paste:=xlPasteValues
Else 'if destination contains merged cells, assign values cell-by-cell
Set cel = col.Cells(1, 1)
Do Until cel.Value = ""
Worksheets("Sheet2").Cells(cel.Row, dC).Value = cel.Value
Set cel = cel.Offset(1, 0)
Loop
End If
Next 'col
End Sub
---
Jay


"Jeff W." wrote:

Jay, I'm copying 6 columns wide and on my temporary sheet
its all a single cell entry from the cvs file

This is what I need to happen, this would copy data from sheet1
to sheet2 transferring the columns 6 wide

Sheet1 A1 copy to Sheet2 A11 single cell destination
Sheet1 B1 copy to Sheet2 B11 single cell destination
Sheet1 C1 copy to Sheet2 C11 "three merged cell destination
Sheet1 D1 copy to Sheet2 F11 single cell destination
Sheet1 E1 copy to Sheet2 G11 "two merged cell" destination
Sheet1 F1 copy to Sheet2 I11 "four merged cell" destination

and go to the next row and as long as it has data in it, loop and do
the same thing until we are out of data in the 1st cell of the next row

It sounds like what you are telling me is that my temporary sheet, needs
to be formatted the same as my destination sheet, before I can copy the
data across from one to the other is this correct?

I have tried this and when I read in the cvs file, it doesnt follow the cell
layout, it sort of does what it wants


<Jeff


"Jay" wrote in message
...
Hi Jeff -

In the case of merged cells at the destination, we'll need to decide how
you
want the source cells to "go into" the merged destination cells. In other
words, if you have a 6 column by 5 row source (from the .csv) and you're
copying those 30 cells into a 23 cell destination, we'll need more
specific
information about which source cells to disregard prior to pasting.

For example, row 2 of a 6 column by 5 row source contains 6 cells. If row
2
of the destination has cells A2-C2 merged, then there are only 4
destination
cells; 6 source cells won't fit. The operation cannot occur unless we
programmatically turn the 6 cells into 4 prior to the paste. We need more
specific rules from you on how to handle this case.

---
Jay

"Jeff W." wrote:

The cell I am coming from is a single cell and the one's I'm going to
some are merged and this errors out, with a message about merged cells
and the debugger comes up


<Jeff

"Jay" wrote in message
...
Hi Jeff -

How about something like these 3 statements (replace range addresses
and
sheet names to suit):

Worksheets("Sheet1").Range("A1").CurrentRegion.Cop y
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

---
Jay


"Jeff W." wrote:

I need to copy data from one sheet to another in a macro
the destination sheet is formatted with borders and the like
and the data is coming from another sheet that gets created
but when I copy cell date from one page to another sheet
my cell formats get screwed up

Not sure what the best way to do this is without having to re-format
each cell after the paste.

The cvs file I read in varies in length but is always 6 columns wide
some cells contain string data and some numeric values

The number of rows always varies.

any suggestions?

Regards,

Jeff W.