Help requested - Copy Paste, from one workbook to another workbook.

[Dingoz2012]

Mind blank.

I have two workbooks. Workbook 1 is the reporting workbook, has 2 sheets in it. Sheet1 is called Data, sheet2 is Count by Member (where I have all the graphs and results set out. Workbook 2 gets labelled with the date that it was created on. This workbook is where multiple workbooks data is consolidated.

I'm drawing a mind blank on how to proceed to open workbook2 , and then copy all the cells from sheet1 into Workbook 1 on the sheet named Data, when the user clicks on a shape which is assigned a macro in workbook 1

* So the managers don't have to open up the results workbook, select all, ctrl c, and then click in the main workbook and click ctrl v.


Any help greatly appreciated as always.

 

[oldbrewer]

in workbook1 record a macro where you open the second workbook then select the first workbook - this will give you the code..........

 

[mumps]

What is the actual full name of Workbook2 including the extension (xlsx, xlsm)? What is the name of Sheet1 in Workbook2? Where on the Data sheet in Workbook1 do you want to paste the copied data? What is the full path to the folder containing Workbook2?

 

[JLGWhiz]

Workbook 2 gets labelled with the date that it was created on

Is the date plus file extension the full name, or does it have a constant such as "Main" & Format(Date, "mm-dd-yyyy")?

 

[Fluff]

How about

Sub Dingoz2012()
   Dim Fname As String
   Dim Wbk As Workbook
   Dim Ws1 As Worksheet
   
   Set Ws1 = ThisWorkbook.Sheets("Data")
   Fname = Application.GetOpenFilename
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets("Sheet1").UsedRange.Copy Ws1.Range("A" & Rows.Count).End(xlUp).Offset(1)
   Wbk.Close False
End Sub

 

[JLGWhiz]

How about

Sub Dingoz2012()
   Dim Fname As String
   Dim Wbk As Workbook
   Dim Ws1 As Worksheet
   
   Set Ws1 = ThisWorkbook.Sheets("Data")
   Fname = Application.GetOpenFilename
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets("Sheet1").UsedRange.Copy Ws1.Range("A" & Rows.Count).End(xlUp).Offset(1)
   Wbk.Close False
End Sub

that's cheating, @Fluff. Now we may never solve the mystery of workbook 2.

 

[Dingoz2012]

Workbook 2 gets labelled with the date that it was created on

Is the date plus file extension the full name, or does it have a constant such as "Main" & Format(Date, "mm-dd-yyyy")?

Mystery solved. Sorry I forgot to mention. Workbook 2 is always saved as "Backup Date" & todays date & .xls, and has only one sheet in it "Sheet1" It opens with the code I have already, just was stuck on the copy and pasting the data from WBK2 "Sheet1" into WBK 1 "Data"

Thankyou Fluff, that helped a lot. Added in a clear sheet command, to wipe all the data. New stuff gets pasted in like a boss.


Only the one thing left to figure out....

 

[Fluff]

How about

   Ws1.UsedRange.Clear
   Wbk.Sheets("Sheet1").UsedRange.Copy Ws1.Range("A1")

 

[Dingoz2012]

How about

   Ws1.UsedRange.Clear
   Wbk.Sheets("Sheet1").UsedRange.Copy Ws1.Range("A1")

Worked like a charm. Only thing left to figure out is how to not have the folder open, and the user select the file. Would like to have this happen in the background. File will always be labelled "Backup Date" & todays date & .xlsx

 

[Fluff]

I don't understand as you said

It opens with the code I have already