Debug Error

  • Thread starter Thread starter Dave Peterson
  • Start date Start date
D

Dave Peterson

This line:
Sheets("Daily").Cells(Sfr, sc).Resize(slr - Sfr + 1).Copy
(there are two lines with Sheets("Daily") on them)

My guess is that slr and Sfr aren't what you want them to be.

Add a line like:

msgbox "slr=" & slr & vblf & "Sfr=" & Sfr

right before it. Maybe it'll give you a clue about what's going wrong.
 
Can someone tell me why I am getting a debug error with this code.
It worked at one time and I do not remember making any changes to it.
The error I get is "Application-defined or object-defined error"on the
sheets("Daily") line

sc = 24 'col AB
Sfr = 12 'row 12 on source sheet
slr = Sheets("Daily").Cells(Rows.Count, sc).End(xlUp).Row
dr = 7 'row 7 on destination sheeet

With Sheets("Sales To Date")
dlc = .Cells(dr, Columns.Count).End(xlToLeft).Column + 1
Sheets("Daily").Cells(Sfr, sc).Resize(slr - Sfr + 1).Copy
..Cells(dr, dlc).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
..Cells(dr, dlc).PasteSpecial Paste:=xlPasteFormats
'.Cells(6, dlc).Value = Range("B1")
Range("B1").Copy .Cells(6, dlc)
..Columns(dlc).AutoFit
End With
 
with slr=1 and sfr=12
then
Sheets("Daily").Cells(Sfr, sc).Resize(slr - Sfr + 1).Copy
is the same as:
Sheets("Daily").Cells(Sfr, sc).Resize(1 - 12 + 1).Copy
or
Sheets("Daily").Cells(Sfr, sc).Resize(-12).Copy

You can't resize a range to anything less than a single row--0's and negatives
are not allowed.

Maybe you want:
Sheets("Daily").Cells(Sfr, sc).Resize(sfr - Slr + 1).Copy
But that's just a guess.
 
Dave
This is the whole macro:
using the nsgbox it gave me
slr = 1
sfr = 12
I would think slr should be the count of rows on the sheet to be copied. If
that is the case the rows should be about 65.


Sub CopySheet1ColGtoSheet2()

Application.ScreenUpdating = False
Worksheets("Daily").Unprotect Password:="7135"

sc = 24 'col AB of source
Sfr = 12 'row 12 to start on source sheet going dow to about 75
slr = Sheets("Daily").Cells(Rows.Count, sc).End(xlUp).Row
dr = 7 'start on row 7 on destination sheeet

Worksheets("Sales To Date").Unprotect Password:="7135"


With Sheets("Sales To Date")
dlc = .Cells(dr, Columns.Count).End(xlToLeft).Column + 1
MsgBox "slr=" & slr & vbLf & "Sfr=" & Sfr

Sheets("Daily").Cells(Sfr, sc).Resize(slr - Sfr + 1).Copy
..Cells(dr, dlc).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
..Cells(dr, dlc).PasteSpecial Paste:=xlPasteFormats
'.Cells(6, dlc).Value = Range("B1")
Range("B1").Copy .Cells(6, dlc)
..Columns(dlc).AutoFit
End With
Worksheets("Daily").Protect Password:="7135"
'calls macro below automatically
CopySheet1andRename
Worksheets("Sales To Date").Protect Password:="7135"

Application.ScreenUpdating = True

End Sub
 
I was able to figure out my problem Dave, Thank you for your help I had
hidden columns and was not counting them.
 
Back
Top