• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy and paste from one sheet to another automatically

Kellis

Member
Hi every one

I need help to automate a copy and paste and do not know the best way VBA or formula.

I have a spreadsheet where data is pasted into the first sheet an extra two columns are updated on this sheet by the person who pasted the info.
The second sheet is a breakdown of the raw data, using formulas and look ups to make more sense of the original data.
At present I had use a basic = sheet1(c2) to copy the data to the second sheet and copied the formula down the page, unfortunately this makes the book too big and slow.

What I want is everything on the first sheet to automatically populate the second sheet so that my extra formulas in my extra columns can work.

What in your opinion is the best way to do this VBA? or is there formula Match/ index that will do the job?

Thanks in advance
 
Hi Kellis ,

Firstly , why should you duplicate an entire worksheet ? Can your extra formulae in the extra columns refer to the original sheet directly ?

For example , if your original sheet is Sheet1 , and your duplicated sheet is Sheet2 , then a cell A1 in Sheet2 has a formula such as =Sheet1!A1 ; the same is copied to every cell in Sheet2 except for the extra columns.

Now , suppose your extra columns are P and Q , then a cell P2 can have a formula referring to various cells in Sheet2 ; why can't these formulae be rewritten to refer to the same cells in Sheet1 ?

Narayan
 
Hi Narayan

I am very new to excel and I am unsure what you mean

I have uploaded a file, it is hard to show you the full effect as the uploaded file needs to be so small.

To explain the use and why there are two sheets; The first sheet is copied and pasted from a e-mail that contains a nightly report by members of staff they audit the jobs and ensure they were logged/closed properly, all they do is a paste and add data into columns I,J,K. Also the staff doing the audit should not need to have any excel knowledge other than copy paste and enter data into cells. I want the input sheet to be as simple and straight forward.

The second sheet breakdowns the data to make it more understandable for other depts.
Columns C to J are copied from the first sheet and then via lookups and formula other columns are populated.
Column K,L,M on metering sheet are normally hidden I use these formulas to work other formulas. i.e for the summary sheet.
On my first sheet I had to copy the formula on the metering sheet down the page this made the sheet slow then on a previous post which you helped me on I added named ranges which meant I could not have formula in the empty columns.

What I need is advise on how to get the data to copy straight over without making my sheet to big and slow. I also played with the idea VBA code may be the best option but I do not understand formulas enough to know whats best.

Thanks
 

Attachments

  • New Metering Audit for Chandoo.xls
    129.5 KB · Views: 81
Hi Kellis ,

Let us be specific and refer everything to the workbook you have uploaded.

1. Sheet FD Input is the sheet where data is copied and pasted. Correct ?

2. Sheet Metering Jobs contains the following new columns with formulae in them : A , B , K , L , M , N and O. Correct ?

3. My question is : why do you need the columns C , D , E , F , G , H , I , J , P , Q , R and S to have data copied into them ? What use is eventually being made of the data on this sheet ?

A formula such as the following ( from cell N7 on the sheet Metering Jobs ) :

=IF(ISNA(VLOOKUP('Metering Jobs'!J7,lookUp!$I$1:$J$5,2,FALSE)),"",VLOOKUP('Metering Jobs'!J7,lookUp!$I$1:$J$5,2,FALSE))

can be rewritten as :

=IF(ISNA(VLOOKUP('FD Input'!H7,lookUp!$I$1:$J$5,2,FALSE)),"",VLOOKUP('FD Input'!H7,lookUp!$I$1:$J$5,2,FALSE))

Narayan
 
Hi Narayan

In answer to your question

1) Yes Sheet 1, this sheet the data is pasted into.

2) Yes the new columns have formula added

3) All the data is needed on the second page so other depts can look at all the data together, filter etc
Due to who will be looking at the spreadsheet it is best to keep them separate. An input sheet and the data sheet.

Do you know of a way I can transfer the data?
 
Hi Kellis ,

You can use a macro ; however , it will make the macro simpler if the order of the columns as well as their location is the same on the source and destination sheets ; can you rearrange your columns on the Metering Jobs sheet ?

Narayan
 
Hi

Would it make the macro much more difficult as the reason I have laid out the page is in order of importance to the viewer. I could change the order but it takes away from my purpose of the sheet.
 
Hi Kellis ,

It would make the macro longer , since each set of columns would have to be copied and pasted in turn.

Narayan
 
Thanks Narayan
Can you point me in the right direction for making the macro or what type of macro I am looking for. I like to understand what I am doing so I know if it comes up again.

Cheers

Kelly
 
Hi, Kellis!

Give a look at this file:
https://dl.dropboxusercontent.com/u...t for Chandoo (for Kellis at chandoo.org).xls

It uses 2 dynamic named ranges (FDInputTable for 1st worksheet and MeteringJobsTable for the 2nd). The definitions are the usual you yet know, except that they start at row 1 for purposes of easy clearing the output area (2nd worksheet) without having to write titles again.

This is the code:
Code:
Option Explicit
 
Sub DontDareToMakeKellisPeopleWork()
    '
    ' constants
    '  ranges
    Const ksWSSource = "FD Input"
    Const ksSource = "FDInputTable"
    Const ksWSTarget = "Metering Jobs"
    Const ksTarget = "MeteringJobsTable"
    '  columns
    Const ksColumnsSource = "@ A B C D E F G H I J K L"
    Const ksColumnsTarget = "@ C D E F G H I J Q P R S"
    '
    ' declarations
    Dim rngS As Range, rngT As Range
    Dim vColS As Variant, vColT As Variant
    Dim I As Integer
    '
    ' start
    '  ranges
    Set rngS = Worksheets(ksWSSource).Range(ksSource)
    Set rngT = Worksheets(ksWSTarget).Range(ksTarget)
    '  columns
    vColS = Split(ksColumnsSource)
    vColT = Split(ksColumnsTarget)
    With rngT
        If .Rows.Count > 1 Then
            For I = 1 To UBound(vColT)
                With .Columns(vColT(I))
                    Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
                End With
            Next I
        End If
    End With
    '
    ' process
    With rngS
        If .Rows.Count > 1 Then
            For I = 1 To UBound(vColS)
                With .Columns(vColS(I))
                    Range(.Rows(2), .Rows(.Rows.Count)).Copy rngT.Cells(2, vColT(I))
                End With
            Next I
        End If
    End With
    '
    ' end
    Set rngT = Nothing
    Set rngS = Nothing
    Beep
    '
End Sub

Note that columns with formulas are not cleared and are kept without erasing them, so you'll only have to be sure that you copied formulas for columns A:B and K:O thru the required no. of rows (in the sample file thru row 41, light orange shadowed in column A).

Just advise if any issue.

Regards!
 
Back
Top