VBA - Master Copy File not all columns will copy

[DJosh27]

I'm trying to create a macro that will take a daily pre-designed format sheet (people just input the numbers in to rows, and columns) and insert them into a combined yearly master. It will copy all the rows just fine it seems at the moment, but it stops at column CD and I need it to go to CQ. Thoughts? Also I'm not sure how to make it where it won't copy the same info each time. I'd like to make it where if it sees new data it just inputs it. I'm lost.

Sub TestingMacro()


Dim FolderPath As String, Filepath As String, Filename As String


FolderPath = "C:\Users\examplej\Desktop\Testing Macro"


Filepath = FolderPath & "*.xlsx*"


Filename = Dir(Filepath)


Dim lastrow As Long, lastcolumn As Long


Do While Filename <> ""
Workbooks.Open (FolderPath & Filename)


lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(4, 1), Cells(lastrow, lastcolumn)).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 1), Cells(erow, lastcolumn))
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 1))
Filename = Dir


Loop
Application.DisplayAlerts = True
End Sub

 

[tonyyy]

DJosh27,

Welcome to the Board.

If you want to hard code Column CQ as the lastcolumn...

lastcolumn = Columns("CQ").Column

If you want the lastcolumn to adjust to the real last column...

lastcolumn = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

Also I'm not sure how to make it where it won't copy the same info each time. I'd like to make it where if it sees new data it just inputs it.

One approach would be to use an IF statement in a loop to test for new data, for example...

Dim i as Long
For i = 4 To lastrow
    If Cells(i, 1).Value >= "9/24/2017" Then
        Range(Cells(i, 4), Cells(lastrow, lastcolumn)).Copy
        Exit For
    End If
Next i

Another approach would be to simply clear the master sheet before copying the data...

Rows("2:" & Cells(Rows.Count, "A").End(xlUp).Row).Clear

Cheers,

tonyyy