Copy daily changing rows from a workbook paste to another workbook

[LearningVBA9]

Hello,

I’m trying to copy columns A:C down to row 52. ( Attached image source1 and 2)

However the amount of rows I want to copy can change day to day.

I want to create a macro that will copy in the data but since the amount of rows can change day to day I’m having issues.


I’ve thought about maybe copying based off background color, which is RGB (255,255,204).

I think that may work best but I’ve had issues with the code ( I’m not great at VBA).


My current code is copying the ENTIRE columns of A:C. Again probably due to user error. I was trying to create a pop-up box where I can then tell excel the exact rows I want to copy but when I tried it I copied all the data ( all rows in column A:C), including the data in green which I don’t want.


I then want to do the same thing for columns F:G which will match the same amount of daily rows as A:C.
Once I know how to get what I need from A:C I know I can mimic that for F:G

Any advice would be amazing.

Thanks



Current Failing Code:

Sub Copy Data()

Dim sourceworkbook As Workbook

Dim currentworkbook As Workbook

Set currentworkbook = ThisWorkbook



Dim dteReportDate As Date

Dim strYear As String

Dim strMonth As String

Dim strday As String

Dim strMonthName As String

Dim strpath As String

Dim strFileName As String




Dim StartCell As Range

Dim FullRange As Range

Dim SheetName As String

Dim CopyTO() As String

Dim ErrorMsg



'The 6 rows below are telling excel where the file path is and how to read in the date format correctly... yyyy-mm-dd

dteReportDate = Worksheets("North Summary").Range("S1")

strYear = Right(Year(dteReportDate), 4)

strMonth = Format(Month(dteReportDate), "-0#")

strday = Format(Day(dteReportDate), "-0#")

strMonthName = Format((dteReportDate), "mmm")

strpath = My File Path"



'This tells excel the filename

strFileName = ("info-" & strYear & strMonth & strday & ".xlsx")



' This tells excel to open the Excel file named above

Set sourceworkbook = Application.Workbooks.Open(strpath & strFileName)



' This tells excel what cells to copy and where to paste them

'sourceworkbook.Worksheets("NORTH SUMMARY").Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row).Copy



On Error GoTo ErrorHandler


ErrorMsg = "Invalid selection"

Set StartCell = Application.InputBox(prompt:="Select first cell in first row of range to be copied.", _

Type:=8, Title:="Copy From Range")

StartCell.Select



ErrorMsg = "Error in selecting full range to copy."

Set FullRange = Range(StartCell, Range(Chr(StartCell.Column + 64) & "65536").End(xlUp))



ThisWorkbook.Worksheets("North Summary").Activate

Set StartCell = Application.InputBox(prompt:="Select first cell in first row to copy to.", _

Type:=8, Title:="Copy To Range")



FullRange.Copy Destination:=StartCell



Exit Sub

ErrorHandler:


MsgBox ErrorMs



ThisWorkbook.Worksheets("North Summary").Activate

currentworkbook.Worksheets("North Summary").Range("A3:C60").PasteSpecial Paste:=xlPasteValues

 

[OaklandJim]

Without knowing more this seems doable. Unfortunately, FOR ME what you need/want is not clear. Your description of what is needed is just too vague and not detailed enough for someone to help you.

Plus, ideally you provide a link to your workbook. Otherwise someone willing to assist might need to recreate it to develop and test the necessary code. Put the file on Box, Dropbox, 1Drive, Google Drive or Sharepoint. Provide a link to the workbook using the link icon at the top of the message box.

And the pictures are just not very helpful.

Consider using Mr Excel's excellent add-in called XL2BB which enables you to post a portion of a worksheet. With that add-in you can show relevant data in a usable form. That way someone willing to help you does not have to create fake data or guess what your data looks like. See XL2BB - Excel Range to BBCode for details.