Previous Topic Next Topic

Copy Paste - Excel

Solved/Closed
ITTrainee - Apr 28, 2010 at 12:14 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 - Apr 28, 2010 at 12:27 PM
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.

1 reply

Answer 1 / 1
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Apr 28, 2010 at 12:27 PM
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
1