Copy Paste - Excel [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
763
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!