Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

code not work for copy specific columns from sheet to another

0

hello 

i no  know  what's wrong  about  this  code  it  should  copy  specific  columns  from sheet  to  another  but  nothing  happend  and  no error 

i  hope  somebody  help  me  to  solve  this problem 

Sub COPY_6_COLUMNS()
    Application.ScreenUpdating = False
    With Sheets("invent")
        For MY_COLS = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
            Select Case .Cells(1, MY_COLS).Value
            Case "A", "c", "d", "e"
                .Columns(MY_COLS).Copy
                    If IsEmpty(Sheets("output").Cells(1, Columns.Count).End(xlToLeft).Value) Then
                        Sheets("output").Cells(1, Columns.Count).End(xlToLeft).PasteSpecial (xlPasteAll)
                    Else
                        Sheets("output").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial (xlPasteAll)
                    End If
            End Select
        Next MY_COLS
    End With
    Application.ScreenUpdating = True
End Sub

Answer
Discuss

Discussion

Are you trying to copy columns A, C, D, and E or the columns that have those values in row 1? If you can explain exactly what you are trying to do, it will be easier to help.
don (rep: 1989) Oct 18, '20 at 3:54 pm
yes, don i 'm trying  copy columns A, C, D, and E    you  can  say   copied   dynamic data in specific  columns 
thanks
leopard (rep: 88) Oct 19, '20 at 4:46 am
You are branching into a follow-up question. In this thread we can deal with the Select statement. However, note that Select is much slower than If. Therefore, if you want to select or exclude only one column, If is the syntax to use.
Use Select to do different things with different things, such as copying 5 columns to 5 different sheets.
Variatus (rep: 4889) Oct 19, '20 at 8:13 pm
Add to Discussion

Answers

0
Selected Answer

 As Variatus points out, your Select Case statment is flawed.

Here's the revised code (and attached the revised file) which I think do what you want.

Also I note that you disable screen updating so guess you have much bigger sheets to process (than the small sample file where this isn't required). Haven't tried that but this should work.

CORRECTION to previous file ssv0_a.xlsm (based on discussion point):

My mistake, I missed that.


The revised file adds a Select Case which tests the variable My_Cols for the column to be skipped "Case <> 2" ...and will copy all columns apart from column B (where My_Cols is 2).

Hope that's what you want.

Sub COPY_6_COLUMNS()
    Application.ScreenUpdating = False

    With Sheets("invent")

        For MY_COLS = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column

            

            Select Case MY_COLS

            

                Case Is <> 2

                    .Columns(MY_COLS).Copy

                    

                    If IsEmpty(Sheets("output").Cells(1, Columns.Count).End(xlToLeft).Value) Then

                            Sheets("output").Cells(1, Columns.Count).End(xlToLeft).PasteSpecial (xlPasteAll)

                    Else

                            Sheets("output").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial (xlPasteAll)

                    End If

                Case Else

            

            End Select

            

        Next MY_COLS

        
With
    Application.ScreenUpdating = True


    End
End Sub
Discuss

Discussion

thanks  john   but  if  you  note   when  i  use  select  case  the  aim use  specific  columns  not  all   as  your  code    it suppose   not  copy   col b   
leopard (rep: 88) Oct 19, '20 at 2:30 pm
Sorry I missed that. Please see my revised answer/ file above. 
John_Ru (rep: 6092) Oct 19, '20 at 4:35 pm
thanks john  now  it  works  but  i'm asking  about  repeating copied data   so  if  is  possible  add  line  code    to  make   copy   data  without  repeat  old  data  just  update  old data(old columns)     and  only  add a new  columns  if  i  decide   add a new  columns f,g,h ..so  on 
leopard (rep: 88) Oct 20, '20 at 4:51 am
Leopard, please noite that the existing code will handle any additional columns (since the code detects the last column)- just add columns F and G (say) to the file I sent then run the macro- it will work as before but copy those additional columns too.

I think your words on "copy   data  without  repeat  old  data  just  update  old data(old columns)" represent a second question (for which I'm afraid I don't really understand your intention). Please add a separate question with extra explanation to get help BUT...

Please select my answer as correct (for your original question) following the Rules of the forum (as detailed in the blue Rules hyperlink to the top right of this webpage), specifically Asking Questions, Rule 6 as below:

Once you have answers to your question, click the "Select Answer" button next to the one that answers your question.

Thanks.
John_Ru (rep: 6092) Oct 20, '20 at 5:58 am
Add to Discussion
0

Try and read your code line by line.

  1. For My_Cols = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
     .Cells(1, Columns.Count).End(xlToLeft).Column should return the value of 5.
    Therefore the code will loop 5 times.
  2. Select Case .Cells(1, My_Cols).Value
    Look at the value of the cells A1, B1, C1, D1 and E1 in turn
  3. Case "A", "c", "d", "e"
    If the value is "A", "c", "d" or "e" Then execute the next line

The available values are "CODE", "BRAND", "TYPE", "ORIGIN", and "QUANTITY". Therefore the condition will never be met. Therefore the code that follows will never be executed. And therefore your code does loop 5 times but it never gets the chance to do anything.

Discuss

Discussion

thanks variatus  for  your  notices     i  thought my  code   copying dynamic data  for specific columns  , so   what  you suggest ?
leopard (rep: 88) Oct 19, '20 at 6:10 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login