Select all the cells of a sheet via VBA

[Sandie1712]

Hello,

i am very new to VBA coding and trying to select all the cells in below mentioned code."cells.select"

But it's giving error"Run time error 1004" could you pl suggest the solution.

Thanks!!

Sub copyfromallopenworkbookstooneworkbook()
Dim wb As Workbook, ws As Worksheet, j As Long
j = 1


For Each wb In Workbooks
  wb.Activate
          'MsgBox wb.Name


For Each ws In Worksheets
 ws.Select
           'MsgBox ws.Name
Cells.Select
  Selection.Copy
  ThisWorkbook.Activate
  Sheets(j).Select
  Range("A1").Select
  ActiveCell.PasteSpecial xlPasteValues
  ws.Activate
  j = j + 1
  wb.Activate
  
  Next
  
  Next
  
 ThisWorkbook.Activate


End Sub

 

[Joe4]

Re: how to select all the cells of a sheet via VBA?

Do you have any hidden, merged cells, or protected cells?

 

[RoryA]

I'd guess it's the location of the code that is the problem, but you don't need to select or activate:

Sub copyfromallopenworkbookstooneworkbook()
Dim wb As Workbook, ws As Worksheet, j As Long
j = 1


For Each wb In Workbooks
          'MsgBox wb.Name


For Each ws In wb.Worksheets
           'MsgBox ws.Name
ws.Cells.Copy
  ThisWorkbook.Sheets(j).Range("A1").PasteSpecial xlPasteValues
  j = j + 1

  
  Next
  
  Next
  
 ThisWorkbook.Activate


End Sub

 

[Sandie1712]

Hello,

Joe 4 Nothing is hidden here!!

Rory A

Your suggested code for coping worked well, but it again showed the same error 1004 at Range("A1")

** All of yours suggestions and codes are more than welcome, whereas i am keen to know the issue with my coding for better understanding.


Thanks!!

 

[RoryA]

What is the exact error message please?

 

[Sandie1712]

Run time error 1004
application defined or object defined error

 

[Sandie1712]

hope all is well!!

 

[Joe4]

You only answered one part of the three part question I asked.

Also, which module did you place your VBA code in?

 

[Sandie1712]

Hello,

pl help me with the right coding for concatenate of below cited, hope with that my issue will be resolved.

For i =1 to 10

cells("j"&i).value>Cells(i).value


Here "j" is column no.

 

[Joe4]

The format of Cells is Cell(row,column).
So, it would be Cells(i,"j")

However, Cells(i) is not valid, as it is missing the row reference.