Columns not copying over

[Tdeg88]

All,

The below code is suppose to copy columns from 3 sheets into a final sheet (sheet "All"). The first two columns are the same so I've just copy pasted them into the final sheet. The error flags up when it tries to run the following "ws.Range("C" & LC).Copy sh.Cells(1, CC)". I'm guessing the range I've selected if cant be selected and that's why the code brings up an error.

Thank you for the help in advance.

Sub CompileAll()
Dim LC As Long, CC As Long
Dim ws As Worksheet, sh As Worksheet

'LC - Last column
'CC - Last compile column

Sheets("All").Delete
Sheets.Add(After:=Sheets("sheet3")).Name = "All"

Sheets("sheet1").Range("A:B").Copy
' Paste Special
Sheets("All").Range("A:B").PasteSpecial xlValues
Sheets("All").Range("A:B").PasteSpecial xlFormats

Set sh = Worksheets("All")

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "All" Then
    
        CC = sh.Cells(3, sh.Columns.Count).End(xlToLeft).Column + 1
        LC = ws.Range("C" & ws.Columns.Count).End(xlToRight).Column
        
        
        If CC = 2 Then CC = 1
        ws.Range("C" & LC).Copy sh.Cells(1, CC)
    End If
Next ws
    
End Sub

 

[Joe4]

This two lines look very weird to me:

LC = ws.Range("C" & ws.Columns.Count).End(xlToRight).Column

and

ws.Range("C" & LC).Copy sh.Cells(1, CC)

So LC is returning a COLUMN number in that first line.
However, you are using it in the ROW place of the second line:

Range("C" & LC)

You are already specificying column "C", then using the LC variable as a row number.

Is that really what you are intending to do?

Also, what is the exact error message you get?

 

[Tdeg88]

Hi Joe4,

Thank you for replying.

Is that really what you are intending to do?

What i intended was I thought i was selecting the range in the first 3 worksheets from column C to the column value given by LC from the worksheet and copying over that section to the last worksheet (worksheet - All) and pasting it after the last column.

Also, what is the exact error message you get?

The error message is a run-time error 1004 - Select method of range class failed.

 

[Joe4]

I think it would be beneficial for us to see some sample data and your expected output, so we can see exactly what it is you are trying to do.
It is a little confusing/unclear from your code and explanation. I think a visual example should help clear things up.

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.

 

[Tdeg88]

Hi Joe,

So basically i have an input sheet that will input a new columns to sheets 1 to 3 i.e. new tasks and new people (that part of the code is working fine). below is a example of what sheet 1 to sheet 3 contain. The code that i posted above was suppose to copy columns C to the last column (the last column isn't constant) and to paste the data from sheet 1 to 3 in the All sheet (second table).

Book1
	A	B	C	D	E	F	G
1					Sheet 1
2
3	Lead		Ben	David	John	Ben	George
4	Task		Task 1	Task 2	Task 3	Task 4	Task 5
5	Date
6	01/03/2023	Week1	Research
7	08/03/2023	Week2			Field work
8	15/03/2023	Week3
9	22/03/2023	Week4
10	29/03/2023	Week5		work 2		Research
11	05/04/2023	Week6
12	12/04/2023	Week7
13	19/04/2023	Week8
14	26/04/2023	Week9	Work 1
15	03/05/2023	Week10
16	10/05/2023	Week11
17	17/05/2023	Week12					work 3
18	24/05/2023	Week13
19	31/05/2023	Week14
20	07/06/2023	Week15
21	14/06/2023	Week16
22	21/06/2023	Week17
23	28/06/2023	Week18
24	05/07/2023	Week19
25	12/07/2023	Week20
26	19/07/2023	Week21
27	26/07/2023	Week22
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1					Sheet 1						Sheet 2						Sheet 3
2
3	Lead		Ben	David	John	Ben	George		Ben	David	John	Ben	George		Ben	David	John	Ben	George
4	Task		Task 1	Task 2	Task 3	Task 4	Task 5		Task 1	Task 2	Task 3	Task 4	Task 5		Task 1	Task 2	Task 3	Task 4	Task 5
5	Date
6	01/03/2023	Week1	Research						Research						Research
7	08/03/2023	Week2			Field work						Field work						Field work
8	15/03/2023	Week3
9	22/03/2023	Week4
10	29/03/2023	Week5		work 2		Research				work 2		Research				work 2		Research
11	05/04/2023	Week6
12	12/04/2023	Week7
13	19/04/2023	Week8
14	26/04/2023	Week9	Work 1						Work 1						Work 1
15	03/05/2023	Week10
16	10/05/2023	Week11
17	17/05/2023	Week12					work 3						work 3						work 3
18	24/05/2023	Week13
19	31/05/2023	Week14
20	07/06/2023	Week15
21	14/06/2023	Week16
22	21/06/2023	Week17
23	28/06/2023	Week18
24	05/07/2023	Week19
25	12/07/2023	Week20
26	19/07/2023	Week21
27	26/07/2023	Week22
All

 

[Joe4]

OK, you had a few logic errors in there. Probably the biggest thing was your column calculations. Note that ".Column" will always return the index number of the column, not the column letter (i.e. column "E" would return 5). So you cannot combine the result of that with a column letter to get a column range.

When working with columns like this, instead of using "Range", I like to use "Cells".
The format of Cells is: Cells(row, column)
The advantage to this is in your column reference, you can use the column letter (enclosed in double-quotes), or the column index number.

So the following three range references are equivalent and all refer to cell E8:
Range("E8")
Cells(8, "E")
Cells(8, 5)

You also have a problem with your first copy, in that you are trying to use row 3 to find the last column with data, but the issue is that after you have copied over just columns A and B, there is nothing in cell B3, so it thinks column A is the last column with data instead of column B.

I think I have fixed up your code. Give this a try:

Sub CompileAll()

Dim LC As Long, CC As Long, LR As Long
Dim ws As Worksheet, sh As Worksheet

'LC - Last column
'CC - Last compile column
'LR - Last row of data to copy

Sheets("All").Delete
Sheets.Add(After:=Sheets("sheet3")).Name = "All"

Sheets("sheet1").Range("A:B").Copy
' Paste Special
Sheets("All").Range("A:B").PasteSpecial xlValues
Sheets("All").Range("A:B").PasteSpecial xlFormats

Set sh = Worksheets("All")

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "All" Then
   
        CC = sh.Cells(3, sh.Columns.Count).End(xlToLeft).Column + 1
        LC = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column
        LR = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
              
        If CC <= 2 Then CC = 3
        ws.Range(ws.Cells(1, 3), ws.Cells(LR, LC)).Copy sh.Cells(1, CC)
    End If
Next ws
   
End Sub