Code only copies certain column.

[SteveABC]

Hello,

Can anyone tell me why the code below only copies columns upto column BR. I have data in column BS & BT but this gets excluded when the data pastes into my new file.

I was under the impression this code would select the range where columns are populated.



lc = .Cells(1, Columns.Count).End(xlToLeft).Column



Sub Pmtemplate()

Dim w As Worksheet, b As Workbook, ol As Object, msg As Object
Dim mypath As String, myfile As String, scc As String, sto As String

mypath = "W:\.Team Documents\Freehold Team\E&J Estates\Reporting\Reports\"

With Sheets("Control")
sto = Join(WorksheetFunction.Transpose(Range("Mail_to")), ";")
scc = Join(WorksheetFunction.Transpose(Range("Mail_cc")), ";")
End With

Set w = Sheets("E&J Master")
Set b = Workbooks.Add

With w
lr = .Cells(Rows.Count, 1).End(xlUp).Row
lc = .Cells(1, Columns.Count).End(xlToLeft).Column
.Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
End With

b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value2
b.Sheets(1).Name = "E&J PM Master"

ActiveWindow.Zoom = 75

myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"

'Application.DisplayAlerts = False
'For sh = b.Sheets.Count To 2 Step -1
'b.Sheets(sh).Delete
'Next
'Application.DisplayAlerts = True

b.SaveAs myfile

End Sub

[SamT]

Is there anything in cells "BS1" and "BT1"?

Try this
With w
.Cells(1).CurrentRegion.Copy b.Sheets(1).Range("a1")
End With

Why remove Date and Currency Formats?
b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value2

To Paste only values and not Formulas
w.Range("A1").CurentRegion.Copy
b.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
b.Range("A1").PasteSpecial Paste:=xlPasteFormats

[SteveABC]

Hello,

Yes there is data in BS1 & BT1. but my code doesn't pick up the data in these columns. It stops in BR1


[QUOTE=SamT;408122]Is there anything in cells "BS1" and "BT1"?



Don't believe this code removes the date or currency formats. When using the original code the formats are copied correctly as I require. I'm simply ensuring the columns and rows are resized correctly

This is isn't the issue anyway. Just want to know why my code stops copying at column BR1.



Why remove Date and Currency Formats?

b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value2


This code breaks with object failure error. Do we need to declare the object?


To Paste only values and not Formulas[CODE]w.Range("A1").CurentRegion.Copy

[SamT]

This code breaks with object failure error. Do we need to declare the object?
w needs to be declared.


Just want to know why my code stops copying at column BR1.Hard to tell without seeing your workbook, but we'll try

What is the result of
Sub Test()
MsgBox "Last Column = " & Sheets("E&J Master").Cells(1, Columns.Count).End(xlToLeft).Address
End Sub

While we're at it, might as well
Sub Test2()
Dim CR as Range
Set CR = Sheets("E&J Master").Range("A1").CurrentRegion
MsgBox "LastColumn2 = " & CR.Cells(1, Columns.Count).Address
End Sub

Finally
Sub Test3()
MsgBox "Last Value = " & Sheets("E&J Master").Range("BT1").Value
End Sub

[SteveABC]

Hello.

I've included a test book that mirror's my original.



Hard to tell without seeing your workbook, but we'll try


Haven't tried the other code, thought sending the test book would be better served.


Thanks for helping btw.

[SamT]

BS1 and BT1 do not have any content. ".End()" will not work on them They are merged with BR1. Avoid Merged Cells. VBA has a hard time with merged Cells. I suggest Hardcoding BT1 into your code.

You use a lot of Merged cells, We suggest you use Horizontal Align "Center Across Selection."

[SteveABC]

Ok the below comments put me on the right path, I've now figured out what the problem was and is now fixed.

Thank you for helping me.



BS1 and BT1 do not have any content. ".End()" will not work on them They are merged with BR1. Avoid Merged Cells. VBA has a hard time with merged Cells. I suggest Hardcoding BT1 into your code.

You use a lot of Merged cells, We suggest you use Horizontal Align "Center Across Selection."


Let me know if you wish me to credit you in anyway on the forum.