Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 137
  • Last Modified:

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.

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
brasiman
Asked:
brasiman
  • 9
  • 7
1 Solution
 
gowflowCommented:
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
 
brasimanAuthor Commented:
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
 
brasimanAuthor Commented:
I have attached an example.
Spreadsheet1.xlsx
Spreadsheet2.xlsx
0
Technology Partners: 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!

 
brasimanAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
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
 
brasimanAuthor Commented:
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
 
gowflowCommented:
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
 
brasimanAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
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
 
brasimanAuthor Commented:
So you need the exact header names?
0
 
gowflowCommented:
forget it. Let me build the macro
gowflow
0
 
gowflowCommented:
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
 
brasimanAuthor Commented:
goflow, you're awesome. That worked!! Thank you!!!!!
0
 
gowflowCommented:
Your welcome. Glad I could help.
gowflow
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now