Results 1 to 33 of 33

Copy and Paste cell value in for loop

Threaded View

  1. #1
    Registered User
    Join Date
    02-11-2013
    Location
    eugene, or
    MS-Off Ver
    Excel 2011
    Posts
    74

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1