0

So, I've been at this for a bit trying to figure out how to proceed on this, and just a heads up, I"m still in the process of learning VBA, but am turning here for advice / help on this problem.

I'm basically using a counter in my main routine and then calling to a subroutine which needs to use the counter from the main to properly do what I need. Below is the code I have so far.

Sub Main()

Dim Count As Integer
Dim X As Integer
Worksheets("Main Sched.").Activate
X = 2
Count = Cells(2, Columns.Count).End(xlToLeft).Column

Do While X < Count + 2
Cells(X, 2).Select
Ctype = ActiveCell

If Ctype = "3/C #6" Then Call Ct1
If Ctype = "2/C #6" Then Call Ct2

X = X + 1
Loop
End Sub

Sub Ct1()

Cells.Copy
Worksheets("Test").Activate
Cells(X + 2, 2).Select
Cells(X + 2, 2).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

End Sub

It ends as a Run-time error '1004', to paste cells from excel worksheet into current, paste into first cell (A1 or R1C1). I'm needing to take that data and paste it into B4, and later on in the loop based on the "Count" will adjust it accordingly to be something like B8, B13, etc.

The reason I need to split it up, is because it is going to have many different "Ctype" values to reference, and each one has a different scheme on how it takes the data from the "Main Sched." Sheet to the "Test" sheet.

IceBurnHex
  • 3
  • 2
  • change `Sub Ct1()` to `Sub Ct1(X as Long)` then when you call it use `Ct1(X)` which will pass the value. – Scott Craner Jun 27 '18 at 18:33
  • Awesome, worked like a charm. I guess I have another issue entirely with the Cells(X+2, 2).PasteSpecial function still giving the Paste into First Cell error. – IceBurnHex Jun 27 '18 at 18:36
  • `Cells.Copy` is copying the entire active sheet. You need to specify a range to copy. You can't paste an entire sheet into any cell but A1. – Scott Craner Jun 27 '18 at 18:39
  • Thanks again! Looks like I can close this one up. – IceBurnHex Jun 27 '18 at 18:40
  • 1
    @IceBurnHex - reading about [avoiding select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) will take you very far in your programming endeavors – Scott Holtzman Jun 27 '18 at 18:41

0 Answers0