Making my macro Independent on future Sheets [Solved]

rizzob95 4 Posts Tuesday July 31, 2018Registration date August 2, 2018 Last seen - Jul 31, 2018 at 03:33 PM - Latest reply: vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen
- Aug 2, 2018 at 08:45 AM
Hello All;
1st time posting anything so please bear with me. I really need some help and have going out of my mind for days with what I believe is most likely something simple I’m missing. I’m writing a code in VBA that works perfect, my problems is, I don’t know how many sheets I will need the amount can change each time i use it. The program I’m writing could only use 2 sheets one time or 20 the next. I need to make my macro independent of each other when I copy and add another sheet. This is what I have in VBA now that works only for that particular sheet.

Sub Test()
'
' Test Macro
'

'
Worksheets("Cutting info").Range("A30:K40").Copy _
Worksheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub

This is what I thought would work but I keep getting errors.

Sub Copy()

'
' Test Macro
'

'
Dim wk As Worksheet
Set wk = ActiveSheet
ActiveSheet("wk").Range("A30:K40").Copy _
ActiveSheet("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub

I have tried everything I can think of as well as this one.

Sub Copy()

'
' Test Macro
'

'
Dim sName As String
sName = ActiveSheet.Name
Worksheets("sName").Range("A30:K40").Copy _
Worksheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

Any help would be greatly appreciated
See more 

Your reply

7 replies

ac3mark 10647 Posts Monday June 3, 2013Registration dateModeratorStatus October 15, 2018 Last seen - Jul 31, 2018 at 03:54 PM
0
Thank you
Do you need to count the number of sheets?

Try this:

Function Counttabs()
Counttabs = ThisWorkbook.Worksheets.Count
End Function


Respond to ac3mark
vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen - Aug 1, 2018 at 01:39 AM
0
Thank you
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:-


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.
Respond to vcoolio
rizzob95 4 Posts Tuesday July 31, 2018Registration date August 2, 2018 Last seen - Aug 1, 2018 at 08:31 AM
0
Thank you
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
Respond to rizzob95
rizzob95 4 Posts Tuesday July 31, 2018Registration date August 2, 2018 Last seen - Aug 1, 2018 at 09:05 AM
0
Thank you
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?
Respond to rizzob95
vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen - Aug 1, 2018 at 05:35 PM
0
Thank you
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.
Respond to vcoolio
rizzob95 4 Posts Tuesday July 31, 2018Registration date August 2, 2018 Last seen - Aug 2, 2018 at 08:33 AM
0
Thank you
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.
vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen - 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.
Respond to rizzob95