Fill a column based on one cell

[GIS]

Hi guys,

I had so much help from others here with the last macro and I am hoping someone can help me out with this new one.

What I need this code to do is copy the value in cell B4 in one workbook to cell G6 in the other workbook, and continue copying the value down to the last row. The workbook names will never be the same, neither will the number of rows. The two cells are the same. I have managed to get it to copy the right data to where I need to get it to go, but not to copy down. I pulled some code from my other macro that copied down to the last row, but I am getting "object not defined" errors. Not sure if it is because this macro works across workbooks, and the other was just worksheets.

Would anyone mind taking a look at what I have so far and suggest any changes? Eventually I will need this macro to do much more but I really want this aprt to work before I continue.
Thank you

Sub LabData()
Dim wb As Workbook
Dim CellCount, FormulaCount As Long
Dim Ws As Worksheet
Dim RangeSource, RangeDest As Range
For Each wb In Workbooks
    If wb.Name <> ThisWorkbook.Name Then
       Worksheets(1).Range("B4").Copy ThisWorkbook.Sheets(1).Range("G6")
    End If
 
    Set Ws = ThisWorkbook.Sheets(1)
    CellCount = Ws.Range("A6").End(x1Down).Row ' error on this line
    FormulaCount = WorksheetFunction.CountA(Ws.Columns("G6")) ' this line gives same error if I comment line above out
 
    If CellCount <> FormulaCount Then
        If FormulaCount > 7 Then Ws.Range("G7:G" & FormulaCount).ClearContents
            Set RangeSource = Ws.Range("G6")
            Set RangeDest = Ws.Range("G6:G" & CellCount)
            RangeDest.Formula = RangeSource.Formula
    End If
 
Next wb
MsgBox "Complete"
End Sub

 

[Andrew Poulsom]

It's xlDown not x1Down (a lower case L not the number one).

 

[GIS]

Well, that's embarrassing.
Thank you for the other set of eyes. I had been staring at those two lines all morning and just never noticed I typed it in wrong.
I had a bad feeling it was something silly....

 

[Andrew Poulsom]

I strongly recommend that you put Option Explicit at the top of all your modules. That way you won't be able to make typos like that. You can have it inserted automatically in new modules by checking 'Require Variable Declaration' on the Editor tab on the Tools|Options dialog.

 

[Rick Rothstein]

Sub LabData()
Dim wb As Workbook
[COLOR=red][B]Dim CellCount, FormulaCount As Long[/B][/COLOR]
Dim Ws As Worksheet
[COLOR=red][B]Dim RangeSource, RangeDest As Range[/B][/COLOR]

The two declaration statements highlighted in red are not doing what you think they are. Only FormulaCount is declared as Long and only RangeDest is declared as Range... both CellCount and RangeSource are declared as Variants. In VB, every variable must be declared as to its type individually... there is no shortcutting this as in other programming languages. Those two declaration lines should look like this...

Dim CellCount As Long, FormulaCount As Long
....
Dim RangeSource As Range, RangeDest As Range

 

[GIS]

Sorry for not responding earlier- I was unable to access the site.

Andrew- Thank you for mentioning Option Explicit. I did some research into it and it is definitely something that I will be including in all my future Macros. It has already saved me several times!

Rick- Thank you for pointing that out! I had no idea. I actually did that in my last macro out of habit and didn't think anything of it. Went back and made all the changes.

Man, the things you learn!