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
Bookmarks