Making my macro independent on future SheetsSolved/Closed

Question

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

ac3mark · Answer

Do you need to count the number of sheets?

Try this:

Function Counttabs()    Counttabs = ThisWorkbook.Worksheets.CountEnd Function

vcoolio · Answer

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.

rizzob95 · Answer

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

rizzob95 · Answer

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?

vcoolio · Answer

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.

rizzob95 · Answer

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.

Making my macro independent on future Sheets

6 responses

Similar discussions

Subscribe To Our Newsletter!