VBA Columns.Count weird number

Question

Hello iv wrote macro in VBA, to copy some data from 1 workbook to another, i need to put this data in end of my "table" in first workbook, and there i have problem. Iv used Columns.Count but this count to much Columns i think. Excel Workload count 5 Rows and 95~~ Columns. if needed i can update for some one both Workloads. Thank There is my code

Sub CopyDataBase()
Dim wb As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Dim strFileToOpen As Variant
Dim LastColumn As Range
Dim LastCellColumnNumber As Long

Set wb = ActiveWorkbook
Set ws = Worksheets("CPC1+CPC2 RNI")

   Set LastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft)
    LastCellColumnNumber = LastColumn.Column + 1


strFileToOpen = Application.GetOpenFilename _
(Title:="Please select an Excel file to open", _
FileFilter:="Excel Files *.xlsm (*.xlsm),")

Workbooks.Open Filename:=strFileToOpen
Set wb2 = ActiveWorkbook
wb2.Worksheets("RNI").Range("S2:S4").Select

Selection.Copy

wb.Activate

wb.Worksheets("CPC1+CPC2 RNI").Cells(2 & LastCellColumnNumber).Select
Selection.PasteSpecial Paste:=xlPasteValues


wb2.Close

Set wb = ActiveWorkbook


End Sub

I cannot understand your question - please try to be more specific... are you receiving an error? — dwirony, Jun 15 '18 at 18:09
I **think** the OP is saying that the column count of 95 is much higher than expected, and that most of those columns are not actively being used? — Mistella, Jun 15 '18 at 18:28
I believe the issue lies in `Set LastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft)` But I am not familiar enough with this methodology to provide an alternative. I've done similar with finding last row and learned that my methods were not the best way to go about things. — Lux Claridge, Jun 15 '18 at 18:50
Please review [How to ask a question](https://stackoverflow.com/help/how-to-ask), and [Not using "Select"](https://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select), and see my answer below. — GMalc, Jun 15 '18 at 19:17

score 0 · Answer 1 · answered Jun 15 '18 at 19:09

0

Change;

wb.Worksheets("CPC1+CPC2 RNI").Cells(2 & LastCellColumnNumber).Select

to

wb.Worksheets("CPC1+CPC2 RNI").Cells(2, LastCellColumnNumber).Select

answered Jun 15 '18 at 19:09

GMalc

2,608
1
9
16

score 0 · Answer 2 · answered Jun 16 '18 at 10:36

Thank @GMalc it Work! i change my code to

  wb.Worksheets("CPC1+CPC2 RNI").Cells(2, 
  LastCellColumnNumber).Select

but i need also to change line from

Set LastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft)

to

Set LastColumn = ws.Cells(2, ws.Columns.Count).End(xlToLeft)

Thank :)

And sorry if i ask wrong Im newbie on this forum!

VBA Columns.Count weird number

2 Answers2