Solved How can i copy and paste cells using VBA in excel

March 21, 2016 at 07:55:36
Specs: Windows 7
I've developed a Timesheet for my staff to use and I've acquired some VBA to copy the worksheet within a workbook and paste it into a new worksheet so that we can look back from one pay period to the next. I would like to include in my VBA to copy and paste to also move a staff members New balance for Sick Time, Comp Time, Vacation Time, etc... to the Previous Balance column. Basically, in the process of copy & paste, i'd like to move the date in G25 to D25, G26 to D26 and so forth. Here is the VBA that I'm using to copy and paste the worksheet.

Sub Test()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Timesheet")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
End Sub

I would appreciate and assistance you can provide.


See More: How can i copy and paste cells using VBA in excel

Report •

✔ Best Answer
March 22, 2016 at 17:07:44
Sub Test()
Dim ws1 As Worksheet
  Set ws1 = ThisWorkbook.Worksheets("Timesheet")
    ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
       ws1.Range("G25:G30").Copy
       ThisWorkbook.Sheets(Sheets.Count - 1).Range("D25:D30").PasteSpecial _
            Paste:=xlValues
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 21, 2016 at 08:32:44
I'm confused.

re: " i'd like to move the date in G25 to D25, G26 to D26..."

Do you mean data as opposed to date?

You don't say which sheet this is supposed to happen in - the sheet to be copied or the copied sheet or both? When should this happen - before the copy occurs or after the copy occurs?

re: "... and so forth."

"and so forth" is not specific enough for VBA. VBA has to either be told the range to move or be told how to figure out the range to be moved (i.e. the entire column or until it runs out of data or until it hits a empty cell or until it hits a "keyword", etc.)

Please keep in mind that we cannot see your worksheet from where we are sitting nor do we understand your work process. You need to be specific with your requirements so that we have a clear understanding of what you are trying to do.

Give us some more details and we'll see what we can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 21, 2016 at 09:10:01
Sorry for the errors and not providing enough information.

Using the VBA that I currently have, the current timesheet is copied into a new sheet for editing and submitting by the staff member. When the sheet is copied, I'd like the staff member's new balances for Holiday time (G25), Sick Time (G26), Vacation Time (G27), Bereacement (G28), Personal days (G29), Comp Time (G30) to be move to corresponding column (D25:D30) in the new, copied sheet. This way time is kept as a running balance and they/we can look back from one time sheet to the next to view a running balance. I hope this clears up what I am trying to accomplish.

In short, I am looking for a way to add to the VBA that I currently have to Copy and create a new sheet, to also move the data from these cells to the new cells so that they can be used in the next time sheet. I've included the information below in hopes to clear up how it looks.

Thank you again for your assistance!

			
Balance"	            "Previous(D)Used(E)Earned(F)"New (G)
Balance"
25. Holiday Hours		0.00	0.00	0.00	0.00
26. Sick Hours			40.00	0.00	0.00	40.00
27. Vacation Hours		0.00	0.00	0.00	0.00
28. Bereavement		          0.00	0.00	0.00	0.00
29. Personal Days		0.00	0.00	0.00	0.00
30. Comp Time			35.00	0.00	#REF!	#REF!



Report •

#3
March 21, 2016 at 10:22:42
Does adding this one instruction do what you want?

Sub Test()
Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Timesheet")
       ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
          ThisWorkbook.Sheets(Sheets.Count - 1).Range("G25:G30").Copy _
          ThisWorkbook.Sheets(Sheets.Count - 1).Range("D25:D30")
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 21, 2016 at 11:46:18
Thank you for your help. I think it does what I want it to do but am running into an error because I failed to think of the conflict, I'm getting a #VALUE! error.

When a staff member enters hours earned into the appropriate cell, this automatically updates the New Balance column. The VBA you suggest moves the New Balance data to the Previous Balance Column when a new sheet is created but, it does not remove the number entered by the staff member and instead copies it to the new sheet. I am guessing that this is causing the error. Do you think it would be best to create some additional VBA to restore the time sheet to some default values (such as 0) at the time of creating a new sheet?


Report •

#5
March 21, 2016 at 12:33:56
re: "The VBA you suggest moves the New Balance data to the Previous Balance Column when a new sheet is created..."

Which I believe is what you asked for.

but, it does not remove the number entered by the staff member and instead copies it to the new sheet."

You've lost me there. How can you want it to copy the number and remove it at the same time?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 21, 2016 at 13:37:48
The VBA you provided does do what I was hoping which is great, thank you!

Unfortunately, I did not think about that when the sheet is copied into a new sheet, it also copies the data the staff enters to acquire the new balance. for example: if the staff enters that they earned 7 hours of sick time into cell F26 this will give them a new balance of 47 hours in G26, we then (using the VBA) copy the sheet into a new sheet, move the 47 hours in G26 to D26 but the 7 that was placed in F26 still remains and is copied as well and moved to the new sheet. I think this is causing the #VALUE! error.

Any ideas for resolving this? Thank you very much for your assistance, you've been great!


Report •

#7
March 21, 2016 at 18:42:16
Without knowing what formulas you are using or the layout of your sheets, i couldn't possibly know what is causing the error. You should be able to use the formula evaluator to track down the source of your problem.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
March 22, 2016 at 07:09:51
Thank you for the suggestion and i was able to fix the error. it was a silly mistake on my part, in consistent data type in some of the cell.

I fixed the problem and re-ran the VBA and it made an exact copy of the time sheet but it did not move the data in column G (G25:G30) to Column D (D25:D30). I modified the VBA you provided me in an attempt to correct the problem and i have included it here. Unfortunately, while it does continue to make an exact copy of the time sheet (which is good), it does not move the data in Column G to Column D.

Sub Test()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Timesheet")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
ThisWorkbook.Sheets(Sheets.Count - 1).Range("G25").Copy Range("D25")
ThisWorkbook.Sheets(Sheets.Count - 1).Range("G26").Copy Range("D26")
ThisWorkbook.Sheets(Sheets.Count - 1).Range("G27").Copy Range("D27")
ThisWorkbook.Sheets(Sheets.Count - 1).Range("G28").Copy Range("D28")
ThisWorkbook.Sheets(Sheets.Count - 1).Range("G29").Copy Range("D29")
ThisWorkbook.Sheets(Sheets.Count - 1).Range("G30").Copy Range("D30")
End Sub

Any suggestions would be welcome and I really appreciate your patience on this, thank you for all your help.


Report •

#9
March 22, 2016 at 09:40:14
First, allow me to offer a posting tip:

Please click on the blue line at the bottom of my posts and read the instructions on how to use the pre tags to post VBA code and data in this forum. Thanks!

Let's break down your "copy" issue into smaller parts to make sure that we are on the same page. This may sound pretty simple, but please don't take it as demeaning, I'm just trying to make sure that I understand what you are asking for.

    Set ws1 = ThisWorkbook.Worksheets("Timesheet")
       ws1.Copy ThisWorkbook.Sheets(Sheets.Count)

These instructions make a copy of the Timesheet worksheet and place it before the last worksheet in the book, making it the "second to last sheet".

          ThisWorkbook.Sheets(Sheets.Count - 1).Range("G25:G30").Copy _
          ThisWorkbook.Sheets(Sheets.Count - 1).Range("D25:D30")

This instruction copies G25:G30 on the "second to last sheet" (via Sheets.Count - 1) and places it in D25:D30 of the "second to last sheet". In other words, the Copy occurs on the newly created sheet. It works for me every time.

Now, I am using "data" in G25:G30, not formulas, so perhaps that is part of the issue.

What do have in Timesheet!G25:G30?

What do you want in Timesheet(x)!D25:G30?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
March 22, 2016 at 13:14:03
Again thank you for your help. I will post the VBA/data correctly in the future, sorry about that.

I reviewed what you sent most recently and the first set of VBA works correctly, copies Timesheet perfectly. the section of VBA, to move data from column G to Column D is not working and instead of placing the data in Column D is placing zeros in its place.

I see that you have run the VBA and it is working correctly and you noted that you are using "data" and not formulas. I am however using formulas in column G

=SUM(F25,D25)-E25
and as you explained that this maybe causing the problem. What do you suggest?

I would like to have the results of the formula from above copied to column D. So if the results of the formula in G25 is 21, then 21 appear in D25 when the Timesheet is copied.

I hope I have explained everything to you and again, thank you for your assistance!


Report •

#11
March 22, 2016 at 17:07:44
✔ Best Answer
Sub Test()
Dim ws1 As Worksheet
  Set ws1 = ThisWorkbook.Worksheets("Timesheet")
    ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
       ws1.Range("G25:G30").Copy
       ThisWorkbook.Sheets(Sheets.Count - 1).Range("D25:D30").PasteSpecial _
            Paste:=xlValues
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
April 18, 2016 at 11:55:00
Thanks for all your help help Derbydad03! the VBA you provided me worked well when creating the first new timesheet within the workbook, no problems. Unfortunately, creating subsequent new timesheet from the last time (timesheet (2)) for example, does not move the data in cells G25:G30 to D25:D30 as planned. I have created multiple timesheets to test to see if it is just timsheet (3) or if it occurs on all timesheets after the first, and it does. If you remember, our code looks like this,
Sub Test()
ActiveSheet.Unprotect Password:="time"
Dim ws1 As Worksheet
  Set ws1 = ThisWorkbook.Worksheets("Timesheet")
    ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
       ws1.Range("G25:G30").Copy
       ThisWorkbook.Sheets(Sheets.Count - 1).Range("D25:D30").PasteSpecial _
            Paste:=xlValues
ActiveSheet.Protect Password:="time"

End Sub

Any help would be greatly appreciated!

Report •

Ask Question