copy entire column instead of rows

**shido** · 09-12-2015, 05:55 PM

Hi Alan,

Thank for the follow up and your time.

I have been playing with the original code myself. I have decided to make Name list same in all sheet, just to get to the solution. I know my variable range causes alot of the problem and took alot of your time.

However, i have come to the following code and its working fine. only deviation i did is to make name list common.

Private Sub Worksheet_Change(ByVal Target As Range)

'http://www.ozgrid.com/forum/showthread.php?t=154637

    Dim strStatus As String

    Dim wsAwait As Worksheet

    Dim wsRmove As Worksheet

    Dim wsMList As Worksheet

    Dim lstrw As Long

    

    If Target.Column = 1 Then Exit Sub

    If Target.Row <> 1 Then Exit Sub

    If Target.Cells.Count > 1 Then Exit Sub

   

    Application.ScreenUpdating = False

    

      

    strStatus = Target.Value

    Set wsAwait = ThisWorkbook.Sheets("Awaiting")

    Set wsRmove = ThisWorkbook.Sheets("Remove")

    Set wsMList = ThisWorkbook.Sheets("Master List")

    lstrw = wsAwait.Rows.Count

    

    If strStatus <> "Move to Master List" Then

        Range(Target, Target.Offset(0, 2)).Copy

        Application.EnableEvents = False

        Select Case strStatus

            

        Case "Awaiting"

            'wsAwait.Range("A" & lstrw).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

            Columns(Target.Column).Copy wsAwait.Columns(wsAwait.Cells(2, Columns.Count).End(xlToLeft).Column + 1)

            

        Case "Remove"

            wsRmove.Range("A" & lstrw).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

            

        End Select

Thanks once again Alan for the direction and effort

**Doc.AElstein** · 09-13-2015, 05:24 PM

Hi

Originally Posted by shido

...... I have decided to make Name list same in all sheet, just to get to the solution. I know my variable range causes a lot of the problem .........
However, i have come to the following code and its working fine. only deviation i did is to make name list common........

. Hi thanks for coming back – it was a difficult one so good to know how it came out, Thanks.
. So basically you have “tidied up” or “restructured” the data to do away with all thet difficult chopping and changes of rows between Sheets with Ranges of different sites, along with some reordering of rows as you go along. – “Wow it is almost a mouthful just to say!!!”
. I guess it is always a bit of a “chicken and Egg” situation , not knowing which is the less of the evils, - making the data better structured with all the work that entails, or developing a more complicated code, which as well as the time taken can , as you see take a while to debug / check that it works finally.
. Happy Yous got there and that Yous may of least got some direction from the thread, so thanks for the feedback

Alan
.
P.s.
. I think now the codes are at or very close to working to meet your initial complicated criteria.
. So can I ask if you have the time to reconsider renaming the Thread Title. This could help navigate someone searching for a solution to such a complicated requirement should they for any reason have to take that route.
.
. I would suggest a title sof the form
“....Based On selection: Copying , Adjusting, Reordering Rows and Columns from Tables, then Pasting into different sized Tables in specific order...”... ( If you can fit that in!! )
FYO
. to do that :
. Go to your first Post , Post #1
. Hit Reply
Attachment 418734

. Scroll down and Hit Go Advanced
Attachment 418735

. Scroll up and you see you can Edit the Title
Attachment 418736

IMPORTANT : There is a Forum Bug. Your original Post can “vanish”. So before you start the above copy the entire contents of your first Post to the clipboard, so that if the worst occurs you can paste that post back in. ( Your text would probably be enough as that initial code you gave was not too relevant to the complicated criteria )

. Hope that is all clear. Thanks in advance if you can do that. But its your Thread - so your decision.
. Good luck with your project
Alan

copy entire column instead of rows

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Thread Information

Users Browsing this Thread

Similar Threads

How to Match a column with another sheet's column and copy paste the Entire Row&Column aft

Alter existing formula to copy specific cells in row instead of copy entire column

Search string in a column then copy entire rows to another sheet

Need a macro to filter a column in one tab and copy the entire rows of data into another

[SOLVED] Copy Entire Column Only Returning 1st 16 Rows?

[SOLVED] Macro to Copy Entire Rows if Column Equals

copy entire rows to a new sheet if certain criteria are met in one column

Bookmarks

Bookmarks

Posting Permissions