0

I wrote a script to copy sheet by sheet from ThisWorkbook to another workbook. Everything seems to work fine except for the column width of the last sheet, and only the last sheet. Some columns have wrapped text which is not found in the original. I eliminated most of the code and only copied formats and column widths. Still, the column widths are different from that in the original file. And this only happens with the last sheet.

Sub copy_all()

Dim rng As Range
Dim i As Integer
Dim destBook As Workbook

Set destBook = Workbooks("Book3")

 With destBook
    For i = 1 To ThisWorkbook.Sheets.Count

         Set rng = ThisWorkbook.Sheets(i).UsedRange

         rng.Copy
        .Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteFormats
        .Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths

   Next i

 End With

End Sub

I even wrote a script to list the column widths of the original and copied files so it'd easy to compare. Indeed, only the last page of the column widths are different. All the other 13 pairs of pages of the original and copied files have the identical column widths.

As you can see from the following picture, only some of the column width on the last page are different from the original, not every one of them. Some are identical to the original. Only those with a width of 87 are copied to becoming a width of 48.

My question is what is wrong with the copy script? Thanks in advance for any help.

enter image description here

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
joehua
  • 725
  • 3
  • 10
  • 25
  • ".UsedRange" why? – Rafał B. Jun 27 '20 at 11:34
  • As Rafal B implies, better to copy your range from A1 to last used cell. – SJR Jun 27 '20 at 12:13
  • Because every sheet has different range. If you look at the picture, one sheet has only two columns while the sheet before it has 17 columns. Any easier way of copying used range without using usedrange? If I use Range("A1:xx"), then how do I determine the xx programmatically? I guess it can be done but not as easy as usedrange. Would not using usedrange make a difference on column width? I still don't know why the code doesn't work only on the last sheet. – joehua Jun 27 '20 at 13:51
  • https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920 – SJR Jun 27 '20 at 13:54
  • @SJR Thanks. I'll try other methods. Still, usedrange works on the first 13 sheets though. – joehua Jun 27 '20 at 13:58
  • Yes no guarantee that that is the cause of your problem. – SJR Jun 27 '20 at 14:30
  • Assumed that you want to have the exact condition of the active workbook sheets(i) on workbook Book 3, then maybe try to copy the sheet, something like this :`ThisWorkbook.Sheets(i).Copy Before:=Workbooks("Book3.xlsm").Sheets(1)`. See if the column width is correct or not. Then clear the contents, if you just want to have only the formatting. – karma Jun 28 '20 at 16:41
  • @karma Thanks for the suggestion. Still, it didn't work. There is something special about the last sheet. When I did nothing but just manually copying the whole sheet and pasting the column width to a new sheet, I got the same result - some column widths were pasted, some didn't. This only happened to the last sheet, not any other sheet. – joehua Jun 30 '20 at 05:34
  • @joehua, you wrote : _manually copying the whole sheet_ ... I think that is a different process on what I mean. What I mean is if we do manually : 1. right click the sheet name (2) choose : "Move or Copy..." (3) a small window show up, in the "to book" dropdown list, choose "Book3.xlsm" (assuming that your Book3.xlsm is already open). So, this code `ThisWorkbook.Sheets(i).Copy Before:=Workbooks("Book3.xlsm").Sheets(1)` is the process on what I mean. – karma Jun 30 '20 at 08:25
  • @karma I have tried your suggestion. As I said, that didn't work. I'm convinced there is something special about the last sheet. I changed the column width of a column which had had problem copying and tried my code again. It worked this time while it had not worked before. And those columns whose width I didn't change still did not work. What is so special about the last sheet? I don't know but it is special. – joehua Jun 30 '20 at 10:14
  • @joehua, hm... that's quite strange that copying that "problematic" sheet to a new sheet manually still change the column on the copied sheet. Yes I think there's something special in this "problematic" sheet. BTW, have you tried to copy the workbook, and in the copied one, clear all the values in the "problematic" sheet (including remove all macros in this "problematic" sheet module if any), then manually copy the sheet to a new sheet? I'm curious if in the copied sheet the column width is change or not :). – karma Jul 01 '20 at 09:59
  • @karma I copied the workbook, opened the copy, delete the module, closed it, reopened it, copied the last sheet to a newly created blank book, pasted by values, then pasted column widths. The column width did not get pasted to the new book. I added a second sheet to the new book, copied the second last sheet from the old book and pasted by values then pasted column widths. The column widths got pasted. So, you see, there is something special about the last sheet. Its column width cannot be pasted. – joehua Jul 01 '20 at 14:48
  • @joehua, LOL, I scratch my head, curious what is the "special" thing in that "problematic" sheet :). Btw, I'm sorry as I don't quite understand this : _"copied the last sheet to a newly created blank book, **pasted** by values, then **pasted** column widths"_. The word "paste" which made me don't understand, because if you copy a sheet, then there's no need any paste. (continue) – karma Jul 01 '20 at 18:39
  • For example Sheet1 has a value from cell A1 to Z100 with many kind cells formatting (font color, fill color, date format, etc). So if you copy this Sheet1, then the **automatically** newly created sheet should have the exact condition just like in Sheet1. So that's why I don't quite understand as in your explanation you said that you did the paste process ? – karma Jul 01 '20 at 18:41
  • @karma Sorry for the imprecise choosing of words. It should have read "copied the last sheet to a newly create blank book by first pasting values then pasting column widths. – joehua Jul 02 '20 at 10:26
  • @joehua, very sorry... I still don't quite understand on this sentence : _"pasting values then pasting column widths"_ ---> because this sentence made me think that you do something like this : there are two workbooks open. One where the "problematic" sheet (say the name of the workbook is wb1) is, and the other one is a newly created workbook (say the name is wb2). In wb1 you copy the "problematic" sheet (say, by ctrl+a then ctrl+c), then you activate wb2, then you paste it for example to "fresh blank" sheet1 (say, by ctrl+v). Am I correct that you do something like that ? – karma Jul 02 '20 at 15:54
  • @karma yes, I copied the problematic sheet and pasted to the newly created workbook by first pasting values then column widths but not using Ctrl-V, which would paste everything. – joehua Jul 02 '20 at 23:34
  • @joehua, ok. How about if you try to copy the "problematic" sheet ? Is the newly created sheet column also not correct ? Just FYI, between (A) copying_all_the_cells_in_a sheet (say, ctrl+A then ctrl+C) then paste the copied_cells to another blank sheet and (B) copying_the_sheet, is different, Harin. In process-A, the then is : _"then paste the copied_cells"_, while in process-B the then is : _"then paste the copied_sheet"_. This is not to solve the problem, but just to check whether the copied_sheet also has incorrect column. – karma Jul 03 '20 at 07:15
  • @karma I'm not trying to overcome the column width problem. There are not too many columns in the problematic sheet. I could just manually adjust the column width of the problematic columns if I wish. I'm trying to understand/find out the root of this problem. – joehua Jul 03 '20 at 16:36
  • Ok then, joehua :). Thanks for the explanation. BTW, have you tried with "cells" instead of "usedrange" in your code ? So, from `Set rng = ThisWorkbook.Sheets(i).UsedRange` change to `Set rng = ThisWorkbook.Sheets(i).cells` – karma Jul 05 '20 at 09:16
  • @karma Excel reported "out of memory". I think the range is too big to copy. – joehua Jul 05 '20 at 12:46

0 Answers0