Copy and paste macro into the next open cell

Closed
tpnass1 - Jun 11, 2010 at 08:19 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Jun 16, 2010 at 10:05 PM
Hello,


I have an Excel spreadsheet that I need a Macro for. The workbook contains 6 worksheets (5 data entry sheets and 1 overview sheet). I need a Macro to Copy cells B5:G70, of each of the 5 (data entry) worksheets and paste it into worksheet 1 (overview worksheet). The Macro I have currently written is as follows:

Sub Update()
'
' Update Macro
' Update Engineering Projects
'

'
Sheets("Walter").Select
Range("A5:G70").Select
Selection.Copy
Sheets("Overview").Select
Range("A5:A7").Select
ActiveSheet.Paste
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 180
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 182
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 190
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 194
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 204
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 207
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 207
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 207
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 194
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 157
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Sheets("J.D.").Select
Range("A5:G70").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Overview").Select
ActiveWindow.SmallScroll Down:=42
Range("A71:A73").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=81
Sheets("Patrick").Select
Range("A5:G70").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Overview").Select
Range("A137:A139").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=69
Range("A203:A205").Select
Sheets("Howard").Select
Range("A5:G70").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Overview").Select
ActiveWindow.SmallScroll Down:=0
Range("A203:A205").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=57
Sheets("Dave").Select
Range("A5:G70").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Overview").Select
Range("A269").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=15
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 265
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 244
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 182
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("A5:A7").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

This works however it does not paste the data into the next available empty cell which creates an excessively long spreadsheet of empty cells in between the data. I do not know the code to place the data into the next available cell when that cell is unknown due to constantly changing data. Can you help please?

20 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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
0
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 11, 2010 at 01:02 PM
Are you simply trying to copy all rows from all sheets into one sheet ?
0
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.
0

Didn't find the answer you are looking for?

Ask a question
Excuse the repeat of "Copy B5:G70"
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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
0
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!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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
0
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.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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
0
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!
0
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!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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,
0
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.
0
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
0
Please hold off until further notice... I may have found a fix.

Thanks
0
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.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 16, 2010 at 09:46 AM
Do you need to have merged cells?
0
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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


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



0