Dynamic copy range with vba

JoeyGaspard

New Member
Joined
Jul 22, 2019
Messages
38
If you right click on the GeneralJournal tab & select View code, is there anything in the code window that opens up?
Also on the BU08Data sheet select I2 & hit Ctrl + down arrow, where do you end up?
When I hit view code on GeneralJournal, there is none, and when I select I2 on BU08Data and do Ctrl + Down arrow, its stops on the last row containing data, in this case that is row 45
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
Do you have any code in the ThisWorkbook module?
 

JoeyGaspard

New Member
Joined
Jul 22, 2019
Messages
38
If you right click on the GeneralJournal tab & select View code, is there anything in the code window that opens up?
Also on the BU08Data sheet select I2 & hit Ctrl + down arrow, where do you end up?
I got that part to work using this:

'Copy BU 18 Data to Atlas Upload
Sheets("BU08Data").Select
Range(Range("I2:T2"), Range("I2:t2").End(xlDown)).Copy
Worksheets("GeneralJournal").Range("B17").PasteSpecial Paste:=xlPasteValues

Now my only problem is the way it is formatting column H on the sheet it is pasting to, cant figure this one out at all, here is what it looks like. you can see how it is different from the other columns.
1584622453872.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
Along with my question in post#12, do you have any conditional formatting on that sheet?
 

JoeyGaspard

New Member
Joined
Jul 22, 2019
Messages
38
Along with my question in post#12, do you have any conditional formatting on that sheet?
No, there is no code on that sheet, and no conditional formatting, it only happens in that column after the macro runs. I have verified that the formatting in column H is the same as the column it is being copied from also
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
In that case I don't know why it's changing the format of the column.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

JoeyGaspard

New Member
Joined
Jul 22, 2019
Messages
38
You're welcome & thanks for the feedback

Hey Fluff, one more quick question, I am trying to insert this formula (in bold) into a cell based on another cells contents, and I think I have most of it figured out, but it doesnt seem to like the " in the formula, any idea? It bombs on the firs set of quotations it comes to

Sheets("GeneralJournal").Select

With Range("Q17:Q" & Cells(Rows.Count, "B").End(xlUp).Row)
.Formula = "=IF([@AccountType]="Ledger",CONCATENATE([@[Main account]],"-",[@BusinessUnit],"-",[@Department],"-",[@CostCenter],"-",[@CIP],"-"),IF(OR([@AccountType]="Customer",[@AccountType]="Vendor",[@AccountType]="Fixedassets"),SUBSTITUTE([@[Main account]],"-","\-",1),[@[Main account]]))"
Range("B17:B100").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B17").Select

1584631520811.png
 

Forum statistics

Threads
1,089,218
Messages
5,406,920
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top