Workbooks copy

[GrayCoder]

Requesting assistance on this.

This formula works:
Workbooks(Mybook).Activate
Workbooks(Mybook).Worksheets(Worksheetname).Range("A1:AZ" & ICount).Copy _
'Workbooks("005.xlsx").Worksheets(1).Range("A1")

However it ceases when changing the "005.xlsx" to the statement: "CellA" & ".xlsx" - and CellA equals 005

Workbooks(Mybook).Activate
Workbooks(Mybook).Worksheets(Worksheetname).Range("A1:AZ" & ICount).Copy _
Workbooks("CellA" & ".xlsx").Worksheets(1).Range ("A1")

Thanks for the assistance.

 

[dreid1011]

What is CellA? Is it a variable, a named range? Chances are, it should not be in quotes.

 

[GrayCoder]

It's a variable. It has this coding earlier in the process:

Dim CellA As String
Do While ActiveCell.Value <> ""
CellA = Range("A3").Value

In which A3 contains a company #.

Removing the quotes gave this message: Object doesn't support this property or method.

The program is copying data in one workbook to another... and at this point in the code it saves the second workbook:

Workbooks(Mybook).Activate
Workbooks(Mybook).Worksheets(Worksheetname).Range("A1:AZ" & ICount).Copy _
Workbooks("CellA" & ".xlsx").Worksheets(1).Range ("A1")

But CellA is from the first workbook. Is there another code that needs to be added? Thanks..

 

[Joe4]

However it ceases when changing the "005.xlsx" to the statement: "CellA" & ".xlsx" - and CellA equals 005

If CellA is indeed a variable, you do NOT want double-quotes around it!
Everything enclosed in double-quotes is treated as literal text.
So the workbook name you are building is literally "CellA.xlsx".

Variables always need to be outside of double-quotes, i.e.

Workbooks(CellA & ".xlsx").Worksheets(1).Range("A1")

 

[GrayCoder]

Thanks... it was the quotes. Had to remove them from a couple of lines and it now works.

 

[Joe4]

Thanks... it was the quotes. Had to remove them from a couple of lines and it now works.

You are welcome.

The key points to remember are:
- Everything enclosed in double-quotes is treated as literal text
- All variables would need to be outside of double-quotes to be treated as a variable and not literal text