Format cells while copying data from one spreadsheet to another

[youngtusk87]

Hey peeps, I'm having an issue with Excel's amazing auto-formatting. I'm entering 12-digit UPC codes consisting of all numbers into column D. When the codes are put in they are of course formatted awkwardly, but even when I change the format from "General" to "Numbers" and run my Macro, is either copies the value of the cell in the general formatting or pastes the value at the destination cell using general formatting.

Here is my code that works with no errors.

Dim SrcWkb as Workbook
Dim DstWkb as Workbook
Dim RowCount as Integer
Dim SrcStr as String

Set SrcWkb = Workbooks("Master_Template")
Set DstWkb = Workbooks("Buy_Addon_Template")
For RowCount = 2 To SrcWkb.Worksheets("Master Sheet").UsedRange.rows.Count
    SrcStr = SrcWkb.Worksheets("Master Sheet").Columns.Range("D" & RowCount)
    DstWkb.Worksheets("Feed Columns").Columns.Range("B" & RowCount) = SrcStr
Next RowCount

I tried using an Integer instead of a string and the CInt function as follows but with no joy; ran into errors.

Dim SrcWkb as Workbook
Dim DstWkb as Workbook
Dim RowCount as Integer
Dim SrcInt as Integer

Set SrcWkb = Workbooks("Master_Template")
Set DstWkb = Workbooks("Buy_Addon_Template")
For RowCount = 2 To SrcWkb.Worksheets("Master Sheet").UsedRange.rows.Count
    SrcInt = CInt(SrcWkb.Worksheets("Master Sheet").Columns.Range("D" & RowCount))
    DstWkb.Worksheets("Feed Columns").Columns.Range("B" & RowCount) = SrcInt
Next RowCount

Any help would be awesome!

[bheanloh]

Why don't you use copy?

For RowCount = 2 To SrcWkb.Worksheets("Master Sheet").UsedRange.Rows.Count
    Set SrcStr = SrcWkb.Worksheets("Master Sheet").Columns.Range("D" & RowCount)
    SrcStr.Copy DstWkb.Worksheets("Feed Columns").Columns.Range("B" & RowCount)
Next RowCount

Boon

[youngtusk87]

Thanks for the reply, unfortunately the code you posted gave me an error. Highlighting the "SrcStr" after the 'Set' and says

Compile error: Object required

[bheanloh]

What if you remove the 'set'?

[youngtusk87]

It highlights the "SrcStr" in the "SrcStr.Copy" function with the following error:

Invalid qualifier

[bheanloh]

Dim SrcStr as Range
Try

[youngtusk87]

At first it gave me an error, but then I put the "Set" back in, as follows:

Dim SrcStr as Range

For RowCount = 2 To SrcWkb.Worksheets("Master Sheet").UsedRange.Rows.Count
    Set SrcStr = SrcWkb.Worksheets("Master Sheet").Columns.Range("D" & RowCount)
    SrcStr.Copy DstWkb.Worksheets("Feed Columns").Columns.Range("B" & RowCount)
Next RowCount

Works perfectly now, thank you bheanloh!

[bheanloh]

Opps. Forgot to mention that. Glad that I helped.