Copying specific columns from worksheet to worksheet, error 1004

[ufka1]

I'm a novice VBA user and copied this from another user. This code works when there is only one in the worksheet I am copying to but I added a 2nd tab and the code doesn't work. The debug is highlighting the ws.Cells(1, 1).select line at the end.


Sub copycolumnsfromreport()

Dim head_count As Integer
Dim row_count As Integer
Dim col_count As Integer
Dim i As Integer
Dim j As Integer
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Sheets("Sheet1")

'count headers in this worksheet
head_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))

'open other workbook and count rows and columns
Workbooks.Open Filename:="C:\Users\desktop\Report.xlsm"
ActiveWorkbook.Sheets(1).Activate

row_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
col_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))

For i = 1 To head_count

j = i

Do While j <= col_count

If ws.Cells(1, i) = ActiveSheet.Cells(1, j).Text Then

ActiveSheet.Range(Cells(1, j), Cells(row_count, j)).Copy
ws.Cells(1, i).PasteSpecial xlPasteValues
Application.CutCopyMode = False
j = col_count

End If

j = j + 1

Loop

Next i

ActiveWorkbook.Close Savechanges:=False

ws.Cells(1, 1).select

Application.ScreenUpdating = True

End Sub

 

[DanteAmor]

Before this line:

ws.Cells(1, 1).select

Put this line:

ws.select

 

[ufka1]

Thank you DanteAmor. The error does not appear anymore but the data does not copy over either. Any suggestions?

 

[ufka1]

DanteAmor, your solution worked. What was happening was that the VBA run function works properly but when I assigned the macro to a button, it does not work. Any reason why this may be?

 

[DanteAmor]

The error does not appear anymore

That's good news!

but the data does not copy over either.

That is bad news.

But... the problem of copying data is a problem that is before reaching the ws.Cells(1, 1).select line, that means that you had the problem before.

What we need is to review the entire macro to make the correction or corrections, but for that, I need you to explain what you want, you put a sample of your data from both books and, very important, the expected result.

Without that information it is very difficult to help you.

Note XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[ufka1]

Hi Dante, I was able to figure out my problem, thank you again.