Macro

[gjh]

Hi All

I was wondering if you can help me with macro.

I am trying to copy certain cells from a different workbook.
At the moment, i have to copy them manually but i am trying create a macro to do this automatically for me.

Every month, i have to create the same workbook and enter the previous months data manually. So that I can see the differences compare to this month data.

So, how can I create a macro to automatically copy the values from the previous month workbook and paste it into the latest workbook?

Then for next month, I need to create another workbook and copy certain values from this month workbook.

Can someone advise me on how to go about to do this, pls?


Thank you!

gjh

 

[HaHoBe]

Hi, gjh,

give more detailed information about what needs to be included in the new workbook or record the action with the macro-recorder and start to work on the code in the VBIDE as the recorder will hard-code all actions (say the names of the workbook etc.). The place for the macro should either be the personal macorbook or an addin so that the macro is available when Excel starts up.

Ciao,
Holger

 

[gjh]

Hi HaHoBe

Thank for getting back to me!

I have tried to record the macro action but it not doing anything when i tried to run it. I guess im not doing the right thing.

Unfortunately, I am new to macro and VBIDE. therefore, i am going to have a lot of issues to fix this.

Basically, im trying to copy certain cells from wb1 and paste it into the same cells in wb2.

then next month, when i have created wb3, i was hoping wb3 to automatically copy cells from wb2 and paste it in wb3

i hope this make sense to you!


gjh

 

[HaHoBe]

Hi, gjh,

check the security level for macors (Tools/Macro/Security) which should be set to medium.

This code should go into a module, I opted for a choice for the (old) file to be opened:

Sub CopyValues()
Dim wbData As Workbook
Dim wbOld As Workbook
Dim varFileToOpen As Variant
Dim rngUsed As Range

Set wbData = ThisWorkbook
On Error Resume Next
varFileToOpen = Application.GetOpenFilename("Workbooks (*.xls), *.xls")
If varFileToOpen = False Then GoTo exit_here
On Error GoTo 0
Set wbOld = Workbooks.Open(varFileToOpen)
wbOld.ActiveSheet.UsedRange.Copy Destination:=wbData.ActiveSheet.Range("A1")
wbOld.Close savechanges:=False

exit_here:
Set wbOld = Nothing
Set wbData = Nothing
End Sub

Ciao,
Holger