Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › VBA worksheet objects
New Posts  All Forums:Forum Nav:

VBA worksheet objects

post #1 of 7
Thread Starter 
I have a macro that can be run multiple times on a workbook. The first time it is run, it creates multiple worksheet objects.

Code:
Private mySheet As Worksheet
And then I have code like this to create the worksheet and name it:
Code:
Set mySheet = Sheets.Add
    mySheet.Activate
    ActiveSheet.name = "WorkSheet"
I am working on modifying the program so it can be run multiple times, concatenating new data each time it is run. Is there a way to associate the worksheet named "WorkSheet" with the Exceptions worksheet object?
Edited by mortimersnerd - 4/14/11 at 7:35am
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
post #2 of 7
Code:
mySheet = Worksheets("WorkSheet")
or, to avoid possible errors:

Code:
        Dim i As Integer
        For i = 1 To Worksheets.Count
            If Worksheets.Item(i).Name = "WorkSheet" Then
                mySheet = Worksheets("WorkSheet")                
            End If
        Next
or to run multiple times:

Code:
        Dim wsFound as Boolean
        wsFound = False
        Dim i As Integer
        For i = 1 To Worksheets.Count
            If Worksheets.Item(i).Name = "WorkSheet" Then
                wsFound = True
                mySheet = Worksheets("WorkSheet")                
            End If
        Next

        If Not wsFound Then
             Set mySheet = Sheets.Add
             mySheet.Activate
             ActiveSheet.name = "WorkSheet"
        End If
i7
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7 920 D0 w/ HT On [4.01ghz @ 1.208v] Asus P6X58D Premium Asus HD5870 G.SKILL 6GB (3X2GB) DDR3 @ 1528MHz 
Hard DriveOptical DriveOSMonitor
OCZ Vertex Turbo 60GB FW1.5 2xSamsung Spinpoint F3 LG 8x Blu-ray Combo Drive Windows 7 64-bit Dell U2410 Rev. A04 
PowerCase
OCZ ModXStream Pro 700W V2.2 HAF 922 
  hide details  
Reply
i7
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7 920 D0 w/ HT On [4.01ghz @ 1.208v] Asus P6X58D Premium Asus HD5870 G.SKILL 6GB (3X2GB) DDR3 @ 1528MHz 
Hard DriveOptical DriveOSMonitor
OCZ Vertex Turbo 60GB FW1.5 2xSamsung Spinpoint F3 LG 8x Blu-ray Combo Drive Windows 7 64-bit Dell U2410 Rev. A04 
PowerCase
OCZ ModXStream Pro 700W V2.2 HAF 922 
  hide details  
Reply
post #3 of 7
Thread Starter 
Thanks! I'll try that in the morning.
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
post #4 of 7
Thread Starter 
When I try the above code, I get the error:

Quote:
Run-time error '91':
Object variable or With block variable not set.
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
post #5 of 7
Quote:
Originally Posted by mortimersnerd View Post
When I try the above code, I get the error:
Sounds like you are missing a .dll or trying to reference one that is unregistered. Didn't the error come with a line number? If it did, do you mind grabbing us the snippet of code?
Intellect v2
(9 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7-6700K Processor ASUS ROG MAXIMUS VIII HERO LGA1151 DDR4 M.2 SAT... EVGA GTX 1080 SC ACX 3.0 Crucial Ballistix Sport 32GB DDR4 2400 MT/s (PC... 
CoolingKeyboardPowerCase
Noctua NH-D15 Das Keyboard 4 Professional (Brown) Corsair AX860 Fractal Design Define R5 
Mouse
MIONIX NAOS 7000 
  hide details  
Reply
Intellect v2
(9 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7-6700K Processor ASUS ROG MAXIMUS VIII HERO LGA1151 DDR4 M.2 SAT... EVGA GTX 1080 SC ACX 3.0 Crucial Ballistix Sport 32GB DDR4 2400 MT/s (PC... 
CoolingKeyboardPowerCase
Noctua NH-D15 Das Keyboard 4 Professional (Brown) Corsair AX860 Fractal Design Define R5 
Mouse
MIONIX NAOS 7000 
  hide details  
Reply
post #6 of 7
Thread Starter 
The error is on the line where mySheet = Worksheets("....
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
post #7 of 7
The code assumed you still had mySheet declared:

Code:
Private mySheet As Worksheet
i7
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7 920 D0 w/ HT On [4.01ghz @ 1.208v] Asus P6X58D Premium Asus HD5870 G.SKILL 6GB (3X2GB) DDR3 @ 1528MHz 
Hard DriveOptical DriveOSMonitor
OCZ Vertex Turbo 60GB FW1.5 2xSamsung Spinpoint F3 LG 8x Blu-ray Combo Drive Windows 7 64-bit Dell U2410 Rev. A04 
PowerCase
OCZ ModXStream Pro 700W V2.2 HAF 922 
  hide details  
Reply
i7
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7 920 D0 w/ HT On [4.01ghz @ 1.208v] Asus P6X58D Premium Asus HD5870 G.SKILL 6GB (3X2GB) DDR3 @ 1528MHz 
Hard DriveOptical DriveOSMonitor
OCZ Vertex Turbo 60GB FW1.5 2xSamsung Spinpoint F3 LG 8x Blu-ray Combo Drive Windows 7 64-bit Dell U2410 Rev. A04 
PowerCase
OCZ ModXStream Pro 700W V2.2 HAF 922 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › VBA worksheet objects