?
Solved

How do I copy data from a worksheet to a Summary listing on another worksheet?

Posted on 2015-02-07
2
Medium Priority
?
98 Views
Last Modified: 2016-02-10
Hello Experts,

This is a continuation of http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28588137.html and the updated code is below.

In addition to copying the worksheets, I'd like to copy parameters from the "Calc" worksheet to a "Summary" worksheet. The Summary worksheet has 7 columns and the copied info needs to be added to the bottom of the list. Here's the mapping from the Calc worksheet to the Summary worksheet:

Calc:P1 -> column 1
Calc:P3 -> column 2
Calc:X71 -> column 3
Calc:x73 -> column 4
Calc:W67 -> column 5
Calc:X67 -> column 6
Calc:w68 -> column 7

Thanks,

Ron


Sub AddSheet()

    Dim lngSheets As Long
    Dim intCount As Integer
   
    'copy worksheet
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Unprotect Password:="1111"
    'remove "clear" and "create worksheet" buttons
    ActiveSheet.Shapes.Range(Array("Button 1", "Button 2")).Select
    Selection.Delete
    ActiveSheet.Range("O1:O3").Value = ""
        'remove formulas from worksheet
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
   
    ' check for multiple tab names, ie BillSmith1, BillSmith2, etc
    For lngSheets = 1 To Sheets.Count
        If Left$(Sheets(lngSheets).Name, Len(Range("P1"))) = Range("P1") Then
            intCount = intCount + 1
        End If
    Next
 '   If intCount > 0 Then
        ActiveSheet.Name = Range("P1") & intCount + 1 & "A"
 '   Else
 '       ActiveSheet.Name = Range("P1") & intCount
 '   End If
    'reset password protection
    ActiveSheet.EnableSelection = xlNoSelection
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1111"
   
    'Copy over Answer Sheet
    Sheets("Answer Sheet").Select
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Unprotect Password:="1111"
    ' remove formulas
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    ' clear secondary DISC if not shown
    If ActiveSheet.Range("A17").Value = "" Then
       Range("A17:E21").Select
       Selection.ClearContents
    End If
    ' rename answer sheet
    ActiveSheet.Name = Range("A1") & intCount + 1 & "B"
    ActiveSheet.EnableSelection = xlNoSelection
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1111"
0
Comment
Question by:bikeski
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 40596564
Hi

Sub CalcToSummary()
    
    Dim wksCalc         As Worksheet
    Dim wksSummary      As Worksheet
    Dim v
    
    
    Set wksCalc = Worksheets("Calc")
    Set wksSummary = Worksheets("Summary")
    
    With wksCalc
        v = Array(.Range("p1").Value, .Range("p3").Value, .Range("x71").Value, .Range("x73").Value, _
                .Range("w67").Value, .Range("x67").Value, .Range("w68").Value)
    End With
    
    wksSummary.Range("a" & wksSummary.Rows.Count).End(3)(2).Resize(, 7).Value = v
    
End Sub

Open in new window


Kris
0
 

Author Closing Comment

by:bikeski
ID: 40599496
Thanks Kris, worked like a charm. I just added a call to the original VBA code:

Call CalcToSummary()
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question