Add to Excel Macros Solved/Closed

Question

Hello, 
I'm looking to edit a macros code and I'm having some issues. Right now our workbook includes a growing amount of days on a project (day 1, day 2, day 3, etc.).
We have the same employees working each day on the same project. I'm looking to add code to our current macros which would populate the names of the employees from the day before on each added sheet. I'm looking to populate the same data for lines 44 through 78, A through E.

I've included the existing code below. 

Sub CreateNewDay()
'
' CreateNewDay Macro
' Creates new tab for daily work log
'
' Keyboard Shortcut: Ctrl+Shift+D
Dim InputValue As Variant
Dim NewName As String
Dim NewName1 As String


Sheets("Blank to Copy").Visible = True
Sheets("Blank to Copy").Select
ActiveWindow.SmallScroll Down:=-12
Sheets("Blank to Copy").Select
Sheets("Blank to Copy").Copy Before:=Sheets("Blank to Copy")
Sheets("Blank to Copy (2)").Select
Sheets("Blank to Copy (2)").Name = "Day (number)"

InputValue = InputBox("Please enter the date (mm/dd/yy):")
Range("B3") = InputValue



NewName = InputBox("Enter day number on job (i.e. 1,2,3..100):")
NewName1 = "Day " & NewName
ActiveSheet.Name = NewName1


Sheets("Blank to Copy").Visible = False


End Sub





Any information would be greatly appreciated. Thank you in advance for your time.


Configuration: Windows 7 / Chrome 49.0.2623.112

vcoolio · Answer

Hello Victoria,

Just to elaborate:-

- You create a new sheet every day.
- You name the new sheet Day 1, Day 2 etc.. etc., etc.....
- I assume the sheets will remain in order.
- You wish to transfer data from the previous day to the next ( or the previous sheet to the next).
- The data to transfer will be from Range (A44:E78) as a "block" of data.
- This range will be transferred to the same range on the next sheet.
- It will always be the same range.

If the above is correct, try the following code in the same module as your code (just below your code):-


Sub CopyData()

Application.ScreenUpdating = False

ActiveSheet.Previous.Select
ActiveSheet.Range("A44:E78").Copy
ActiveSheet.Next.Range("A44").PasteSpecial xlPasteValues

Application.ScreenUpdating = True
Application.CutCopyMode = False
ActiveSheet.Next.Select

End Sub

Now call this macro from your macro by typing the sub name (CopyData, but without the parenthesis) just above End Sub in your macro.

To show you how the above code works (on its own in this case), following is the link to my test work book for you to peruse:-

https://www.dropbox.com/s/51ao3947and1cbr/Transfer%20same%20data%20block%20from%20previous%20sheet%20to%20next%29.xlsm?dl=0

Let us know if this works for you.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Victoria,

It is OK to cross post a thread but a Poster needs to advise where and how many times it has been cross-posted. The reason for this is that the people who help others with their issues on forums such as this are volunteers. They give their time freely to help others and there is no reward, financial or otherwise. 

A volunteer spends a great deal of time on a particular issue and as a script is not something that can be done within seconds, it is disheartening to find out that the same thread has been solved on another forum. In other words, the volunteer's time and efforts have been wasted.
Simple notification of a cross-post will help the volunteer decide whether or not it is worthwhile continuing with a particular thread.

This is also why a Poster is asked to read the forum rules before beginning a thread.

Anyway, back to your query:-

The error message has appeared because, when pasting, Excel has discovered that the destination cell(s) are a different size to the source cell(s).
The merged cells are creating the problem. Merged cells create havoc with VBA coding and are the bane of coders all over the Planet. So, if you unmerge the cells (permanently), whether they be in the source or the destination sheet, the code should work. It is quite simple to re-format your work sheets without merged cells.

If this still does not work, as per my test work book, then please supply a sample of your actual work book for us to investigate.. You can upload a sample to a free file sharing site such as DropBox or SpeedyShare then post the link to your file back here. Be careful with any sensitive data. Use dummy data.

Please do not upload a picture of your file.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Victoria,

There are still quite a few merged cells in both the current and previous sheets which are causing the problem. I tested the code with all cells unmerged and it works just fine. 
The simplest way to make sure that all cells are not merged is to do the following:-

- In the current and previous sheets, stay in the Home tab.
- At the top left of the data field you will see a little black arrow just above row 1 and to the left of "A" (top of Column A).
- Click on this arrow and the whole sheet will be selected.
- In the Alignment Group in the Home tab you will see the "Merge & Center" button light up.
- Click on the little down arrow beside the button then click on "Unmerge Cells".

All cells in the sheet will then be unmerged.

Click anywhere on the sheet to return to normal view.

Run the code that I supplied and you will see that it works.

You may want to start your data copying from cell A46 instead of A44 as you already have headings and sub headings in the new sheet.

BTW, I'm extremely disappointed to see that you have taken the code supplied to you above and taken it over to XLGuru and started a new thread with it over there when you were receiving help here ( I believe that here is the only place that you have received help thus far). Hence, this thread will now be marked as Solved/Closed.

You have enough information to finalise your query by yourself.

Good luck.

Cheerio,
vcoolio.

Add to Excel Macros

3 responses

Subscribe To Our Newsletter!