Copy Paste - Excel Solved/Closed

Question

Hi

I have a spreadsheet with a list of job titles in column A.

there are a list of dates in columns G & I.

there is information in all the rows between A, G & I.

I want to write a macro does the following:

*	Copies the job title and all three of the dates

*	Creates a new worksheet 

*	Pastes the job titles next to all three of the dates in four new columns 

*	does this for all rows where there is information.

rizvisa1 · Answer

Not clear on there are a list of dates in columns G & I as further down you are saying that there are three dates that need to go to 3 column but G & I makes two. I am presuming you meant G, H and I and that you want each row  to be copied on a new sheet. 

This should give you a good idea


Sub createReports()
Dim lMaxRows As Long
Dim lThisRow As Long
Dim sDataSheet As String

    'name of the data sheet or main sheet
    sDataSheet = "Sheet1"
    
    Sheets(sDataSheet).Select
    
    ' max number of rows
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
        
    'stopping refresh of screen
    Application.ScreenUpdating = False
    
    'processing the rows starting from 2 to end
    For lThisRow = 2 To lMaxRows
        
        Sheets.Add
        
        Range("A1") = Sheets(sDataSheet).Range("A" & lThisRow).Value
        Range("B1") = Sheets(sDataSheet).Range("G" & lThisRow).Value
        Range("C1") = Sheets(sDataSheet).Range("H" & lThisRow).Value
        Range("D1") = Sheets(sDataSheet).Range("I" & lThisRow).Value
        
    Next lThisRow
    
    Sheets(sDataSheet).Select
    
    'starting refresh of screen
    Application.ScreenUpdating = True
    
    'allow events to be completed
    DoEvents

End Sub

Copy Paste - Excel

1 response

Similar discussions

Subscribe To Our Newsletter!