Copy and paste macro into the next open cell
Closed
tpnass1
-
Jun 11, 2010 at 08:19 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 16, 2010 at 10:05 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 16, 2010 at 10:05 PM
Related:
- Copy and paste macro into the next open cell
- Clear only the formatting from the selected cell (leaving the content) - Guide
- Spell number in excel without macro - Guide
- Copy and paste fonts - Guide
- Excel macro to create new sheet based on value in cells - Guide
- Conditional formatting if cell contains text - Excel Forum
20 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 08:46 AM
Jun 11, 2010 at 08:46 AM
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Here is the link to my sample workbook. When you run the "Update" Macro, click on the "Overview" worksheet to see that the data has been brought in but there is blank cells throughout the worksheet from one sheets copy and paste to the next. My goal is to eliminate these spaces, so the "Overview" sheet is 1 continious document. Please help with the Macro.
https://authentification.site/files/22910476/sample_workbook.xls
Thanks
https://authentification.site/files/22910476/sample_workbook.xls
Thanks
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 01:02 PM
Jun 11, 2010 at 01:02 PM
Are you simply trying to copy all rows from all sheets into one sheet ?
Im trying to Copy cells "B5:G70" from each of the 5 data worksheets (Walter, J.D, Patrick, Howard, Dave) and put it into the "Overview" worksheet. Now, the data will change in the 5 data worksheets (i.e. "Walter" might have 6 rows of data one day then 2 a week later.) so, when the data is brought into the "Overview"worksheet I want it to be pasted into the next available empty cell. The code in people talk should say;
"open "Walter" worksheet
copy B5:G70
open "Overview" worksheet
paste next open cell
open "J.D." worksheet
copy B5:G70
open "Overview" worksheet
paste next available cell
open "Patrick" worksheet
copy B5:G70
open "Overview worksheet"
paste in next avalable cell
open "Howard" worksheet
copy B5:G70
open "Overview" worksheet
paste next available cell
open "Dave" Worksheet
copy B5:G70
copy B5:G70
open "Overview" worksheet
paste next available cell
scroll to top
save.
"open "Walter" worksheet
copy B5:G70
open "Overview" worksheet
paste next open cell
open "J.D." worksheet
copy B5:G70
open "Overview" worksheet
paste next available cell
open "Patrick" worksheet
copy B5:G70
open "Overview worksheet"
paste in next avalable cell
open "Howard" worksheet
copy B5:G70
open "Overview" worksheet
paste next available cell
open "Dave" Worksheet
copy B5:G70
copy B5:G70
open "Overview" worksheet
paste next available cell
scroll to top
save.
Didn't find the answer you are looking for?
Ask a question
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 03:03 PM
Jun 11, 2010 at 03:03 PM
Clarify two points for me.
1. Are you copying from 5 sheets (Walter, J.D, Patrick, Howard, Dave) because these are the only sheets in the book (minus Overview) or there would be more than than these 6 sheets in the book.
2. You said you are trying to copy from "B5:G70". Would it be correct to say in others word you want to copy between column B and G starting from row 5 till the last row of the sheet (what ever it may be less than 70 or over than 70 or what ever, as long as copy starts from row 5 and is within column B - G
These two points are important for design
1. Are you copying from 5 sheets (Walter, J.D, Patrick, Howard, Dave) because these are the only sheets in the book (minus Overview) or there would be more than than these 6 sheets in the book.
2. You said you are trying to copy from "B5:G70". Would it be correct to say in others word you want to copy between column B and G starting from row 5 till the last row of the sheet (what ever it may be less than 70 or over than 70 or what ever, as long as copy starts from row 5 and is within column B - G
These two points are important for design
Yes, there is only 5 "data" worksheets that I will be pulling Data. I have uploaded the actual spreadsheet i'm looking to modify. As far as the range, as long as it captures the B row and done throughout is fine. I chose "G70" because that is where my formating ends. We will never need data entered beyond this point and probably not even close to it.
I hope this helps.
Thanks!
I hope this helps.
Thanks!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 06:11 PM
Jun 11, 2010 at 06:11 PM
Try this
Sub Update()
'
' Update Macro
' Update Engineering Projects
'
'
Dim sOverView As String
Dim lOverView As Long
Dim Sheet As Variant
Dim lSheetRow As Long
sOverView = "Overview"
Sheets(sOverView).Select
lOverView = Cells(Rows.Count, "B").End(xlUp).Row
For Each Sheet In Sheets
If Sheet.Name = sOverView Then GoTo Next_Sheet
Sheet.Select
lSheetRow = Cells(Rows.Count, "B").End(xlUp).Row
If lSheetRow < 5 Then GoTo Next_Sheet
Range("B5:G" & lSheetRow + 2).Copy
Sheets(sOverView).Select
lOverView = Cells(Rows.Count, "B").End(xlUp).Row
If lOverView < 5 Then lOverView = 4
If lOverView > 5 Then lOverView = lOverView + 2
lOverView = lOverView + 1
Range("B" & lOverView).PasteSpecial
Next_Sheet:
Next Sheet
End Sub
That Macro worked with the exception that I needed to copy A:5 - row G and not B5 (my design error, this will be more efficient). Once, I fixed this the Macro worked great. However, now I am being told that I have to add some new worksheets and some new functions for the Macro to solve. So, I am preparing that information now. Thank you for your help. I hope you can continue to help.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 14, 2010 at 01:22 PM
Jun 14, 2010 at 01:22 PM
It will add the information from all sheets in the book except for "overview". So if you add new sheets with same design, macro will use that one also
I know... the problem now is that I have to add sheets with cheesy detail like one stating what the activity codes mean and on giving instructions on how to use the project tracker (for upper management). So, these sheets will obiously not have the same format and do not need ANY information brought in to the "Overview" sheet. I am putting together what I have now and I will re-upload it and send the link along with a description of what I am looking to accomplish.
Thanks for your help!
Thanks for your help!
The macro that I need now has to do the following:
1. Search in Worksheets; "Walter", "J.D", "Patrick", "Howard", and "Dave" for all fields with "100%" in column F.
a. If there is no fields with, "100%" in column F.
i. Proceed to step 2
b. If there is fields with, "100%" in column F.
i. Copy the Row (columns A-G) with the "100%" in column F.
ii. Paste special this data into the next available cell of the "Archive" sheet (same idea we did before so it is a continuous sheet).
iii. Go back to the Worksheet where the data was taken from and delete the row (so there are no blank rows within the sheet.)
iv. Have the cursor box be on cell B5 of the "Overview" worksheet.
2. Starting with the, "Walter" worksheet and then continuing with; "J.D, "Patrick", "Howard", and "Dave", copy cells A5:G70 (like we did previously) and paste the data into the "Overview" worksheet (starting with cell A5) in the next available cell down the A column.
3. Bring the cursor box to cell, "B5" of the "Overview" worksheet.
4. Save the Workbook.
Here is the link to the New Spreadsheet. I have some sample data inputted that can be used for the Macro creation.
https://authentification.site/files/22961326/3.xls
Question: With this Macro will the comments from the cells be carried to the destination as well? Also, when archiving the completed files (100%) the comments need to disappear from the person's (Walter, J.D., Patrick, Howard, Dave) worksheet so that the new data can move up (continuous worksheet with no blank rows). Will the Macro accomplish this?
Any other desin questions let me know... Ideas, let me know.
Thanks!
1. Search in Worksheets; "Walter", "J.D", "Patrick", "Howard", and "Dave" for all fields with "100%" in column F.
a. If there is no fields with, "100%" in column F.
i. Proceed to step 2
b. If there is fields with, "100%" in column F.
i. Copy the Row (columns A-G) with the "100%" in column F.
ii. Paste special this data into the next available cell of the "Archive" sheet (same idea we did before so it is a continuous sheet).
iii. Go back to the Worksheet where the data was taken from and delete the row (so there are no blank rows within the sheet.)
iv. Have the cursor box be on cell B5 of the "Overview" worksheet.
2. Starting with the, "Walter" worksheet and then continuing with; "J.D, "Patrick", "Howard", and "Dave", copy cells A5:G70 (like we did previously) and paste the data into the "Overview" worksheet (starting with cell A5) in the next available cell down the A column.
3. Bring the cursor box to cell, "B5" of the "Overview" worksheet.
4. Save the Workbook.
Here is the link to the New Spreadsheet. I have some sample data inputted that can be used for the Macro creation.
https://authentification.site/files/22961326/3.xls
Question: With this Macro will the comments from the cells be carried to the destination as well? Also, when archiving the completed files (100%) the comments need to disappear from the person's (Walter, J.D., Patrick, Howard, Dave) worksheet so that the new data can move up (continuous worksheet with no blank rows). Will the Macro accomplish this?
Any other desin questions let me know... Ideas, let me know.
Thanks!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 14, 2010 at 05:47 PM
Jun 14, 2010 at 05:47 PM
In a sheet loop as before, apply filter for F=100
As before find out the lastrows
if last row < 2 then goto next sheet
Copy the rows and paste as before
Delete the rows
Rows("2:" & lastrows).delete
remove the filter
Now you can directly use the original macro
For 100% job, you may have to copy and use paste special
1. copy
PasteSpecial xlPasteComments
PasteSpecial xlPasteValues
Use macro recorder,
As before find out the lastrows
if last row < 2 then goto next sheet
Copy the rows and paste as before
Delete the rows
Rows("2:" & lastrows).delete
remove the filter
Now you can directly use the original macro
For 100% job, you may have to copy and use paste special
1. copy
PasteSpecial xlPasteComments
PasteSpecial xlPasteValues
Use macro recorder,
Here is my "New" Uploaded Excel Workbook.
https://authentification.site/files/22971933/4.xls
I think I have the code for the macro "Update" laid out how it needs to be executed. I am receiving some debugging issues that I need help resolving please. Here is the code I currently have written (debugging error on line 69, "For Each Sheet In Sheets").
Sub Update()
'
' Update Macro
' Update Engineering Projects
'
'
Sheets("Walter").Select
Selection.AutoFilter Field:=3, Criteria1:="100%"
Sheets("J.D.").Select
Selection.AutoFilter Field:=3, Criteria1:="100%"
Sheets("Patrick").Select
Selection.AutoFilter Field:=3, Criteria1:="100%"
Sheets("Howard").Select
Selection.AutoFilter Field:=3, Criteria1:="100%"
Sheets("Dave").Select
Selection.AutoFilter Field:=3, Criteria1:="100%"
Dim sArchive As String, sOverview As String
Dim lArchive As Long, lOverview As Long
Dim Sheet As Variant
Dim lSheetRow As Long
sArchive = "Archive"
sOverview = "Overview"
Sheets(sArchive).Select
lArchive = Cells(Rows.Count, "B").End(xlUp).Row
For Each Sheet In Sheets
If Sheet.Name = sArchive Then GoTo Next_Sheet
If Sheet.Name = sOverview Then GoTo Next_Sheet
If Sheet.Name = sActivityCodes Then GoTo Next_Sheet
If Sheet.Name = sInstructions Then GoTo Next_Sheet
Sheet.Select
lSheetRow = Cells(Rows.Count, "B").End(xlUp).Row
If lSheetRow < 5 Then GoTo Next_Sheet
Range("A5:G70" & lSheetRow + 2).Copy
Sheets(sArchive).Select
lArchive = Cells(Rows.Count, "B").End(xlUp).Row
If lArchive < 5 Then lArchive = 4
If lArchive > 5 Then lArchive = l3 + 2
lArchive = lArchive + 1
Range("A" & lArchive).PasteSpecial
Sheets("Walter").Select
Selection.AutoFilter Field:=3
Sheets("J.D.").Select
Selection.AutoFilter Field:=3
Sheets("Patrick").Select
Selection.AutoFilter Field:=3
Sheets("Howard").Select
Selection.AutoFilter Field:=3
Sheets("Dave").Select
Selection.AutoFilter Field:=3
Sheets("Archive").Select
Sheets(sOverview).Select
lOverview = Cells(Rows.Count, "B").End(xlUp).Row
For Each Sheet In Sheets
If Sheet.Name = sArchive Then GoTo Next_Sheet
If Sheet.Name = sOverview Then GoTo Next_Sheet
If Sheet.Name = sActivityCodes Then GoTo Next_Sheet
If Sheet.Name = sInstructions Then GoTo Next_Sheet
Sheet.Select
lSheetRow = Cells(Rows.Count, "B").End(xlUp).Row
If lSheetRow < 5 Then GoTo Next_Sheet
Range("A5:G" & lSheetRow + 2).Copy
Sheets(sOverview).Select
lOverview = Cells(Rows.Count, "B").End(xlUp).Row
If lOverview < 5 Then lOverview = 4
If lOverview > 5 Then lOverview = lOverview + 2
lOverview = lOverview + 1
Range("A" & lOverview).PasteSpecial
Next_Sheet:
Next Sheet
End Sub
Please advise.
https://authentification.site/files/22971933/4.xls
I think I have the code for the macro "Update" laid out how it needs to be executed. I am receiving some debugging issues that I need help resolving please. Here is the code I currently have written (debugging error on line 69, "For Each Sheet In Sheets").
Sub Update()
'
' Update Macro
' Update Engineering Projects
'
'
Sheets("Walter").Select
Selection.AutoFilter Field:=3, Criteria1:="100%"
Sheets("J.D.").Select
Selection.AutoFilter Field:=3, Criteria1:="100%"
Sheets("Patrick").Select
Selection.AutoFilter Field:=3, Criteria1:="100%"
Sheets("Howard").Select
Selection.AutoFilter Field:=3, Criteria1:="100%"
Sheets("Dave").Select
Selection.AutoFilter Field:=3, Criteria1:="100%"
Dim sArchive As String, sOverview As String
Dim lArchive As Long, lOverview As Long
Dim Sheet As Variant
Dim lSheetRow As Long
sArchive = "Archive"
sOverview = "Overview"
Sheets(sArchive).Select
lArchive = Cells(Rows.Count, "B").End(xlUp).Row
For Each Sheet In Sheets
If Sheet.Name = sArchive Then GoTo Next_Sheet
If Sheet.Name = sOverview Then GoTo Next_Sheet
If Sheet.Name = sActivityCodes Then GoTo Next_Sheet
If Sheet.Name = sInstructions Then GoTo Next_Sheet
Sheet.Select
lSheetRow = Cells(Rows.Count, "B").End(xlUp).Row
If lSheetRow < 5 Then GoTo Next_Sheet
Range("A5:G70" & lSheetRow + 2).Copy
Sheets(sArchive).Select
lArchive = Cells(Rows.Count, "B").End(xlUp).Row
If lArchive < 5 Then lArchive = 4
If lArchive > 5 Then lArchive = l3 + 2
lArchive = lArchive + 1
Range("A" & lArchive).PasteSpecial
Sheets("Walter").Select
Selection.AutoFilter Field:=3
Sheets("J.D.").Select
Selection.AutoFilter Field:=3
Sheets("Patrick").Select
Selection.AutoFilter Field:=3
Sheets("Howard").Select
Selection.AutoFilter Field:=3
Sheets("Dave").Select
Selection.AutoFilter Field:=3
Sheets("Archive").Select
Sheets(sOverview).Select
lOverview = Cells(Rows.Count, "B").End(xlUp).Row
For Each Sheet In Sheets
If Sheet.Name = sArchive Then GoTo Next_Sheet
If Sheet.Name = sOverview Then GoTo Next_Sheet
If Sheet.Name = sActivityCodes Then GoTo Next_Sheet
If Sheet.Name = sInstructions Then GoTo Next_Sheet
Sheet.Select
lSheetRow = Cells(Rows.Count, "B").End(xlUp).Row
If lSheetRow < 5 Then GoTo Next_Sheet
Range("A5:G" & lSheetRow + 2).Copy
Sheets(sOverview).Select
lOverview = Cells(Rows.Count, "B").End(xlUp).Row
If lOverview < 5 Then lOverview = 4
If lOverview > 5 Then lOverview = lOverview + 2
lOverview = lOverview + 1
Range("A" & lOverview).PasteSpecial
Next_Sheet:
Next Sheet
End Sub
Please advise.
This may not even be the propper code for what I need to accomplish, I can't gete it to execute to see if it is the results I need produced. Should we start there? It needs to produce results as I mentioned in the message yesterday.
Thanks
Thanks
Please Help!
Here is the link of what I am looking to achieve in Word format.:
https://authentification.site/files/22986468/Macro_Help.doc
Here is the link of the excel spreadsheet:
https://authentification.site/files/22986170/5_good_formatting_macro_help.xls
Thanks.
Here is the link of what I am looking to achieve in Word format.:
https://authentification.site/files/22986468/Macro_Help.doc
Here is the link of the excel spreadsheet:
https://authentification.site/files/22986170/5_good_formatting_macro_help.xls
Thanks.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 16, 2010 at 09:46 AM
Jun 16, 2010 at 09:46 AM
Do you need to have merged cells?
I think I eliminated all the merged cells to make things easier except for the Rows 1-4. I left rows 1-4 merged so I didnt have to go back through and change ALL the Row references in the Macro.
No rows need to be merged. some Column cells need to be merged in a few instances.
Let me know if you need anything further.
Thanks
No rows need to be merged. some Column cells need to be merged in a few instances.
Let me know if you need anything further.
Thanks
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 16, 2010 at 10:05 PM
Jun 16, 2010 at 10:05 PM
First what this macro will NOT DO
1. It will not maintain the number of rows if rows are delete.
2. It will not keep the formula for the Rows deleted
You have to add the rows back yourself
As for formula why you even need a formula if all it does is show the sheet name. If the sheet name is DAVE, then why show why show DAVE.
The macro below will populate the sheet name in column A from where the data is being copied. See the comments. If you want it, the remove the if block
1. It will not maintain the number of rows if rows are delete.
2. It will not keep the formula for the Rows deleted
You have to add the rows back yourself
As for formula why you even need a formula if all it does is show the sheet name. If the sheet name is DAVE, then why show why show DAVE.
The macro below will populate the sheet name in column A from where the data is being copied. See the comments. If you want it, the remove the if block
Sub Update()
'
' Update Macro
' Update Engineering Projects
Dim sArchive As String, sOverview As String, sActivityCodes As String, sInstructions As String
Dim lArchive As Long, lOverview As Long, lNewRow As Long
Dim Sheet As Variant
Dim lSheetRow As Long
Dim iFieldCol As Integer
sArchive = "Archive"
sOverview = "Overview"
sActivityCodes = "ActivityCodes"
sInstructions = "Instructions"
Sheets("Overview").Select
ActiveSheet.UnProtect
Sheets(sArchive).Select
ActiveSheet.UnProtect
For Each Sheet In Sheets
If Sheet.Name = sArchive Then GoTo Next_Sheet
If Sheet.Name = sOverview Then GoTo Next_Sheet
If Sheet.Name = sActivityCodes Then GoTo Next_Sheet
If Sheet.Name = sInstructions Then GoTo Next_Sheet
Sheet.Select
ActiveSheet.UnProtect
Cells.Select
For iFieldCol = 1 To ActiveSheet.AutoFilter.Filters.Count
Selection.AutoFilter field:=iFieldCol
Next iFieldCol
lSheetRow = Cells(Rows.Count, "B").End(xlUp).Row
If lSheetRow < 5 Then GoTo Protect_Sheet
Selection.AutoFilter field:=3, Criteria1:="100%"
Range("A5:G" & lSheetRow + 2).Copy
Sheets(sArchive).Select
lArchive = Cells(Rows.Count, "B").End(xlUp).Row
If lArchive < 5 Then lArchive = 4
If lArchive > 5 Then lArchive = lArchive + 1
lArchive = lArchive + 1
Range("A" & lArchive).PasteSpecial
lNewRow = Cells(Rows.Count, "B").End(xlUp).Row
'this puts the sheet name in column A
If lNewRow >= lArchive Then
Range("A" & lArchive & ":A" & lNewRow).Value = Sheet.Name
End If
Sheet.Select
Rows("5:" & lSheetRow).Delete
Selection.AutoFilter field:=3
lSheetRow = Cells(Rows.Count, "B").End(xlUp).Row
If lSheetRow < 5 Then GoTo Protect_Sheet
Range("A5:G" & lSheetRow + 2).Copy
Sheets(sOverview).Select
lOverview = Cells(Rows.Count, "B").End(xlUp).Row
If lOverview < 5 Then lOverview = 4
If lOverview > 5 Then lOverview = lOverview + 1
lOverview = lOverview + 1
Range("A" & lOverview).PasteSpecial
lNewRow = Cells(Rows.Count, "B").End(xlUp).Row
'this puts the sheet name in column A
If lNewRow >= lOverview Then
Range("A" & lOverview & ":A" & lNewRow).Value = Sheet.Name
End If
Protect_Sheet:
Sheet.Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFiltering:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Next_Sheet:
Next Sheet
Sheets(sOverview).Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFiltering:=True
Sheets(sArchive).Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFiltering:=True
End Sub
