Making my macro independent on future Sheets
Solved/Closed
rizzob95
Posts
4
Registration date
Tuesday July 31, 2018
Status
Member
Last seen
August 2, 2018
-
Updated on Nov 11, 2018 at 02:52 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 2, 2018 at 08:45 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 2, 2018 at 08:45 AM
Related:
- Making my macro independent on future Sheets
- Sheets right to left - Guide
- Spell number in excel without macro - Guide
- Excel macro to create new sheet based on value in cells - Guide
- Macro excel download - Download - Spreadsheets
- How to take screenshot of excel sheet on laptop - Guide
6 responses
Do you need to count the number of sheets?
Try this:
Try this:
Function Counttabs()
Counttabs = ThisWorkbook.Worksheets.Count
End Function
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 1, 2018 at 01:39 AM
Aug 1, 2018 at 01:39 AM
Hello Rizzob95,
Further to what ac3mark has supplied you'd be glad to know that you weren't that far away with your efforts.
To reference all worksheets, regardless of name and quantity thereof, and to exclude the "Summary" sheet from the process, the syntax needs to be something like this:-
Hence, all source sheets will have the fixed range copied/pasted to the Summary sheet all together.
I hope that this helps.
Cheerio,
vcoolio.
Further to what ac3mark has supplied you'd be glad to know that you weren't that far away with your efforts.
To reference all worksheets, regardless of name and quantity thereof, and to exclude the "Summary" sheet from the process, the syntax needs to be something like this:-
Sub Rizzob() Dim ws As Worksheet, sh As Worksheet Set sh = Sheets("Summary") Application.ScreenUpdating = False For Each ws In Worksheets If ws.Name <> "Summary" Then '---->Excludes the Summary sheet from the copy/paste process. ws.Range("A30:K40").Copy sh.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) '---->This is the copy/paste line. It assumes that your range is always fixed at A30:K40 in each source sheet. End If '---->Closes off the IF statement. Next ws '---->The search moves on to the next sheet. Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
Hence, all source sheets will have the fixed range copied/pasted to the Summary sheet all together.
I hope that this helps.
Cheerio,
vcoolio.
rizzob95
Posts
4
Registration date
Tuesday July 31, 2018
Status
Member
Last seen
August 2, 2018
Aug 1, 2018 at 08:31 AM
Aug 1, 2018 at 08:31 AM
Thank you very much for the quick response. I placed your code into VBA but it wasn't quite what I needed. I decided to open two new clean sheets in order to make it more simplified until get it working, then I can bring in the rest. I have the sheet that I will be entering data into and the sheet where the data will eventually be placed when I hit the macro button on each copied sheet. Hopefully to make it less confusing as possible, here's what I'm trying to do. I have sheet 1 that needs specific data entered based on information given, sheet 2 ("summary" tab) is the tab that will be collecting all the data to later on sort and run other macros. My problem I'm having, is that I don't know how many data sheets I will be entering each time I use the program. I could have 10 data sheets or ? amount that will be copied to the "summary". That code I had entered was so that when I copy and add another sheet the macro is independent to the new sheet. That new data sheet will transfer the new info to the next available cells on the summary sheet and not over write the cells with info already in it.
So Sorry for the lengthy reply, I just don't know how I can explain it any easier. Thank you
So Sorry for the lengthy reply, I just don't know how I can explain it any easier. Thank you
rizzob95
Posts
4
Registration date
Tuesday July 31, 2018
Status
Member
Last seen
August 2, 2018
Aug 1, 2018 at 09:05 AM
Aug 1, 2018 at 09:05 AM
I got it to a point. Here's the code I'm using.
Sub CopyRows()
Dim wk As Worksheet
Dim wkD As Worksheet
Set wkD = Worksheets("tst")
Set wk = ActiveSheet
wk.Range("A30:K40").Copy _
wkD.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub
BUT, when I was doing some testing and copied a 3rd sheet I get this "The name "Direction" already exists. Click Yes to use that version of the name, or click No to rename the version of "Direction" you're moving or copying". If I hit yes it then says "The name "H" already exists. Click yes to use that version of the name, or click No to rename the version of "H" you're moving or copying". Hitting yes again says the same thing but it's "Location" instead of "H", then same but with "V". It does still work if I hit yes 4 times. Would you be able to explain what it's trying to tell me?
Sub CopyRows()
Dim wk As Worksheet
Dim wkD As Worksheet
Set wkD = Worksheets("tst")
Set wk = ActiveSheet
wk.Range("A30:K40").Copy _
wkD.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub
BUT, when I was doing some testing and copied a 3rd sheet I get this "The name "Direction" already exists. Click Yes to use that version of the name, or click No to rename the version of "Direction" you're moving or copying". If I hit yes it then says "The name "H" already exists. Click yes to use that version of the name, or click No to rename the version of "H" you're moving or copying". Hitting yes again says the same thing but it's "Location" instead of "H", then same but with "V". It does still work if I hit yes 4 times. Would you be able to explain what it's trying to tell me?
Didn't find the answer you are looking for?
Ask a question
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 1, 2018 at 05:35 PM
Aug 1, 2018 at 05:35 PM
Hello Rizzob95,
The code in post#2 will gather the data from each sheet and transfer it to the Summary sheet all together, all at once.
Are you saying that you would rather just transfer the data on an individual basis from each individual sheet as and when you need to and not all together?
Cheerio,
vcoolio.
The code in post#2 will gather the data from each sheet and transfer it to the Summary sheet all together, all at once.
Are you saying that you would rather just transfer the data on an individual basis from each individual sheet as and when you need to and not all together?
Cheerio,
vcoolio.
rizzob95
Posts
4
Registration date
Tuesday July 31, 2018
Status
Member
Last seen
August 2, 2018
Aug 2, 2018 at 08:33 AM
Aug 2, 2018 at 08:33 AM
Basically but I figured out what I needed, this is the code that worked for me
Sub SheetCopy()
ActiveSheet.Range("A30:K40").Copy
Worksheets("SumOfData").Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub
Thank you guys for your help.
Sub SheetCopy()
ActiveSheet.Range("A30:K40").Copy
Worksheets("SumOfData").Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub
Thank you guys for your help.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 2, 2018 at 08:45 AM
Aug 2, 2018 at 08:45 AM
You're welcome Rizzob.
I'm glad that we were able to help in some way.
Cheerio,
vcoolio.
I'm glad that we were able to help in some way.
Cheerio,
vcoolio.