Results 1 to 4 of 4

Thread: Columns being added when copying worksheets

  1. #1
    Seeker reglarh's Avatar
    Join Date
    Feb 2019
    Posts
    7
    Articles
    0
    Excel Version
    2010

    Columns being added when copying worksheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have three single sheet workbooks which I wish to copy into three worksheets in one workbook. After copying I change column headings, remove a few unwanted rows before importing the sheets into Access tables.
    My VBA is executed for a button in sheet 1 of the receiving workbook.
    I have three instances of the following, each using a different source workbook.

    ActiveWorkbook.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets(2).Range("A1")
    ActiveWorkbook.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets(3).Range("A1")
    ActiveWorkbook.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets(4).Range("A1")

    One worksheet contains the same number of columns as the source workbook, the other two have many more empty columns added, which then get imported into Access as unwanted fields.

    I don't understand why.

    Can anyone help?

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Are the sheets in ThisWorkbook 'clean' when you paste into them ?
    Are you pasting different number of columns to a particular sheet each time it's used ?

    Are the extra columns originating from the source files ?
    You can open the source file and from cell A1 hit Shift+Crtl+End and see if the selected area is just what you're wanting to copy.

  3. #3
    Seeker reglarh's Avatar
    Join Date
    Feb 2019
    Posts
    7
    Articles
    0
    Excel Version
    2010
    I used the usedrange parameter to do the copying, and the source workbooks had no extra columns. In on case just a few were added, in another about 40.

    not sure about clean, but the vba deletes all content first. And no, it’s the same number of columns each time, they originate from a thirparty application via an export function.

    i am in hospital tomorrow for a couple of days so won’t be responding for a few days.


    strange!

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    the source workbooks had no extra columns.
    then the issue is with the sheets being copied to.
    not sure about clean, but the vba deletes all content first.
    can you post the part of the code that does that ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •