Copy Paste - Excel [Solved/Closed]

Question

Copy Paste - Excel [Solved/Closed]

Report

ITTrainee - Apr 28, 2010 at 12:14 PM
rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

- 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.

Ask a question

rizvisa1 · Accepted Answer · 2010-04-28T12:27:57-0400

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 [Solved/Closed]

1 reply

Your reply

Subscribe To Our Newsletter!