none
Copy worksheets (names, data and format) from saved data file to open active workbook RRS feed

  • Question

  • I have data in a saved desktop folder: C:\Users\NAME\Desktop\QuickFS\Data.xlsx

    The "Data" file has 5 worksheets (Overview, Income Statement, Balance Sheet, Cash Flow Statement, Ratios), which I would like to copy into my active workbook (named "Data_Analysis"). My active workbook is located at: C:\Users\Name\Desktop\Data_Anlaysis.xlsx

    "Data_Analysis" will be open at the time of the import, while the "Data" file will be closed.  The "Data_Analysis" file has many worksheets, including 5 worksheets named identically to those found in the "Data" file (Overview, Income Statement, Balance Sheet, Cash Flow Statement, Ratios). When the data in the "Data" file changes, I would like to run the VBA and import the new data into the correct worksheets and overwrite the previous data. Because I have formulas in the "Data_Analysis" file linking to the 5 worksheets (Overview, Income Statement, Balance Sheet, Cash Flow Statement, Ratios), these sheets can't be created as new, and they must overwrite on the appropriate sheets in the "Data Analysis" workbook. 

    Thanks in advanced for your help. 

    CDT



    Sunday, February 3, 2019 9:34 PM

All replies

  • Hi,

    I'm confused and would like to confirm your requirement.
      (1) Where your macro (VBA code) is? In "Data" file, or in "Data_Analysis" file?
      (2) Is the number of Rows the same between "Data" and "Data_Analysis"?
      (3) Which Columns (or Rows) have Formulas in "Data_Analysis"?
          (I suppose that Formulas in "Data_Analysis" should not be removed, when data is copied into them.)
      (4) Please specify which Cells (Columns) will be changed in "Data" file.
          (I want to know the range where data will be changed.)
    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html


    • Edited by Ashidacchi Sunday, February 3, 2019 11:57 PM
    Sunday, February 3, 2019 11:52 PM
  • Sorry for the confusion.

    1) In "Data_Analysis" file - as it will be open and the "Data" file will be closed. So I need to copy/import the data from the "Data' file into the already open "Data_Analysis" file.

    2) I believe so, yes. The amount of data in the data file is minimal and the max number of rows between the two files is the same.

    3) The formulas are in different worksheets in Data_Analysis (they are not in Overview, Income Statement, Balance Sheet, Cash Flow Statement, Ratios),but will refer to the data that is copied into those worksheets. I don't yet know which cells/rows exactly as I won't start calculations until I know I can copy/import the data reliably via VBA.

    4) No data will be changed, just copied/imported into the "Data_Analysis" File. 

     
    Monday, February 4, 2019 12:17 AM
  • Hi,

    I asked as the below:
    (1) Where your macro (VBA code) is? In "Data" file, or in "Data_Analysis" file?
       --- (solved: no need to answer)
          According to your description, macro (VBA code) is in "Data" file, and its extension should be ".xlsm".  -- (solved)

    (2) Is the number of Rows the same between "Data" and "Data_Analysis"?
       --- (not solved: please answer)
          I should have asked like this:
          Is the number of Rows of "Data_Analysis" the same between before copying and after copying from "Data" file?  (Does the number of Rows of "Data_Analysis" vary after copying?)  

      (3) Which Columns (or Rows) have Formulas in "Data_Analysis"?
          (I suppose that Formulas in "Data_Analysis" should not be removed, when data is copied into them.)
       --- (not solved: please answer)     Why I asked this? It is because not being removed/deleted Formulas after copying.
         It is very important that you specify where Formulas are in "Data_Analysis".
         Please specify.
    (4) Please specify which Cells (Columns) will be changed in "Data" file.
          (I want to know the range where data will be changed.)
       --- (not solved: please answer)
            I know no data should be changed after copying/importing into "Data_Analysis".

          I want to know which cells (columns) users can input before copying.
         (We need to know which cells has been changed for making macro.)  Please specify. 
    Regards,

    P.S. I'm not good at English. I'd like you to understand what I want to say.

            

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Monday, February 4, 2019 3:00 AM
  • Perhaps we can make this easier?  My formulas will not effect this copying. 

    If I have an excel file called "Data_Analysis" that has "sheet1" and 5 sheets titled: Overview, Income Statement, Balance Sheet, Cash Flow Statement, and Ratios that are blank. 

    I have another excel file called "Data" that has 5 sheets with the data I need on sheets named Overview, Income Statement, Balance Sheet, Cash Flow Statement, and Ratios. These sheets have the data I want to copy. I need the VBA code to copy the data from the "Data" file to the "Data_Analysis" file with the corresponding name (Ie: Data Income Statement copied to Data_Analysis Income Statement). The only caviat is that I want the data copied from the Data file to overwrite the data in the Data_Analysis workbook. 

    I have code shown below, but it only brings in the first active sheet (in this case the Overview), I need to know how to modify the code, to bring in the remaining sheets (Income Statement, Balance Sheet, Cash Flow Statement, and Ratios).

    Public Sub OpenQuickFS()

    ' *************************************************
    ' Define Workbook and Worksheet Variables
    ' *************************************************
    Dim wkbMyWorkbook As Workbook
    Dim wkbWebWorkbook As Workbook
    Dim wksWebWorkSheet As Worksheet
    Dim wsDest As Worksheet

    Set wkbMyWorkbook = ActiveWorkbook
    On Error Resume Next
    Set wsDest = wkbMyWorkbook.Worksheets("IS")
    wsDest.Cells.ClearContents
    On Error GoTo 0

    ' *************************************************
    ' Open The Web Workbook From Desktop (Financial Statements)
    ' *************************************************
    Workbooks.Open ("C:\Users\Name\Desktop\QuickFS\PPG_Data.xlsx")

    ' *************************************************
    ' Set the Web Workbook and Worksheet Variables
    ' *************************************************
    Set wkbWebWorkbook = ActiveWorkbook
    Set wksWebWorkSheet = ActiveSheet

    ' *************************************************
    ' Copy The Web Worksheet To My Workbook and Rename
    ' *************************************************
    If wsDest Is Nothing Then
        wksWebWorkSheet.Copy after:=wkbMyWorkbook.Sheets("Start")
        wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "IS"
    Else
        wksWebWorkSheet.Cells.Copy wsDest.Range("A1")
    End If

    ' *************************************************
    ' Close the Web Workbook
    ' *************************************************
    wkbMyWorkbook.Activate
    wkbWebWorkbook.Close
    End Sub


    Monday, February 4, 2019 3:38 AM
  • Hi,

    I'm afraid you need to provide (share) your two files, via cloud storage such as OneDrive, Dropbox, etc.  It will make me understand your requirements easily.

    Remember to modify your vital/sensitive data before sharing.

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Monday, February 4, 2019 4:59 AM
  • Hi cdtouchberry,

    In advance, I'm not sure I can understand exactly what you want to do. 
    I've tried and shared "Data_Analysis2_ash.xlsm" via OneDrive.
    Please download and check it.

    [note]
      My VBA code is premised on that two files ("Data_Analysis2_ash.xlsm" and ""Data.xlsx") are in "C:\test\cdtouchberry" folder.
      When you open "Data_Analysis2_ash.xlsm", sheet "Integrated FS" will be shown and you will see two buttons on it.
      [Get latest Data] button --- copy sheets from Data to Data_Analysis
      [Clear Sheets (for Debugging)] button --- clear related sheets (we can try to copy and verify again and again) 

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Tuesday, February 5, 2019 1:15 AM