Copying multiple columns

[hedonman]

Hello Mr.Excel Board!

This is my first post and apologize if i am missing any formalities. I am using Office Excel 2010. Below is code for what I am trying to do. I am so close but stuck.

I am trying to extract specific columns from a Workbook with many Worksheets into another NEW workbook on One single worksheet.

Sub SearchBurnerList()

    Dim fileName As String: fileName = "C:\Users\...\Burner List.xls"
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim lastRow
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Set wb = Workbooks.Open(fileName)

    For Each ws In wb.Worksheets
        ws.Range("A1", "B2").Copy _
            ThisWorkbook.Sheets("SearchBook").Range("A" & Rows.Count).End(xlUp).Offset(1)
        'ws.Range(Columns(1), Columns(2)).Select
       ' Selection.Copy ThisWorkbook.Sheets("SearchBook").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next ws

    'wb.Close False

End Sub

My problem is at the moment is at the ws.Range().Copy... i don't know what to fill in there to get the columns. Because i am using the COPY method, does that change the parameters?

Thank you Very much

 

[Joe4]

Welcome to the Board!

If you are just trying to select the first two columns, this should worK:

    ws.Columns("A:B").Select

 

[hedonman]

thanks for the reply joe4.

Acutally what i am trying to do is; Get the columns from A,B,C, H, J. But only after the 2nd row. I should of put A2 etc etc.

Would the convention work?:


ws.Columns("A2:B2", "H2", "J2"). Copy _</pre>

 

[Joe4]

Wouldn't it just be easier to copy the whole column and then delete the first row after the copy? Then you don't need to worry about finding the ending row.

 

[hedonman]

ws.Columns("A:B").Select

I just tried taking the whole columns. But i get an error:
Application defined or object defined error. Run-Time Error 1004


My Goal is to Open A workbook and take every worksheet and put that into my current workbook's single worksheet.

Does it look like this code does that?

Man im so stumped

 

[RSXchin]

try this

ws.Range("A2:B,H2:J"). Copy

 

[hedonman]

try this

ws.Range("A2:B,H2:J"). Copy

Just tried it, I still get the same 1004 error. VBA auto corrected the space after the Copy. i added the "_" after the copy for the paste location. Ran it and debug'd the crash at this line.

how exactly does Range work? I heard you can do this?

ws.Range(Columns(A),Columns(B)).Copy _ ???

Additionally how can you print a message to the screen? I did a counter in my for loop. i want to count how many worksheets are being cycled through.

 

[Joe4]

I still get the same 1004 error

Sounds like it is not recognizing "ws" as a valid range name.
Currently, how is "ws" declared and value set?

 

[hedonman]

I declared it at the top after Sub.

Dim ws As Worksheet

Also if i do this:
Dim count
        count = 1
Print count

does that work?

 

[Joe4]

What exactly are you trying to do with the code you just posted, and how does it relate to your original question?

It would probably be helpful if you posted you entire current code that you are getting the error on. If I get some time later today, I may see if I can recreate your situation.