Copy and Paste cell value in for loop

I am trying to copy and paste individual cell data if it does not equal "?" and is not blank into the first empty cell in a specific column. The purpose of this is to extract the two pieces of data I want to work with, in a more organized sheet. I will be adding to this code as well to make it compare the invoice number, which is the data coming from column C into column A, to another spreadsheet with total invoice cost. that cost will be divided by the hours worked, which is the data being copied from column G into column B. Whole thing is saved into a new workbook.

Anyways, the issue I am having is copying the data from the current cell in the for statement and pasting it into the new worksheet. this is the code I have... I have attached the excel files as well. The timecodes file is what i am currently working on, the quickbooks file is the next step in the process for me.

Function SheetExists(sheetName As String)
   Dim sheet As Worksheet
      For Each sheet In Sheets
         If sheet.Name = sheetName Then
            SheetExists = True
            Exit Function
         Else
            SheetExists = False
         End If
       Next
   End Function
   
   
Function SaveWorkbook(workbookName As String)
   Dim filePath As String
    Application.DisplayAlerts = False
   filePath = "C:\Users\chrish\Desktop\Job Costing\dump\output.xlsx"
   Sheets(workbookName).Copy
   ActiveWorkbook.SaveAs Filename:=filePath
   ActiveWorkbook.Close
   Application.DisplayAlerts = True

End Function

Sub CreateWorkbooks()
   Dim newSheet As Worksheet, timecodeSheet As Worksheet
   Dim cell As Object
   Dim regionRange As String
   Dim lastrow As Long

   Set timecodeSheet = Sheets("Sheet1")
   Application.ScreenUpdating = False


   regionRange = "C4:" & timecodeSheet.Range("C4").End(xlDown).Address
   regionRange2 = "G4:" & timecodeSheet.Range("G4").End(xlDown).Address
   Sheets.Add After:=Sheets(Sheets.Count)
   Set newSheet = ActiveSheet
   
   For Each cell In timecodeSheet.Range(regionRange)
      If cell.Value <> "?" And cell.Value <> "" Then
            lastrow = newSheet.Range("A" & Rows.Count).End(xlUp).Row
            cell.Value.Copy newSheet.Range(lastrow)
        End If
    Next cell
    
    For Each cell In timecodeSheet.Range(regionRange2)
      If cell.Value <> "?" And cell.Value <> "" Then
            lastrow2 = newSheet.Range("G" & Rows.Count).End(xlUp).Row
            cell.Value.Copy newSheet.Range(lastrow2)
        End If
    Next cell
    
    newSheet.Name = cell.Value
    SaveWorkbook (cell.Value)
    Application.DisplayAlerts = False
    newSheet.Delete
    Application.DisplayAlerts = True
            
   MsgBox "All workbooks have been created successfully"

   Application.ScreenUpdating = True

End Sub

Lastrow and lastrow2 contain the row of the last used row, may I suggest you insert the to-be-copy row into lastrow+1 and lastrow2+1?

I think because lastrow1 and lastrow2 are redefined every time the loop runs, it should always have an empty row, however I could be wrong, as I can't test that until I get the cell.Value.Copy working, I'm not sure if this is something that works as when I run it, i get a "Run-time error '1004': "Method 'Range' of object'_Worksheet' failed and this highlights this line of code

cell.Value.Copy newSheet.Range(lastrow)

don't you need a column too and not only a row?

If it might help, why don't you record the steps and read how excel executed the action...

I just need it to be pasting into the first empty cell in that column. I recorded a macro and it used this method

Range("C4").Select
    Selection.Copy

I'm not sure how to tell it in my code to select whatever cell it is currently on in the loop...

or simply

newsheet.cells(3,lastrow+1)=cell.value

and

newsheet.cells(6,lastrow2+1)=cell.value

the 3 is for the C column and the 6 for the G column

cell.Value.Copy
newsheet.range("C"&lastrow+1).select
selection.paste

in the other case

cell.Value.Copy
newsheet.range("G"&lastrow2+1).select
selection.paste

The variable workbookName was never defined so it will be ""
You are trying to copy a sheet called "" which is throwing an error.
Also, I think to copy the Sheet you would have to go Sheets(SheetName).Cells.copy

Try Sheets(SheetName).Copy first but if that doesnt work, you will need the ".Cells"

Hmm, I keep finding new ways to break the script... This might be easier... This is how it was originally. This just copies the entire column instead of cell by cell. The Issue I have with this is that it copies blank cells and cells with "?"'s in them. My goal is to have it copy the invoice number from the cell in column C and past it into a new sheet to be saved as a new workbook, and where that invoice number exists I want it to copy the cell with the total hours in column G of the same row... I know a lot about this code is unnecessary, I am VERY new to VBA and trying to frankenstien this code together from existing scripts and my basic knowledge....

Function SheetExists(sheetName As String)
   Dim sheet As Worksheet
      For Each sheet In Sheets
         If sheet.Name = sheetName Then
            SheetExists = True
            Exit Function
         Else
            SheetExists = False
         End If
       Next
   End Function
   
   
Function SaveWorkbook(workbookName As String)
   Dim filePath As String
    Application.DisplayAlerts = False
   filePath = "C:\Users\chrish\Desktop\Job Costing\dump\output.xlsx"
   Sheets(workbookName).Copy
   ActiveWorkbook.SaveAs Filename:=filePath
   ActiveWorkbook.Close
   Application.DisplayAlerts = True

End Function

Sub CreateWorkbooks()
   Dim newSheet As Worksheet, timecodeSheet As Worksheet
   Dim cell As Object
   Dim regionRange As String

   Set timecodeSheet = Sheets("Sheet1")
   Application.ScreenUpdating = False

   regionRange = "C4:" & timecodeSheet.Range("C4").End(xlDown).Address

   For Each cell In timecodeSheet.Range(regionRange)
      If SheetExists(cell.Value) = False Then
         Sheets.Add After:=Sheets(Sheets.Count)
         Set newSheet = ActiveSheet
         timecodeSheet.Range("C4").EntireColumn.Copy newSheet.Range("A1")
         timecodeSheet.Range("G4").EntireColumn.Copy newSheet.Range("B1")
         newSheet.Name = cell.Value
         SaveWorkbook (cell.Value)
         Application.DisplayAlerts = False
         newSheet.Delete
         Application.DisplayAlerts = True
      End If
    Next cell

   MsgBox "All workbooks have been created successfully"

   Application.ScreenUpdating = True

End Sub

I attached the file as well.

Try this. Its a very different approach.
I think its better because there are more invoices than blank cells

Sub CreateWorkbooksV2()

Dim timecodeSheet As Worksheet
Set timecodeSheet = Sheets("Sheet1")

Dim newSheet As Worksheet
Sheets.Add After:=Sheets(Sheets.Count)
Set newSheet = ActiveSheet

timecodeSheet.Range("C:C").Copy Destination:=newSheet.Cells(1, 1)  ' Done twice because of merged cells
timecodeSheet.Range("G:G").Copy Destination:=newSheet.Cells(1, 2)

Dim Bot As Integer  ' May have to change this if the last row gets too big
Bot = newSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = Bot To 4 Step -1    ' Start from the last row and go up
    If Cells(i, 1) = "?" Or Cells(i, 1) = vbNullString Then Rows(i).Delete Shift:=xlUp  ' If the first cell of row is ? or empty, delete the row
Next

End Sub

That works great, it puts in a few pieces of unnecessary data, but it's much cleaner! I really appreciate your help, and if it's too much to ask, I totally understand, but the next portion of this I'm trying to figure out is comparing the invoice numbers from that file to the invoice numbers in the file I've attached. there are going to be multiple instances where the same invoice number is displayed as quickbooks wont just dump an invoice number with a total and a rep name, it wan'ts to show it itemized with a total, making this more complicated. My goal is to find invoice numbers that match the ones from the file we were just working on, and pull the total cost along with the rep name. This info needs to end up back in the associated with the invoice and hours so the cost can be divided by the hours to get the total cost per hour with the rep name, so we know who bid the work. If you have suggestions as to the best way to go about this, I would be incredibly grateful!

Either way really, as an end result, I was going to try and have a new workbook saved with the data. I feel like the Timeclocks is a cleaner spreadsheet to work with, so maybe there?

I had some errors so i had to edit it. Sorry about that. It should be good now

I tired to use the VB code, but it gave me #VALUE! in all the worksheets...

Also, once the formulas are both inthere, you can close the workbooks

Do I need to put the formulas In, or is it an either VB or formula method of working the data?

The VBA puts that formula in for you.

Try re copy/pasting the VBA code because I made a mistake at first but I fixed it.
I just tried the code I pasted up top and it is working for me.

It's still doing the same thing, this is exactly what I have running, these two files in the same directory... does it work on your end with these?

I downloaded the file and ran it.
It runs for me.
On one of the cells with the formula, can you copy the formula and paste it here?

=IF(SUMIF('C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$D:$D,$C4,'C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$F:$F)=0,"",SUMIF('C:


I can see the broken formula, but I dont know what would cause it? is the file path too long?

If broken down it should be like this:

=IF( SUMIF('C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$D:$D,$C4,'C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$F:$F)
= 0, "",
SUMIF('C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$D:$D,$C4,'C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$F:$F)
)

Im not sure why the formula would be cut like that though.
Check to make sure the script is copied correctly. Especially this:

'Add the formulas
Range(Cells(4, 8), Cells(Bot, 8)).FormulaR1C1 = "=IF(SUMIF('[quickbooks file.xlsx]Sheet1'!C4,RC3,'[quickbooks file.xlsx]Sheet1'!C6)=0,"""",SUMIF('[quickbooks file.xlsx]Sheet1'!C4,RC3,'[quickbooks file.xlsx]Sheet1'!C6))"

Sorry, that was user error, I though the whole thing had copied, and it had not. this is the full formula that the VBA code outputs

=IF(SUMIF('C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$D:$D,$C4,'C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$F:$F)=0,"",SUMIF('C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$D:$D,$C4,'C:\Users\chrish\Desktop\JC FINAL\[quickbooks file.xlsx]Sheet1'!$F:$F))

This looks to be the same as what you have, but I end up with #VALUE! as all my outputs...

Can you follow these steps:
Open both workbooks,
Run Macro,
Then copy the formula

Right now, you closed your quickbooks file so it gave the formula a full path. It is hard to compare the formula to mine since the path is different

Right, sorry. This is the formula it gives. Not longer a #VALUE! but a blank cell

=IF(SUMIF('[quickbooks file.xlsx]Sheet1'!$D:$D,$C4,'[quickbooks file.xlsx]Sheet1'!$F:$F)=0,"",SUMIF('[quickbooks file.xlsx]Sheet1'!$D:$D,$C4,'[quickbooks file.xlsx]Sheet1'!$F:$F))

So what the formula does is it adds up all the values in column F of quickbooks where the column D of quickbooks equals the column C of timeclock (or the invoice number)
then if that value = 0, then it gives a blank.

This happened because your quickbooks file changed. You moved the values over a couple columns.

Also, you should note that your current quickbooks doesnt have any invoices matching the invoices in your timeclocks file

'Add the formulas
Range(Cells(4, 8), Cells(Bot, 8)).FormulaR1C1 = "=IF(SUMIF('[quickbooks file.xlsx]Sheet1'!C4,RC3,'[quickbooks file.xlsx]Sheet1'!C8)=0,"""",SUMIF('[quickbooks file.xlsx]Sheet1'!C4,RC3,'[quickbooks file.xlsx]Sheet1'!C8))"

Sorry again, I uploaded a new file with the Rep name you must not have seen... I figured you just changed the name back. Thank you for pointing that out, I forgot to change the info in the new file i generated to match the made up invoice numbers. I'll give all that a shot. I really appreciate your help, please forgive my ignorance.

Jason, not sure if you still are feeling up to helping out with this, but I have it all working now. The two things I am still struggling with are figuring out how to divide the total cost by the number of hours to get the cost per hour. I also need to have the rep name associated with the invoice displayed next to the final cost per hour. I attached the current files I am working with that are working so you can see where I am. I'm going to keep playing with it and I'll add any updates here as I progress. Thanks again!

Hey sorry about that
been super busy recently.

Hmm. I think this is a good one for you to try using the Record Macro.
Since you just want the sums of 2 columns, try recording a macro, and entering worksheet functions etc...
If you can get the code to work, take a look at it.
Also, post it on here if you want and I can help make it work or give you some tips.
Or if you cant get it to work, post the bit of code that you recorded.

Also, have you tried using the "Step In To" function in the VBA editor?
I find it very useful when I cant get something to work.
Instead of pressing the Run button or F5, press F8.
That will make your macro run line by like as you keep pressing F8.
That way, you can see the effect of the code on the workbook as they take place.