copy columns to multiple sheets with different column references

    I have an input sheet, and 13 report sheets.
    The input sheet is a must for internal data.
    I am trying to write vba code for a button to copy four columns on input sheet.
    The problem is that there will be a different "paste to" column reference for each sheet.
    I have all of the column references on the input sheet.
    I cannot figure out how to loop copy the columns (maintaining widths) while using the changing column references.
    I found basic loop code and paste column code, but need help to assign variables. I am very weak using the Cells reference.
    I appreciate any advice.

    I just need a starting point...

    Re: copy columns to multiple sheets with different column references


    Why not post the code you have so we can see where you've got to? If you want to attach a sample file, that always helps.

    Re: copy columns to multiple sheets with different column references


    This should start you off, but will probably need adjusting for actual file. You need to have a table containing sheet names (and nothing else) and column letters.

    Re: copy columns to multiple sheets with different column references


    When it errors, hover over the yellow line and the current values of the various elements of that line should appear so hopefully error will become apparent. Otherwise attach a file with revised layout.

    Re: copy columns to multiple sheets with different column references


    just found that out, fixed references and it is working but not copying range I need.


    I have a cell where I have user fill in start column and end column... for example, PA column to start and PW column to end.
    In another cell, hidden in white, my formula is =cell&1... resulting in a text cell reference.
    I don't know how to call for the cell reference represented by this text..
    here is what I found and tried without success - VBA works but copies the cell reference and some surrounding cells only (kind of funny to see)
    I used this line:


    Set rData = .Range(([indirect("e28")]), .Range([indirect("e29")]))


    e28 contains the characters PA3,
    e29 contains the characters PW18


    Thanks for looking at this!
    Ken

    Re: copy columns to multiple sheets with different column references


    Forgot to mention... the column reference the user chooses will change every day.
    for this example I want the copy range to become the same as: Set rData = .Range("PA3", .Range("PW18"))

    Re: copy columns to multiple sheets with different column references


    But it's erroring on the rSheets line so the problem appears to lie with that rather than rData.


    Anyway with VBA you can just use

    Code
    Set rData = .Range(.Range("E28").value, .Range(e29").value)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login