Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice copying from one sheet to another
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice copying from one sheet to another
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice copying from one sheet to another
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice copying from one sheet to another
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice copying from one sheet to another
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice copying from one sheet to another
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice copying from one sheet to another
I cut and pasted this into my workbook, but it errors out
for some reason, it cant get the single cell data into the merged cells on sheet1 the data starts at cell A1 and in sheet2 "destination" it begins at cell A11, I don't know if this is the problem or not. but when it does stop, I can see the first Colum on sheet1 has been selected but the whole page of the destination sheet is also selected. I know this is possible, I have another workbook from a few years back that is doing this same thing, except I didn't write the code, I just pieced it all together and fooled with it till I got it to work... I remember, that we copied the data from sheet1 to sheet2 on the lower portion of the page below all my other stuff then transferred the data up above then deleted the lower portion of the page, but exactly what was done, or why, I'm not clear... I'll try anything... <Jeff "Jay" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice copying from one sheet to another
Hi Jeff -
The code I sent should paste starting at cell A1 on the destination sheet, but it sounds like you want it to start in cell A11. Although a small adjustment to start pasting into cell A11 can be made, it won't avoid the error you describe. We'll need to clearly redefine the problem before we can get to the solution. If you could email me a test workbook, it would help us to focus accurately on the issue. Also, if you think it will help, send the code you pieced together, too. Email to jc.sresearch@~h~o~t~m~a~i~l.~c~o~m (remove the "~" characters). ---- Jay "Jeff W." wrote: I cut and pasted this into my workbook, but it errors out for some reason, it cant get the single cell data into the merged cells on sheet1 the data starts at cell A1 and in sheet2 "destination" it begins at cell A11, I don't know if this is the problem or not. but when it does stop, I can see the first Colum on sheet1 has been selected but the whole page of the destination sheet is also selected. I know this is possible, I have another workbook from a few years back that is doing this same thing, except I didn't write the code, I just pieced it all together and fooled with it till I got it to work... I remember, that we copied the data from sheet1 to sheet2 on the lower portion of the page below all my other stuff then transferred the data up above then deleted the lower portion of the page, but exactly what was done, or why, I'm not clear... I'll try anything... <Jeff "Jay" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice copying from one sheet to another
To Readers -
We solved Jeff's problem offline, but I'm posting solutions here for readers. Although the readers won't have the benefit of Jeff's probelmatic workbook, the thread should clearly describe the common problem of trying to paste into merged cells. Two solutions follow. The first pastes columns if the destinations are predetermined to be single columns, but programmatically assigns values to columns that contain merged cells (columns 3, 7, and 9 in Jeff's example). The second solution uses programmatic assignments for all of the data. Readers should note that either solution involves programmatically assigning values into the left-most cell of a group of merged cells. For example, if columns 3, 4, and 5 of row 2 are merged, you can assign a value to cell(2,3) just as if it were not merged and the value will be visible in the merged cells. Programmatically assigning a value to any of the other cells in the merged group does not result in an error, but the value is not visible and all cells in the group remain empty. Solution I: Sub JeffW_02() Application.ScreenUpdating = False 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("Tool_List").Cells(11, 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("Tool_List").Cells(cel.Row + 10, dC).Value = cel.Value Set cel = cel.Offset(1, 0) Loop End If Next 'col 'Wrap procedure, register cursor at A1 of Tool_List Application.CutCopyMode = False Worksheets("Tool_List").Activate Range("A1").Select End Sub Solution II: Sub copy_su_modified() 'Logic flow similar to Jeff's original version (cell by cell copying), but 'uses programmatic setting of cell values instead of copying. 'In the destination worksheet, the following columns are merged: '3,4,and 5 '7 and 8 '9, 10, 11, and 12 Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Tool_List") Application.ScreenUpdating = False iterations = ws1.Range("A1").CurrentRegion.Rows.Count 'COPY THE TOOL NUMBER For i = 1 To iterations ws2.Cells(i + 10, 1) = ws1.Cells(i, 1) Next i 'COPY THE TOOL diameter For i = 1 To iterations ws2.Cells(i + 10, 2) = ws1.Cells(i, 2) Next i 'COPY THE TOOL description For i = 1 To iterations ws2.Cells(i + 10, 3) = ws1.Cells(i, 3) Next i '-------------------------------------------------------- 'COPY THE flute length For i = 1 To iterations ws2.Cells(i + 10, 6) = ws1.Cells(i, 4) Next i 'COPY THE LFH For i = 1 To iterations ws2.Cells(i + 10, 7) = ws1.Cells(i, 5) Next i 'COPY THE notes For i = 1 To iterations ws2.Cells(i + 10, 9) = ws1.Cells(i, 6) Next i 'Wrap procedure, register cursor at A1 Sheets("Sheet1").Select Range("A1").Select Sheets("Tool_List").Select Range("A1").Select End Sub --- Jay "Jeff W." wrote: I cut and pasted this into my workbook, but it errors out for some reason, it cant get the single cell data into the merged cells on sheet1 the data starts at cell A1 and in sheet2 "destination" it begins at cell A11, I don't know if this is the problem or not. but when it does stop, I can see the first Colum on sheet1 has been selected but the whole page of the destination sheet is also selected. I know this is possible, I have another workbook from a few years back that is doing this same thing, except I didn't write the code, I just pieced it all together and fooled with it till I got it to work... I remember, that we copied the data from sheet1 to sheet2 on the lower portion of the page below all my other stuff then transferred the data up above then deleted the lower portion of the page, but exactly what was done, or why, I'm not clear... I'll try anything... <Jeff "Jay" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 sheet links advice | Excel Discussion (Misc queries) | |||
Need help Taking alot data from one sheet (if not blank) and copying toa list on another sheet. | Excel Worksheet Functions | |||
Multiple copying of a Spreadsheet advice please | Excel Discussion (Misc queries) | |||
Copying cells from on sheet to another sheet (via sheet module) | Excel Programming | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions |