Solved

How to create a button that copies a ranges of cells in one spreadsheet, then copies that data to another spreadsheet, appending what is already there.

Posted on 2014-09-18
16
131 Views
Last Modified: 2014-09-25
I have two spreadsheets. One spreadsheet has a range of data. It is an ODBC query into a SQL database. I pull that data, modify the data and print it. Well, as you know, the next time i refresh the data, the changes i made to the query will be erased. I am looking for a way when I refresh the query, i make my changes, then click a button or something that copies all the data onto a totally different spreadsheet. Then, the next week, or whenevere i open the query, i can refresh the data, then click this button, then it copes and ADDS TO the other spreadsheet. So basically this 2nd spreadsheet just keeps getting added to. Is it possible to do something like this? If so, is there a macro that would do this? Thanks!!
0
Comment
Question by:brasiman
  • 9
  • 7
16 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40331361
could you post the sample workbook that have this query this way we can copy the correct data output as need to know the columns and rows of data and their format as your question is rather general.
gowflow
0
 

Author Comment

by:brasiman
ID: 40331380
Hi gowflow. Thanks for the quick reply! Yes, i will get a sample now. I'll have it posted in about 5-10 minutes. Thanks!
0
 

Author Comment

by:brasiman
ID: 40331396
I have attached an example.
Spreadsheet1.xlsx
Spreadsheet2.xlsx
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:brasiman
ID: 40331688
I hope i'm explaining it well enough. Sorry if not. So i would click the button on spreadsheet one each day. that button then copies the data on spreadsheet1, and pastes it on Spreadsheet2 under all the data. So spreadsheet2 is a compilation that gets bigger and bigger every time I click the button on spreadsheet1.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40332041
Noted above and here are some questions:

1) Can Spreadsheet1 and Spreadsheet2 be in the same workbook ? or they are 2 different workbooks ?
2) If same workbook then can I have the real name of both sheets ?
3) If not same workbook then I need name of the second workbook and the destination sheet name.

I don't need a sample just as sample I understood what you want I want the real workbook that you have as I will see the real sheet name and the real data going from what line to what line etc.. so I can design it properly.

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40332054
I you want in the real workbook just change the data that you feel is sensitive or confidential but keep the format. The format is as important as the data.
gowflow
0
 

Author Comment

by:brasiman
ID: 40332755
Its all financial data. I am querying my GL. Spreadsheet1 on my example is the query into my GL. In answer to your questions: 1. We would like them in different workbooks. 2. They are different workbooks. 3. The 2nd (desitnation) workbooks is called Spreadsheet2. The destination worksheet is Sheet1.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40332948
I need to full path to Spreadsheet2 so I can hardcoded it in the macro.
I need the columns letters from to that have data and are they all following each other or you have some blank columns and blank rows in between ? like I expect and answer like form Col A to say Z and no blank columns no blank rows something like this.
gowflow
0
 

Author Comment

by:brasiman
ID: 40333008
Hi gowflow. Ah I see. There are no blank columns from column A to O. The headers on both workbooks are the same and will not change. Spreadsheet1 is on D:\GL\Spreadsheet1.xlsx. Spreadsheet is on D:\GL\Spreadsheet2.xlsx. So they are both on the D drive in a folder called GL. (D:\GL).
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40333100
if your spreadsheet1 contain an ODBC query into a SQL database then it is coded as a macro or as an attach external database ? my question is because I will develop the macro in spreadsheet1 and then the name will move from spreadsheet1.xlsx to spreadsheet1.xlsm this is why I needed you original file so I can modify it into an .xlsm or else you will hv a hard time transferring this !!

Capiche all my questions?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40333105
I do not care about the data you can simply delete it from spreadsheet1 before attaching but at least I will have all the rest coded there and it will be easier for you.
gowflow
0
 

Author Comment

by:brasiman
ID: 40333215
So you need the exact header names?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40333707
forget it. Let me build the macro
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40333759
ok here is the code for that.

Option Explicit

Sub ArchiveData()
Dim WB As Workbook
Dim WS As Worksheet
Dim ThisWS As Worksheet
Dim MaxRow As Long, MaxRowThis As Long, I As Long, FmRowThis As Long, MaxColThis As Long
Dim sFileName As String, sPathName As String

'---> Set Variables
Set ThisWS = ActiveSheet
MaxRowThis = ThisWS.Range("A" & ThisWS.Rows.Count).End(xlUp).Row
MaxColThis = ThisWS.Columns(ThisWS.Columns.Count).End(xlToLeft).Column
sFileName = "Spreadsheet2.xlsx"
sPathName = ActiveWorkbook.Path & "\"

'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'---> Open Destination Workbook
Set WB = Workbooks.Open(Filename:=sPathName & sFileName)
Set WS = WB.Sheets("Sheet1")
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row

'---> Set Beginning / Destination row for copy / Paste
If MaxRow = 1 And WS.Range("A1") = "" Then
    FmRowThis = 1
Else
    FmRowThis = 2
    MaxRow = MaxRow + 1
End If

'---> Copy Data
ThisWS.Range(ThisWS.Cells(FmRowThis, "A"), ThisWS.Cells(MaxRowThis, MaxColThis)).Copy WS.Cells(MaxRow, "A")

'---> Save
WB.Close savechanges:=True
Set WB = Nothing
Set WS = Nothing

'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With


'---> Advise User
MsgBox "Data Archived Successfully.", vbExclamation, "Archive Data"

End Sub

Open in new window


PLs try the attached workbook by activating macros and executing the Archive button. It will look for the workbook called Spreadsheet2 that is sitting in the same directory as Spreadsheet1 and update the data.

Let me know.
gowflow
Spreadsheet1.xlsm
0
 

Author Comment

by:brasiman
ID: 40345103
goflow, you're awesome. That worked!! Thank you!!!!!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40345411
Your welcome. Glad I could help.
gowflow
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Logon script fails 23 45
Copying directory listing to specific files in another directory 8 26
How do I extract data from Excel worksheets? 5 40
Code enhancement 4 22
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Viewers will learn various types of data validation for different data types in Excel 2013.
Viewers will learn the basics of formula auditing in Excel 2013.

830 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