VBA code to copy data to new workbook

[AndyFarrell79]

Hi All,

I've not had much experience with VBA code, other than recording macros and editing them a little. What i would like to do is code a macro to copy the data (text only) of a whole worksheet into a blank worksheet in a different workbook, effectively allowing the user to "upload" the data to a master workbook.

The user will complete data entry into a daily template worksheet containing all formulae to obtain necessary daily data. I would like them to then be able to click a button that runs a macro copying the text data from the daily template to a monthly workbook. I have no problem recording this in a macro and the code for that (no doubt there is a more efficient way) is:

Sub Load_Report()
'
' Load_Report Macro
'

'
Workbooks.Open Filename:= _
"C:\Users\afarrell\Desktop\New-PSR Re-Structure\Jan.xlsx"
Windows("Production Supervisors Report.xlsm").Activate
Cells.Select
ActiveSheet.Shapes.Range(Array("Group 37")).Select
Cells.Select
Selection.Copy
Windows("Jan.xlsx").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Range("B8:V8").Select
End Sub


The monthly workbook will contain 31 sheets (named 1,2,3...31) for the days, and each month will have it's own workbook (named Jan, Feb, Mar...Dec).
The key thing I need help with is using cells within the daily workbook to determine the month and day used by VBA for the filename and worksheet respectively.

Cell B5 contains the day (eg. 1) and C5 the month (eg. Jan)

Any help on this would be immensely appreciated!

Regards,
Andy ​

 

[GTO]

Any chance that you have control over the daily worksheet/workbook's design and could put the date (as an actual date value) in one cell?

 

[jbeaucaire]

Crossposted:
VBA code to copy data to new workbook

A solution as been posted there...

 

[ukmikeb]

Andy

I appreciate that there may be an urgency to gaining a solution to your problem, but please do respect Forum Rules which request that you post a link to threads in other forums where you ask the same question.

Cross-posted here :- VBA code to copy data to new workbook

Btw as a respondent to your earlier thread I do not see that this problem addresses your earlier problem from "a different angle".

 

[GTO]

Crossposted:
VBA code to copy data to new workbook

A solution as been posted there...

Hi Jerry and Thank You so very much

At Andy:

I did see in the other thread that you assured not cross-posting without advisement. Thank you so very much and FWIW, I totally understand 'getting your feet wet' when first joining sites or in your case, joining an additional site. I personally was lucky a fellow in Poole didn't fly over and box my ears off after I joined my first site.

Mark

 

[AndyFarrell79]

Thanks all - Sloved the issue refer cross post:

VBA code to copy data to new workbook

A