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

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Viewers will learn the different options available in the Backstage view in Excel 2013.
Video by: Zack
Viewers will learn about various customizable options in Excel 2013.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now